Backup File Name

Dear all,

On scheduling the online backup for SQL server 2005 with SQL server management studio, how the backup filename can be automatically change for everyday. I mean the backup filename be automatically generated every day.

Thanks

Imran Hasware

You can take help of database maintenance plan in this case, where you can automatically set the filename to change.

Or else refer to the Tara's weblog http://weblogs.sqlteam.com/tarad/ where she got very useful script that can used without any issues.

|||

Dear Satya,

I tried Maintenance plan, but i am getting the following error:

Creating maintenance plan "SAP_Backup_Full" (Error)

Messages

Create maintenance plan failed.

ADDITIONAL INFORMATION:

Create failed for JobStep 'Subplan'. (Microsoft.SqlServer.MaintenancePlanTasks)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The specified '@.subsystem' is invalid (valid values are returned by sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476

When I checked the Link, No information is described there. Can you please suggest me.

Thank

Imran Hasware

backup file much larger than the actual working database file

Hi All,
I am having a problem with the backup file (approx 70+ GB) that is much larger than the original working database (approx 4GB). This doesn't seems logical to me, anyone knows what causes this strange situations ?
following is my database backup setting
- Full Database Backup
- Overwrite Media
- Backup to physical disc on the same server
Many thanks in advance.
Bernard Goh
I would guess there is more than 1 backup set in that file. If you were to
run the RESTORE HEADERONLY command on the backup file, how many entries do
you see?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:A150B725-F523-4152-9E53-D1DC71815A94@.microsoft.com...
> Hi All,
> I am having a problem with the backup file (approx 70+ GB) that is much
larger than the original working database (approx 4GB). This doesn't seems
logical to me, anyone knows what causes this strange situations ?
> following is my database backup setting
> - Full Database Backup
> - Overwrite Media
> - Backup to physical disc on the same server
> Many thanks in advance.
> --
> Bernard Goh
|||I would guess there is more than 1 backup set in that file. If you were to
run the RESTORE HEADERONLY command on the backup file, how many entries do
you see?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:A150B725-F523-4152-9E53-D1DC71815A94@.microsoft.com...
> Hi All,
> I am having a problem with the backup file (approx 70+ GB) that is much
larger than the original working database (approx 4GB). This doesn't seems
logical to me, anyone knows what causes this strange situations ?
> following is my database backup setting
> - Full Database Backup
> - Overwrite Media
> - Backup to physical disc on the same server
> Many thanks in advance.
> --
> Bernard Goh
|||Hi,
Can you execute the below command to check the number of backup sets you
have in your backup file:-
RESTORE headeronly from disk='c:\FILENAME.BAK'
This will show the number of backup files in the backfile. Since you
selected the "Overwrite media", there should be only one file.
To identify the exact backup file size, Execute the below command from Query
Analyzer:-
BACKUP DATABASE <dbname> to disk='d:\backup\dbname.bak' with
,stats=10 -- INIT will overwrite the backup file
Give the drive letter and folder based on ur availability , after the
execution check the file size.
Can you check the transaction log size as well. How to check the log size
DBCC SQLPERF(LOGSPACE)
If you have a big log file then backup the transaction log file and shrink
the trasnaction log file.
How to shrink the transaction log file if your database is FULL recovery
model.
1. Backup the Transaction Log using (Backup log command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Truncateonly')
How to shrink the transaction log file if your database is Simple recovery
model.
1. Truncate the Transaction Log using (Backup log dbname with truncate_only
command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Truncateonly')
How to shrink the data files
1. Check any open transactions or there using DBCC OPENTRAN(DBname)
2. If not, Run DBCC SHRINKFILE('logical_datafile_name',size)
Thanks
Hari
MCDBA
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:A150B725-F523-4152-9E53-D1DC71815A94@.microsoft.com...
> Hi All,
> I am having a problem with the backup file (approx 70+ GB) that is much
larger than the original working database (approx 4GB). This doesn't seems
logical to me, anyone knows what causes this strange situations ?
> following is my database backup setting
> - Full Database Backup
> - Overwrite Media
> - Backup to physical disc on the same server
> Many thanks in advance.
> --
> Bernard Goh
|||Hi,
Can you execute the below command to check the number of backup sets you
have in your backup file:-
RESTORE headeronly from disk='c:\FILENAME.BAK'
This will show the number of backup files in the backfile. Since you
selected the "Overwrite media", there should be only one file.
To identify the exact backup file size, Execute the below command from Query
Analyzer:-
BACKUP DATABASE <dbname> to disk='d:\backup\dbname.bak' with
,stats=10 -- INIT will overwrite the backup file
Give the drive letter and folder based on ur availability , after the
execution check the file size.
Can you check the transaction log size as well. How to check the log size
DBCC SQLPERF(LOGSPACE)
If you have a big log file then backup the transaction log file and shrink
the trasnaction log file.
How to shrink the transaction log file if your database is FULL recovery
model.
1. Backup the Transaction Log using (Backup log command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Truncateonly')
How to shrink the transaction log file if your database is Simple recovery
model.
1. Truncate the Transaction Log using (Backup log dbname with truncate_only
command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Truncateonly')
How to shrink the data files
1. Check any open transactions or there using DBCC OPENTRAN(DBname)
2. If not, Run DBCC SHRINKFILE('logical_datafile_name',size)
Thanks
Hari
MCDBA
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:A150B725-F523-4152-9E53-D1DC71815A94@.microsoft.com...
> Hi All,
> I am having a problem with the backup file (approx 70+ GB) that is much
larger than the original working database (approx 4GB). This doesn't seems
logical to me, anyone knows what causes this strange situations ?
> following is my database backup setting
> - Full Database Backup
> - Overwrite Media
> - Backup to physical disc on the same server
> Many thanks in advance.
> --
> Bernard Goh

backup file much larger than the actual working database file

Hi All,
I am having a problem with the backup file (approx 70+ GB) that is much larg
er than the original working database (approx 4GB). This doesn't seems logic
al to me, anyone knows what causes this strange situations ?
following is my database backup setting
- Full Database Backup
- Overwrite Media
- Backup to physical disc on the same server
Many thanks in advance.
--
Bernard GohI would guess there is more than 1 backup set in that file. If you were to
run the RESTORE HEADERONLY command on the backup file, how many entries do
you see?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:A150B725-F523-4152-9E53-D1DC71815A94@.microsoft.com...
> Hi All,
> I am having a problem with the backup file (approx 70+ GB) that is much
larger than the original working database (approx 4GB). This doesn't seems
logical to me, anyone knows what causes this strange situations ?
> following is my database backup setting
> - Full Database Backup
> - Overwrite Media
> - Backup to physical disc on the same server
> Many thanks in advance.
> --
> Bernard Goh|||Hi,
Can you execute the below command to check the number of backup sets you
have in your backup file:-
RESTORE headeronly from disk='c:\FILENAME.BAK'
This will show the number of backup files in the backfile. Since you
selected the "Overwrite media", there should be only one file.
To identify the exact backup file size, Execute the below command from Query
Analyzer:-
BACKUP DATABASE <dbname> to disk='d:\backup\dbname.bak' with
,stats=10 -- INIT will overwrite the backup file
Give the drive letter and folder based on ur availability , after the
execution check the file size.
----
--
Can you check the transaction log size as well. How to check the log size
DBCC SQLPERF(LOGSPACE)
If you have a big log file then backup the transaction log file and shrink
the trasnaction log file.
How to shrink the transaction log file if your database is FULL recovery
model.
1. Backup the Transaction Log using (Backup log command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Trun
cateonly')
How to shrink the transaction log file if your database is Simple recovery
model.
1. Truncate the Transaction Log using (Backup log dbname with truncate_only
command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Trun
cateonly')
How to shrink the data files
1. Check any open transactions or there using DBCC OPENTRAN(DBname)
2. If not, Run DBCC SHRINKFILE('logical_datafile_name',size)
Thanks
Hari
MCDBA
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:A150B725-F523-4152-9E53-D1DC71815A94@.microsoft.com...
> Hi All,
> I am having a problem with the backup file (approx 70+ GB) that is much
larger than the original working database (approx 4GB). This doesn't seems
logical to me, anyone knows what causes this strange situations ?
> following is my database backup setting
> - Full Database Backup
> - Overwrite Media
> - Backup to physical disc on the same server
> Many thanks in advance.
> --
> Bernard Goh

backup file much larger than the actual working database file

Hi All,
I am having a problem with the backup file (approx 70+ GB) that is much larger than the original working database (approx 4GB). This doesn't seems logical to me, anyone knows what causes this strange situations ?
following is my database backup setting
- Full Database Backup
- Overwrite Media
- Backup to physical disc on the same server
Many thanks in advance.
--
Bernard GohI would guess there is more than 1 backup set in that file. If you were to
run the RESTORE HEADERONLY command on the backup file, how many entries do
you see?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:A150B725-F523-4152-9E53-D1DC71815A94@.microsoft.com...
> Hi All,
> I am having a problem with the backup file (approx 70+ GB) that is much
larger than the original working database (approx 4GB). This doesn't seems
logical to me, anyone knows what causes this strange situations ?
> following is my database backup setting
> - Full Database Backup
> - Overwrite Media
> - Backup to physical disc on the same server
> Many thanks in advance.
> --
> Bernard Goh|||Hi,
Can you execute the below command to check the number of backup sets you
have in your backup file:-
RESTORE headeronly from disk='c:\FILENAME.BAK'
This will show the number of backup files in the backfile. Since you
selected the "Overwrite media", there should be only one file.
To identify the exact backup file size, Execute the below command from Query
Analyzer:-
BACKUP DATABASE <dbname> to disk='d:\backup\dbname.bak' with
,stats=10 -- INIT will overwrite the backup file
Give the drive letter and folder based on ur availability , after the
execution check the file size.
----
--
Can you check the transaction log size as well. How to check the log size
DBCC SQLPERF(LOGSPACE)
If you have a big log file then backup the transaction log file and shrink
the trasnaction log file.
How to shrink the transaction log file if your database is FULL recovery
model.
1. Backup the Transaction Log using (Backup log command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Truncateonly')
How to shrink the transaction log file if your database is Simple recovery
model.
1. Truncate the Transaction Log using (Backup log dbname with truncate_only
command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Truncateonly')
How to shrink the data files
1. Check any open transactions or there using DBCC OPENTRAN(DBname)
2. If not, Run DBCC SHRINKFILE('logical_datafile_name',size)
--
Thanks
Hari
MCDBA
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:A150B725-F523-4152-9E53-D1DC71815A94@.microsoft.com...
> Hi All,
> I am having a problem with the backup file (approx 70+ GB) that is much
larger than the original working database (approx 4GB). This doesn't seems
logical to me, anyone knows what causes this strange situations ?
> following is my database backup setting
> - Full Database Backup
> - Overwrite Media
> - Backup to physical disc on the same server
> Many thanks in advance.
> --
> Bernard Gohsql

Backup File I/o Error

Guys,

Apologies if this is in the wrong section. I have a SQL 2000 database which I backup to a folder on the hard disk using the following command in a Server Agent:

BACKUP DATABASE [Multilog] TO DISK = N'C:\MSSQL7\BACKUP\multilog_backup' WITH INIT , NOUNLOAD , NAME = N'Multilog backup', NOSKIP , STATS = 10, NOFORMAT

Recently it has been failing with the following error message:

Nonrecoverable I/O error occurred on file 'C:\Multilog Database Files\Data\MultiLog_data.MDF'. [SQLSTATE 42000] (Error 3271) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

Any idea what could be causing this?

Any help would be greatly appreciated.

Sean.

Try some of the method(s) in the following kb:

http://support.microsoft.com/default.aspx/kb/904804

Is this an SP4 server? Looks like it may have been upgraded from SQL 7.0, is that correct?

|||

Chad,

Just noticed the following error on the logs:

I/O error 23(Data error (cyclic redundancy check).) detected during read of BUF pointer = 0x14243d80, page ptr = 0x1607c000, pageid = (0x1:0x1ae), dbid = 5, status = 0x801, file = C:\Multilog Database Files\Data\MultiLog_data.MDF..

Would this indicate a fault with the actual database file? Any idea what could be done to check and fix?

|||

Yeah, that would indicate a data integrity issue within your DB file. Try running a DBCC CHECKDB on the database in question and see what you get for output. Depending on the output of that, you could try using the FIX options with the CHECKDB command, rebuilding indexes, etc.

Backup File Deletions not Working

Hi:
We configured a backup job in the database maintenance plan for a database
for both "Complete Backup" and the "Transaction Log". We told the job to
delete files after one day. It has not been deleting these files, though.
Why is that?
John
Hi
Files are deleted after the next backup completes successfully.
If you se a backup to delete after 1 day, and you don't run the actual
backup job for 2 days, the backup will remain on disk for 2 days, until
after the backup is successful.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:6E533D8D-A308-47F9-AC62-282987DBF849@.microsoft.com...
> Hi:
> We configured a backup job in the database maintenance plan for a database
> for both "Complete Backup" and the "Transaction Log". We told the job to
> delete files after one day. It has not been deleting these files, though.
> Why is that?
> John
|||So, does that mean that the backups have not been successful?
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Files are deleted after the next backup completes successfully.
> If you se a backup to delete after 1 day, and you don't run the actual
> backup job for 2 days, the backup will remain on disk for 2 days, until
> after the backup is successful.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:6E533D8D-A308-47F9-AC62-282987DBF849@.microsoft.com...
>
>
|||See this old post:-
http://groups.google.co.in/group/mic...141cc02159d2bc
Thanks
Hari
SQL Server MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:262CAB61-803A-46BF-AA4A-CF5EC4AF8672@.microsoft.com...[vbcol=seagreen]
> So, does that mean that the backups have not been successful?
> "Mike Epprecht (SQL MVP)" wrote:
|||Hi
No. Look at the backup job logs to see what is going on.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:262CAB61-803A-46BF-AA4A-CF5EC4AF8672@.microsoft.com...[vbcol=seagreen]
> So, does that mean that the backups have not been successful?
> "Mike Epprecht (SQL MVP)" wrote:

Backup File Deletions not Working

Hi:
We configured a backup job in the database maintenance plan for a database
for both "Complete Backup" and the "Transaction Log". We told the job to
delete files after one day. It has not been deleting these files, though.
Why is that?
JohnHi
Files are deleted after the next backup completes successfully.
If you se a backup to delete after 1 day, and you don't run the actual
backup job for 2 days, the backup will remain on disk for 2 days, until
after the backup is successful.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:6E533D8D-A308-47F9-AC62-282987DBF849@.microsoft.com...
> Hi:
> We configured a backup job in the database maintenance plan for a database
> for both "Complete Backup" and the "Transaction Log". We told the job to
> delete files after one day. It has not been deleting these files, though.
> Why is that?
> John|||So, does that mean that the backups have not been successful?
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Files are deleted after the next backup completes successfully.
> If you se a backup to delete after 1 day, and you don't run the actual
> backup job for 2 days, the backup will remain on disk for 2 days, until
> after the backup is successful.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:6E533D8D-A308-47F9-AC62-282987DBF849@.microsoft.com...
>
>|||See this old post:-
http://groups.google.co.in/group/mi...4141cc02159d2bc
Thanks
Hari
SQL Server MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:262CAB61-803A-46BF-AA4A-CF5EC4AF8672@.microsoft.com...[vbcol=seagreen]
> So, does that mean that the backups have not been successful?
> "Mike Epprecht (SQL MVP)" wrote:
>|||Hi
No. Look at the backup job logs to see what is going on.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:262CAB61-803A-46BF-AA4A-CF5EC4AF8672@.microsoft.com...[vbcol=seagreen]
> So, does that mean that the backups have not been successful?
> "Mike Epprecht (SQL MVP)" wrote:
>

Backup File Deletions not Working

Hi:
We configured a backup job in the database maintenance plan for a database
for both "Complete Backup" and the "Transaction Log". We told the job to
delete files after one day. It has not been deleting these files, though.
Why is that?
JohnHi
Files are deleted after the next backup completes successfully.
If you se a backup to delete after 1 day, and you don't run the actual
backup job for 2 days, the backup will remain on disk for 2 days, until
after the backup is successful.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:6E533D8D-A308-47F9-AC62-282987DBF849@.microsoft.com...
> Hi:
> We configured a backup job in the database maintenance plan for a database
> for both "Complete Backup" and the "Transaction Log". We told the job to
> delete files after one day. It has not been deleting these files, though.
> Why is that?
> John|||So, does that mean that the backups have not been successful?
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Files are deleted after the next backup completes successfully.
> If you se a backup to delete after 1 day, and you don't run the actual
> backup job for 2 days, the backup will remain on disk for 2 days, until
> after the backup is successful.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:6E533D8D-A308-47F9-AC62-282987DBF849@.microsoft.com...
> > Hi:
> >
> > We configured a backup job in the database maintenance plan for a database
> > for both "Complete Backup" and the "Transaction Log". We told the job to
> > delete files after one day. It has not been deleting these files, though.
> >
> > Why is that?
> >
> > John
>
>|||See this old post:-
http://groups.google.co.in/group/microsoft.public.sqlserver.server/browse_thread/thread/f24fa621edb472b/04141cc02159d2bc?lnk=st&q=maintenance+plan+not+deleting+old+files&rnum=4&hl=en#04141cc02159d2bc
Thanks
Hari
SQL Server MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:262CAB61-803A-46BF-AA4A-CF5EC4AF8672@.microsoft.com...
> So, does that mean that the backups have not been successful?
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> Files are deleted after the next backup completes successfully.
>> If you se a backup to delete after 1 day, and you don't run the actual
>> backup job for 2 days, the backup will remain on disk for 2 days, until
>> after the backup is successful.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
>> message news:6E533D8D-A308-47F9-AC62-282987DBF849@.microsoft.com...
>> > Hi:
>> >
>> > We configured a backup job in the database maintenance plan for a
>> > database
>> > for both "Complete Backup" and the "Transaction Log". We told the job
>> > to
>> > delete files after one day. It has not been deleting these files,
>> > though.
>> >
>> > Why is that?
>> >
>> > John
>>|||Hi
No. Look at the backup job logs to see what is going on.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:262CAB61-803A-46BF-AA4A-CF5EC4AF8672@.microsoft.com...
> So, does that mean that the backups have not been successful?
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> Files are deleted after the next backup completes successfully.
>> If you se a backup to delete after 1 day, and you don't run the actual
>> backup job for 2 days, the backup will remain on disk for 2 days, until
>> after the backup is successful.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
>> message news:6E533D8D-A308-47F9-AC62-282987DBF849@.microsoft.com...
>> > Hi:
>> >
>> > We configured a backup job in the database maintenance plan for a
>> > database
>> > for both "Complete Backup" and the "Transaction Log". We told the job
>> > to
>> > delete files after one day. It has not been deleting these files,
>> > though.
>> >
>> > Why is that?
>> >
>> > John
>>

Backup file database was restored from

Hello!
I am trying to find out name of the backup file database was restored from
(I am restoring from DISK), something similar to physical_device_name in
backupmediafamily. restorehistory doesn't have this information.
Any advice is greatly appreciated.
Thanks,
Igor
You can use backup_set_id on restorehistory and media_set_id on backupset to
find the record on backupmediafamily.
Hope this helps,
Ben Nevarez
"Igor Marchenko" wrote:

> Hello!
> I am trying to find out name of the backup file database was restored from
> (I am restoring from DISK), something similar to physical_device_name in
> backupmediafamily. restorehistory doesn't have this information.
> Any advice is greatly appreciated.
> Thanks,
> Igor
|||Thanks a lot Ben! That exactly what I was looking for.
Igor
"Ben Nevarez" wrote:
[vbcol=seagreen]
> You can use backup_set_id on restorehistory and media_set_id on backupset to
> find the record on backupmediafamily.
> Hope this helps,
> Ben Nevarez
>
>
> "Igor Marchenko" wrote:
sql

Backup file database was restored from

Hello!
I am trying to find out name of the backup file database was restored from
(I am restoring from DISK), something similar to physical_device_name in
backupmediafamily. restorehistory doesn't have this information.
Any advice is greatly appreciated.
Thanks,
IgorYou can use backup_set_id on restorehistory and media_set_id on backupset to
find the record on backupmediafamily.
Hope this helps,
Ben Nevarez
"Igor Marchenko" wrote:
> Hello!
> I am trying to find out name of the backup file database was restored from
> (I am restoring from DISK), something similar to physical_device_name in
> backupmediafamily. restorehistory doesn't have this information.
> Any advice is greatly appreciated.
> Thanks,
> Igor|||Thanks a lot Ben! That exactly what I was looking for.
Igor
"Ben Nevarez" wrote:
> You can use backup_set_id on restorehistory and media_set_id on backupset to
> find the record on backupmediafamily.
> Hope this helps,
> Ben Nevarez
>
>
> "Igor Marchenko" wrote:
> > Hello!
> >
> > I am trying to find out name of the backup file database was restored from
> > (I am restoring from DISK), something similar to physical_device_name in
> > backupmediafamily. restorehistory doesn't have this information.
> > Any advice is greatly appreciated.
> >
> > Thanks,
> > Igor

backup file being reported as corrupt

I am backing up my databases every night. The system admin has a process running where he copies any new files on the drive to a tape drive for backup.

Twice now he has told me that a backup file was corrupt. In both cases, my jobs and processes are still running clean. DBCC checkdb returns no errors. Where is the best place / tool to check for corruption in tables?

Corruption was occurring in the backup process and was not related to SQL Server itself.

backup file being reported as corrupt

I am backing up my databases every night. The system admin has a process running where he copies any new files on the drive to a tape drive for backup.

Twice now he has told me that a backup file was corrupt. In both cases, my jobs and processes are still running clean. DBCC checkdb returns no errors. Where is the best place / tool to check for corruption in tables?

Corruption was occurring in the backup process and was not related to SQL Server itself.

Backup file and database name

Hi
I have a backup file.
How can I find that to which database this backup is related?
I want to do it programmaitcally.
thanks in advanceRESTORE HEADERONLY
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"perspolis" <rezarms@.hotmail.com> wrote in message news:OGbhokv0FHA.612@.TK2MSFTNGP10.phx.gb
l...
> Hi
> I have a backup file.
> How can I find that to which database this backup is related?
> I want to do it programmaitcally.
> thanks in advance
>|||Perspolis,
Might consider using Enterprise Manager for your restores - much easier and
much less coding.
HTH
Jerry
"perspolis" <rezarms@.hotmail.com> wrote in message
news:OGbhokv0FHA.612@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have a backup file.
> How can I find that to which database this backup is related?
> I want to do it programmaitcally.
> thanks in advance
>

Backup Failure - Delete old files

I have setup my backup job to delete file older than 12 hours. I want to
keep atleast 2 backup files saved. The backup runs every night.
My backup job fails on alternate days.
The day it fails, it completes just 2 steps 'backup database' and 'verify
database'.
The day is completes sucessfully, it completes 4 steps 'backup database' and
'verify database' and 'delete old backup files' and 'check data and index
linkage'.
It started failing for last few months. The same setting used to work fine
before.
Any kind of help is greatly appreciated.
What is the error message you get at 3rd step?
"helpplease" wrote:

> I have setup my backup job to delete file older than 12 hours. I want to
> keep atleast 2 backup files saved. The backup runs every night.
> My backup job fails on alternate days.
> The day it fails, it completes just 2 steps 'backup database' and 'verify
> database'.
> The day is completes sucessfully, it completes 4 steps 'backup database' and
> 'verify database' and 'delete old backup files' and 'check data and index
> linkage'.
> It started failing for last few months. The same setting used to work fine
> before.
> Any kind of help is greatly appreciated.
|||Does not show the description of error inside the job or the maintenance
plan. The following error message is shown on the Application event log.
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Insite
Maint'' (0xE5E76C0F4E0B7546BB973E5A02710F7F) - Status: Failed - Invoked on:
2006-02-20 21:00:00 - Message: The job failed. The Job was invoked by
Schedule 17 (Schedule 1). The last step to run was step 1 (Step 1).
"bluefish" wrote:
[vbcol=seagreen]
> What is the error message you get at 3rd step?
>
> "helpplease" wrote:
|||Specify a report file for the maint plan and check the report file for the error messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:E2EA51C2-6ED1-4EF6-BA39-FEF7D275245E@.microsoft.com...[vbcol=seagreen]
> Does not show the description of error inside the job or the maintenance
> plan. The following error message is shown on the Application event log.
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Insite
> Maint'' (0xE5E76C0F4E0B7546BB973E5A02710F7F) - Status: Failed - Invoked on:
> 2006-02-20 21:00:00 - Message: The job failed. The Job was invoked by
> Schedule 17 (Schedule 1). The last step to run was step 1 (Step 1).
>
> "bluefish" wrote:

Backup Failure - Delete old files

I have setup my backup job to delete file older than 12 hours. I want to
keep atleast 2 backup files saved. The backup runs every night.
My backup job fails on alternate days.
The day it fails, it completes just 2 steps 'backup database' and 'verify
database'.
The day is completes sucessfully, it completes 4 steps 'backup database' and
'verify database' and 'delete old backup files' and 'check data and index
linkage'.
It started failing for last few months. The same setting used to work fine
before.
Any kind of help is greatly appreciated.What is the error message you get at 3rd step?
"helpplease" wrote:
> I have setup my backup job to delete file older than 12 hours. I want to
> keep atleast 2 backup files saved. The backup runs every night.
> My backup job fails on alternate days.
> The day it fails, it completes just 2 steps 'backup database' and 'verify
> database'.
> The day is completes sucessfully, it completes 4 steps 'backup database' and
> 'verify database' and 'delete old backup files' and 'check data and index
> linkage'.
> It started failing for last few months. The same setting used to work fine
> before.
> Any kind of help is greatly appreciated.|||Does not show the description of error inside the job or the maintenance
plan. The following error message is shown on the Application event log.
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Insite
Maint'' (0xE5E76C0F4E0B7546BB973E5A02710F7F) - Status: Failed - Invoked on:
2006-02-20 21:00:00 - Message: The job failed. The Job was invoked by
Schedule 17 (Schedule 1). The last step to run was step 1 (Step 1).
"bluefish" wrote:
> What is the error message you get at 3rd step?
>
> "helpplease" wrote:
> > I have setup my backup job to delete file older than 12 hours. I want to
> > keep atleast 2 backup files saved. The backup runs every night.
> > My backup job fails on alternate days.
> >
> > The day it fails, it completes just 2 steps 'backup database' and 'verify
> > database'.
> >
> > The day is completes sucessfully, it completes 4 steps 'backup database' and
> > 'verify database' and 'delete old backup files' and 'check data and index
> > linkage'.
> >
> > It started failing for last few months. The same setting used to work fine
> > before.
> >
> > Any kind of help is greatly appreciated.|||Specify a report file for the maint plan and check the report file for the error messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:E2EA51C2-6ED1-4EF6-BA39-FEF7D275245E@.microsoft.com...
> Does not show the description of error inside the job or the maintenance
> plan. The following error message is shown on the Application event log.
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Insite
> Maint'' (0xE5E76C0F4E0B7546BB973E5A02710F7F) - Status: Failed - Invoked on:
> 2006-02-20 21:00:00 - Message: The job failed. The Job was invoked by
> Schedule 17 (Schedule 1). The last step to run was step 1 (Step 1).
>
> "bluefish" wrote:
>> What is the error message you get at 3rd step?
>>
>> "helpplease" wrote:
>> > I have setup my backup job to delete file older than 12 hours. I want to
>> > keep atleast 2 backup files saved. The backup runs every night.
>> > My backup job fails on alternate days.
>> >
>> > The day it fails, it completes just 2 steps 'backup database' and 'verify
>> > database'.
>> >
>> > The day is completes sucessfully, it completes 4 steps 'backup database' and
>> > 'verify database' and 'delete old backup files' and 'check data and index
>> > linkage'.
>> >
>> > It started failing for last few months. The same setting used to work fine
>> > before.
>> >
>> > Any kind of help is greatly appreciated.

Backup Failure - Delete old files

I have setup my backup job to delete file older than 12 hours. I want to
keep atleast 2 backup files saved. The backup runs every night.
My backup job fails on alternate days.
The day it fails, it completes just 2 steps 'backup database' and 'verify
database'.
The day is completes sucessfully, it completes 4 steps 'backup database' and
'verify database' and 'delete old backup files' and 'check data and index
linkage'.
It started failing for last few months. The same setting used to work fine
before.
Any kind of help is greatly appreciated.What is the error message you get at 3rd step?
"helpplease" wrote:

> I have setup my backup job to delete file older than 12 hours. I want to
> keep atleast 2 backup files saved. The backup runs every night.
> My backup job fails on alternate days.
> The day it fails, it completes just 2 steps 'backup database' and 'verify
> database'.
> The day is completes sucessfully, it completes 4 steps 'backup database' a
nd
> 'verify database' and 'delete old backup files' and 'check data and index
> linkage'.
> It started failing for last few months. The same setting used to work fin
e
> before.
> Any kind of help is greatly appreciated.|||Does not show the description of error inside the job or the maintenance
plan. The following error message is shown on the Application event log.
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Insite
Maint'' (0xE5E76C0F4E0B7546BB973E5A02710F7F) - Status: Failed - Invoked on:
2006-02-20 21:00:00 - Message: The job failed. The Job was invoked by
Schedule 17 (Schedule 1). The last step to run was step 1 (Step 1).
"bluefish" wrote:
[vbcol=seagreen]
> What is the error message you get at 3rd step?
>
> "helpplease" wrote:
>|||Specify a report file for the maint plan and check the report file for the e
rror messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:E2EA51C2-6ED1-4EF6-BA39-FEF7D275245E@.microsoft.com...[vbcol=seagreen]
> Does not show the description of error inside the job or the maintenance
> plan. The following error message is shown on the Application event log.
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Insite
> Maint'' (0xE5E76C0F4E0B7546BB973E5A02710F7F) - Status: Failed - Invoked on
:
> 2006-02-20 21:00:00 - Message: The job failed. The Job was invoked by
> Schedule 17 (Schedule 1). The last step to run was step 1 (Step 1).
>
> "bluefish" wrote:
>

Backup fails: "A nonrecoverable I/O error occurred on file"

We have been having a problem with one of our backups:
A nonrecoverable I/O error occurred on file "D:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\SOMEDB_Data.mdf"
It's on the same external storage drive as all of our databases yet it's the
only one that errors out, so it appears that the problem is with the .mdf
file, not with the hard disk. And it only errors intermittently...sometimes
the backup succeeds.
We have:
Run dbcc checkdb and receive no errors.
Dropped the database and restored from backup thinking the .mdf file would
be recreated.
We're not sure what to try next. Again, it doesn't seem like a hardware
problem but I can't rule it out either. Please advise.
"nexdeveloper" <nexdeveloper@.community.nospam> wrote in message
news:E0948BFC-C002-4EBF-9E00-B0EAF6A8E850@.microsoft.com...
> We have been having a problem with one of our backups:
> A nonrecoverable I/O error occurred on file "D:\Program Files\Microsoft
> SQL
> Server\MSSQL.1\MSSQL\Data\SOMEDB_Data.mdf"
> It's on the same external storage drive as all of our databases yet it's
> the
> only one that errors out, so it appears that the problem is with the .mdf
> file, not with the hard disk. And it only errors
> intermittently...sometimes
> the backup succeeds.
> We have:
> Run dbcc checkdb and receive no errors.
> Dropped the database and restored from backup thinking the .mdf file would
> be recreated.
> We're not sure what to try next. Again, it doesn't seem like a hardware
> problem but I can't rule it out either. Please advise.
Is this a SAN drive per chance? Or any special SCSI drive that may be doing
something while you do a backup?
Otherwise you may need to open a ticket with MS.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Hello,
Since the issue only occurs iwht one mdf file, it might be a corruption
issue. Since the issue also occurs with a restore from backup, you may try
the following steps to troubleshoot the issue:
1. Create a new, blank database with whatever name and filename you want
(we may rename it later)
2. Generate the scripts of all tables and create them in the new database;
3. Transfer all data into the new database;
4. Check if the issue occurs with the new database
If the issue does not occur any more, you may want to detach the old
database, and rename the new database to the original name to test.
If the issue persists, you may want to use hardware check tool to confirm
there is no hardware related issue.
To find out the root cause of this issue we may need to analyze memory
dumps, this work has to be done by contacting Microsoft Product Support
Services. Therefore, we probably will not be able to resolve the issue
through the newsgroups. I recommend that you open a Support incident with
Microsoft Product Support Services so that a dedicated Support Professional
can assist with this case. If you need any help in this regard, please let
me know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
If you have any concerns or comments on this, please feel free to let's
know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Yes this a SAN drive at a remote datacenter. The backup runs in the middle
of the night so I don't think the SAN has any heavy lifting going on at the
time the backup runs. We're going to continue to investigate and open a
ticket with MS if appropriate.
"Greg D. Moore (Strider)" wrote:

>
>
> "nexdeveloper" <nexdeveloper@.community.nospam> wrote in message
> news:E0948BFC-C002-4EBF-9E00-B0EAF6A8E850@.microsoft.com...
> Is this a SAN drive per chance? Or any special SCSI drive that may be doing
> something while you do a backup?
> Otherwise you may need to open a ticket with MS.
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>
|||We're going to continue to investigate and open a ticket with MS if
appropriate. Thanks for the response...I'll let you know if I have further
questions.
"Peter Yang [MSFT]" wrote:

> Hello,
> Since the issue only occurs iwht one mdf file, it might be a corruption
> issue. Since the issue also occurs with a restore from backup, you may try
> the following steps to troubleshoot the issue:
> 1. Create a new, blank database with whatever name and filename you want
> (we may rename it later)
> 2. Generate the scripts of all tables and create them in the new database;
> 3. Transfer all data into the new database;
> 4. Check if the issue occurs with the new database
> If the issue does not occur any more, you may want to detach the old
> database, and rename the new database to the original name to test.
> If the issue persists, you may want to use hardware check tool to confirm
> there is no hardware related issue.
> To find out the root cause of this issue we may need to analyze memory
> dumps, this work has to be done by contacting Microsoft Product Support
> Services. Therefore, we probably will not be able to resolve the issue
> through the newsgroups. I recommend that you open a Support incident with
> Microsoft Product Support Services so that a dedicated Support Professional
> can assist with this case. If you need any help in this regard, please let
> me know.
> For a complete list of Microsoft Product Support Services phone numbers,
> please go to the following address on the World Wide Web:
> http://support.microsoft.com/directory/overview.asp
> If you have any concerns or comments on this, please feel free to let's
> know. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
|||"nexdeveloper" <nexdeveloper@.community.nospam> wrote in message
news:B86FFC11-4C68-462B-88B5-3C9B640369DF@.microsoft.com...
> Yes this a SAN drive at a remote datacenter. The backup runs in the
> middle
> of the night so I don't think the SAN has any heavy lifting going on at
> the
> time the backup runs. We're going to continue to investigate and open a
> ticket with MS if appropriate.
Was thinking more perhaps the SAN drive might be doing something like
breaking a mirror during that time to do a backup and then recreating the
mirror or some other function and not properly quiescing the database.
[vbcol=seagreen]
> "Greg D. Moore (Strider)" wrote:
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Backup fails: "A nonrecoverable I/O error occurred on file"

We have been having a problem with one of our backups:
A nonrecoverable I/O error occurred on file "D:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\SOMEDB_Data.mdf"
It's on the same external storage drive as all of our databases yet it's the
only one that errors out, so it appears that the problem is with the .mdf
file, not with the hard disk. And it only errors intermittently...sometimes
the backup succeeds.
We have:
Run dbcc checkdb and receive no errors.
Dropped the database and restored from backup thinking the .mdf file would
be recreated.
We're not sure what to try next. Again, it doesn't seem like a hardware
problem but I can't rule it out either. Please advise."nexdeveloper" <nexdeveloper@.community.nospam> wrote in message
news:E0948BFC-C002-4EBF-9E00-B0EAF6A8E850@.microsoft.com...
> We have been having a problem with one of our backups:
> A nonrecoverable I/O error occurred on file "D:\Program Files\Microsoft
> SQL
> Server\MSSQL.1\MSSQL\Data\SOMEDB_Data.mdf"
> It's on the same external storage drive as all of our databases yet it's
> the
> only one that errors out, so it appears that the problem is with the .mdf
> file, not with the hard disk. And it only errors
> intermittently...sometimes
> the backup succeeds.
> We have:
> Run dbcc checkdb and receive no errors.
> Dropped the database and restored from backup thinking the .mdf file would
> be recreated.
> We're not sure what to try next. Again, it doesn't seem like a hardware
> problem but I can't rule it out either. Please advise.
Is this a SAN drive per chance? Or any special SCSI drive that may be doing
something while you do a backup?
Otherwise you may need to open a ticket with MS.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hello,
Since the issue only occurs iwht one mdf file, it might be a corruption
issue. Since the issue also occurs with a restore from backup, you may try
the following steps to troubleshoot the issue:
1. Create a new, blank database with whatever name and filename you want
(we may rename it later)
2. Generate the scripts of all tables and create them in the new database;
3. Transfer all data into the new database;
4. Check if the issue occurs with the new database
If the issue does not occur any more, you may want to detach the old
database, and rename the new database to the original name to test.
If the issue persists, you may want to use hardware check tool to confirm
there is no hardware related issue.
To find out the root cause of this issue we may need to analyze memory
dumps, this work has to be done by contacting Microsoft Product Support
Services. Therefore, we probably will not be able to resolve the issue
through the newsgroups. I recommend that you open a Support incident with
Microsoft Product Support Services so that a dedicated Support Professional
can assist with this case. If you need any help in this regard, please let
me know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
If you have any concerns or comments on this, please feel free to let's
know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Yes this a SAN drive at a remote datacenter. The backup runs in the middle
of the night so I don't think the SAN has any heavy lifting going on at the
time the backup runs. We're going to continue to investigate and open a
ticket with MS if appropriate.
"Greg D. Moore (Strider)" wrote:

>
>
> "nexdeveloper" <nexdeveloper@.community.nospam> wrote in message
> news:E0948BFC-C002-4EBF-9E00-B0EAF6A8E850@.microsoft.com...
> Is this a SAN drive per chance? Or any special SCSI drive that may be doi
ng
> something while you do a backup?
> Otherwise you may need to open a ticket with MS.
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com [url]http://www.greenms.com/sqlserver.html[/ur
l]
>
>|||We're going to continue to investigate and open a ticket with MS if
appropriate. Thanks for the response...I'll let you know if I have further
questions.
"Peter Yang [MSFT]" wrote:

> Hello,
> Since the issue only occurs iwht one mdf file, it might be a corruption
> issue. Since the issue also occurs with a restore from backup, you may try
> the following steps to troubleshoot the issue:
> 1. Create a new, blank database with whatever name and filename you want
> (we may rename it later)
> 2. Generate the scripts of all tables and create them in the new database;
> 3. Transfer all data into the new database;
> 4. Check if the issue occurs with the new database
> If the issue does not occur any more, you may want to detach the old
> database, and rename the new database to the original name to test.
> If the issue persists, you may want to use hardware check tool to confirm
> there is no hardware related issue.
> To find out the root cause of this issue we may need to analyze memory
> dumps, this work has to be done by contacting Microsoft Product Support
> Services. Therefore, we probably will not be able to resolve the issue
> through the newsgroups. I recommend that you open a Support incident with
> Microsoft Product Support Services so that a dedicated Support Professiona
l
> can assist with this case. If you need any help in this regard, please let
> me know.
> For a complete list of Microsoft Product Support Services phone numbers,
> please go to the following address on the World Wide Web:
> http://support.microsoft.com/directory/overview.asp
> If you have any concerns or comments on this, please feel free to let's
> know. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ========================================
==========
> Get notification to my posts through email? Please refer to
> l]
> ications
> <[url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx" target="_blank">http://msdn.microsoft.com/subscript...ps/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscript...rt/default.aspx>.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
>|||"nexdeveloper" <nexdeveloper@.community.nospam> wrote in message
news:B86FFC11-4C68-462B-88B5-3C9B640369DF@.microsoft.com...
> Yes this a SAN drive at a remote datacenter. The backup runs in the
> middle
> of the night so I don't think the SAN has any heavy lifting going on at
> the
> time the backup runs. We're going to continue to investigate and open a
> ticket with MS if appropriate.
Was thinking more perhaps the SAN drive might be doing something like
breaking a mirror during that time to do a backup and then recreating the
mirror or some other function and not properly quiescing the database.
[vbcol=seagreen]
> "Greg D. Moore (Strider)" wrote:
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Backup fails: "A nonrecoverable I/O error occurred on file"

We have been having a problem with one of our backups:
A nonrecoverable I/O error occurred on file "D:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\SOMEDB_Data.mdf"
It's on the same external storage drive as all of our databases yet it's the
only one that errors out, so it appears that the problem is with the .mdf
file, not with the hard disk. And it only errors intermittently...sometimes
the backup succeeds.
We have:
Run dbcc checkdb and receive no errors.
Dropped the database and restored from backup thinking the .mdf file would
be recreated.
We're not sure what to try next. Again, it doesn't seem like a hardware
problem but I can't rule it out either. Please advise."nexdeveloper" <nexdeveloper@.community.nospam> wrote in message
news:E0948BFC-C002-4EBF-9E00-B0EAF6A8E850@.microsoft.com...
> We have been having a problem with one of our backups:
> A nonrecoverable I/O error occurred on file "D:\Program Files\Microsoft
> SQL
> Server\MSSQL.1\MSSQL\Data\SOMEDB_Data.mdf"
> It's on the same external storage drive as all of our databases yet it's
> the
> only one that errors out, so it appears that the problem is with the .mdf
> file, not with the hard disk. And it only errors
> intermittently...sometimes
> the backup succeeds.
> We have:
> Run dbcc checkdb and receive no errors.
> Dropped the database and restored from backup thinking the .mdf file would
> be recreated.
> We're not sure what to try next. Again, it doesn't seem like a hardware
> problem but I can't rule it out either. Please advise.
Is this a SAN drive per chance? Or any special SCSI drive that may be doing
something while you do a backup?
Otherwise you may need to open a ticket with MS.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hello,
Since the issue only occurs iwht one mdf file, it might be a corruption
issue. Since the issue also occurs with a restore from backup, you may try
the following steps to troubleshoot the issue:
1. Create a new, blank database with whatever name and filename you want
(we may rename it later)
2. Generate the scripts of all tables and create them in the new database;
3. Transfer all data into the new database;
4. Check if the issue occurs with the new database
If the issue does not occur any more, you may want to detach the old
database, and rename the new database to the original name to test.
If the issue persists, you may want to use hardware check tool to confirm
there is no hardware related issue.
To find out the root cause of this issue we may need to analyze memory
dumps, this work has to be done by contacting Microsoft Product Support
Services. Therefore, we probably will not be able to resolve the issue
through the newsgroups. I recommend that you open a Support incident with
Microsoft Product Support Services so that a dedicated Support Professional
can assist with this case. If you need any help in this regard, please let
me know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
If you have any concerns or comments on this, please feel free to let's
know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Yes this a SAN drive at a remote datacenter. The backup runs in the middle
of the night so I don't think the SAN has any heavy lifting going on at the
time the backup runs. We're going to continue to investigate and open a
ticket with MS if appropriate.
"Greg D. Moore (Strider)" wrote:
>
>
> "nexdeveloper" <nexdeveloper@.community.nospam> wrote in message
> news:E0948BFC-C002-4EBF-9E00-B0EAF6A8E850@.microsoft.com...
> > We have been having a problem with one of our backups:
> >
> > A nonrecoverable I/O error occurred on file "D:\Program Files\Microsoft
> > SQL
> > Server\MSSQL.1\MSSQL\Data\SOMEDB_Data.mdf"
> >
> > It's on the same external storage drive as all of our databases yet it's
> > the
> > only one that errors out, so it appears that the problem is with the .mdf
> > file, not with the hard disk. And it only errors
> > intermittently...sometimes
> > the backup succeeds.
> >
> > We have:
> >
> > Run dbcc checkdb and receive no errors.
> > Dropped the database and restored from backup thinking the .mdf file would
> > be recreated.
> >
> > We're not sure what to try next. Again, it doesn't seem like a hardware
> > problem but I can't rule it out either. Please advise.
> Is this a SAN drive per chance? Or any special SCSI drive that may be doing
> something while you do a backup?
> Otherwise you may need to open a ticket with MS.
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>|||We're going to continue to investigate and open a ticket with MS if
appropriate. Thanks for the response...I'll let you know if I have further
questions.
"Peter Yang [MSFT]" wrote:
> Hello,
> Since the issue only occurs iwht one mdf file, it might be a corruption
> issue. Since the issue also occurs with a restore from backup, you may try
> the following steps to troubleshoot the issue:
> 1. Create a new, blank database with whatever name and filename you want
> (we may rename it later)
> 2. Generate the scripts of all tables and create them in the new database;
> 3. Transfer all data into the new database;
> 4. Check if the issue occurs with the new database
> If the issue does not occur any more, you may want to detach the old
> database, and rename the new database to the original name to test.
> If the issue persists, you may want to use hardware check tool to confirm
> there is no hardware related issue.
> To find out the root cause of this issue we may need to analyze memory
> dumps, this work has to be done by contacting Microsoft Product Support
> Services. Therefore, we probably will not be able to resolve the issue
> through the newsgroups. I recommend that you open a Support incident with
> Microsoft Product Support Services so that a dedicated Support Professional
> can assist with this case. If you need any help in this regard, please let
> me know.
> For a complete list of Microsoft Product Support Services phone numbers,
> please go to the following address on the World Wide Web:
> http://support.microsoft.com/directory/overview.asp
> If you have any concerns or comments on this, please feel free to let's
> know. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||"nexdeveloper" <nexdeveloper@.community.nospam> wrote in message
news:B86FFC11-4C68-462B-88B5-3C9B640369DF@.microsoft.com...
> Yes this a SAN drive at a remote datacenter. The backup runs in the
> middle
> of the night so I don't think the SAN has any heavy lifting going on at
> the
> time the backup runs. We're going to continue to investigate and open a
> ticket with MS if appropriate.
Was thinking more perhaps the SAN drive might be doing something like
breaking a mirror during that time to do a backup and then recreating the
mirror or some other function and not properly quiescing the database.
> "Greg D. Moore (Strider)" wrote:
>>
>>
>> "nexdeveloper" <nexdeveloper@.community.nospam> wrote in message
>> news:E0948BFC-C002-4EBF-9E00-B0EAF6A8E850@.microsoft.com...
>> > We have been having a problem with one of our backups:
>> >
>> > A nonrecoverable I/O error occurred on file "D:\Program Files\Microsoft
>> > SQL
>> > Server\MSSQL.1\MSSQL\Data\SOMEDB_Data.mdf"
>> >
>> > It's on the same external storage drive as all of our databases yet
>> > it's
>> > the
>> > only one that errors out, so it appears that the problem is with the
>> > .mdf
>> > file, not with the hard disk. And it only errors
>> > intermittently...sometimes
>> > the backup succeeds.
>> >
>> > We have:
>> >
>> > Run dbcc checkdb and receive no errors.
>> > Dropped the database and restored from backup thinking the .mdf file
>> > would
>> > be recreated.
>> >
>> > We're not sure what to try next. Again, it doesn't seem like a
>> > hardware
>> > problem but I can't rule it out either. Please advise.
>> Is this a SAN drive per chance? Or any special SCSI drive that may be
>> doing
>> something while you do a backup?
>> Otherwise you may need to open a ticket with MS.
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting Remote and Onsite available!
>> Email: sql (at) greenms.com
>> http://www.greenms.com/sqlserver.html
>>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.htmlsql

backup exec fails (though having the sql addon)

Hi group
Working with backup exec 10, sql agent and advanced open file options
included to backup our databases. We have 11 SQL databases varying in size
from 200 to 5000mb. There is one new dbase which is the database for a
website that is now not being backed up (size, approx 1.5 GB), this has
never worked since it was created last week.
(for now we are doing a full backup dump in non production hours to a
location that is backed up)
Additional info:
-Backupexec runs under a domain admin account
-SQL Server runs under another account
-owner for databases is domain admin
-2003 server for SQL (2000/sp3a) and the webserver (IIS6.0)
-backupexec shows fileskipped messages without specifying the file
-no usable info in eventvwr
-there are jobs running on this database (although not when the backup is
run)
-no locks/active connections are shown in the enterprise manager
Are there any issues with sql databases that are used by websites?
Has anybody have experienced similar problems and have a sollution available
that might work for me?
thx.
PerryPerry,
When you view the history for the backup job with BackupExec what error
message is recorded, if any?
> (for now we are doing a full backup dump in non production hours to a
> location that is backed up)
Using? Are you using Enterprise Manager? If not, does that work? Any
errors in the SQL errorlog for the backup failing?
HTH
Jerry
"Perry" <reply@.to.ng> wrote in message
news:ejIiKnUxFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi group
> Working with backup exec 10, sql agent and advanced open file options
> included to backup our databases. We have 11 SQL databases varying in size
> from 200 to 5000mb. There is one new dbase which is the database for a
> website that is now not being backed up (size, approx 1.5 GB), this has
> never worked since it was created last week.
> (for now we are doing a full backup dump in non production hours to a
> location that is backed up)
> Additional info:
> -Backupexec runs under a domain admin account
> -SQL Server runs under another account
> -owner for databases is domain admin
> -2003 server for SQL (2000/sp3a) and the webserver (IIS6.0)
> -backupexec shows fileskipped messages without specifying the file
> -no usable info in eventvwr
> -there are jobs running on this database (although not when the backup is
> run)
> -no locks/active connections are shown in the enterprise manager
> Are there any issues with sql databases that are used by websites?
> Has anybody have experienced similar problems and have a sollution
> available that might work for me?
> thx.
> Perry
>
>|||Hi
Never use open file options against a SQL Server database as the DB and
Transaction log will never be restorable as they will not be in sync at a
point in time.
Backup the database to disk from within SQL Server (see BACKUP command in
BOL), or use the SQL Server maintenance wizard.
Backup the backup file files that SQL Server generated.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Perry" <reply@.to.ng> wrote in message
news:ejIiKnUxFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi group
> Working with backup exec 10, sql agent and advanced open file options
> included to backup our databases. We have 11 SQL databases varying in size
> from 200 to 5000mb. There is one new dbase which is the database for a
> website that is now not being backed up (size, approx 1.5 GB), this has
> never worked since it was created last week.
> (for now we are doing a full backup dump in non production hours to a
> location that is backed up)
> Additional info:
> -Backupexec runs under a domain admin account
> -SQL Server runs under another account
> -owner for databases is domain admin
> -2003 server for SQL (2000/sp3a) and the webserver (IIS6.0)
> -backupexec shows fileskipped messages without specifying the file
> -no usable info in eventvwr
> -there are jobs running on this database (although not when the backup is
> run)
> -no locks/active connections are shown in the enterprise manager
> Are there any issues with sql databases that are used by websites?
> Has anybody have experienced similar problems and have a sollution
> available that might work for me?
> thx.
> Perry
>
>|||Mike,
I have this same argument with my IT director. It's been one of those he
won't budge...I won't budge thing so...Veritas does have an agent for SQL
backups. We have the license but I've chosen not to use it (yet) not
because it doesn't work but because it is a bit more challenging and
involved. I do backup the databases using EM (jobs) then I use Veritas
BackupExec to backup the backup files to tape for off-site storage.
HTH
Jerry
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ObMOEeVxFHA.3756@.tk2msftngp13.phx.gbl...
> Hi
> Never use open file options against a SQL Server database as the DB and
> Transaction log will never be restorable as they will not be in sync at a
> point in time.
> Backup the database to disk from within SQL Server (see BACKUP command in
> BOL), or use the SQL Server maintenance wizard.
> Backup the backup file files that SQL Server generated.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Perry" <reply@.to.ng> wrote in message
> news:ejIiKnUxFHA.1032@.TK2MSFTNGP12.phx.gbl...
>> Hi group
>> Working with backup exec 10, sql agent and advanced open file options
>> included to backup our databases. We have 11 SQL databases varying in
>> size from 200 to 5000mb. There is one new dbase which is the database for
>> a website that is now not being backed up (size, approx 1.5 GB), this has
>> never worked since it was created last week.
>> (for now we are doing a full backup dump in non production hours to a
>> location that is backed up)
>> Additional info:
>> -Backupexec runs under a domain admin account
>> -SQL Server runs under another account
>> -owner for databases is domain admin
>> -2003 server for SQL (2000/sp3a) and the webserver (IIS6.0)
>> -backupexec shows fileskipped messages without specifying the file
>> -no usable info in eventvwr
>> -there are jobs running on this database (although not when the backup is
>> run)
>> -no locks/active connections are shown in the enterprise manager
>> Are there any issues with sql databases that are used by websites?
>> Has anybody have experienced similar problems and have a sollution
>> available that might work for me?
>> thx.
>> Perry
>>
>>
>|||Jerry,
Below is what the log says, the servername is DC4, the databasename is
SYNERGY:
Job Completion Status
Job ended: Wednesday, September 28, 2005 at 8:36:41 PM
Completed status: Completed with exceptions
Errors
Click an error below to locate it in the job log
Backup- DC4 Database ?SYNERGY was not found, or could not be accessed.
Exceptions
Click an exception below to locate it in the job log
Backup- DC4 The item DC4\SYNERGY in use - skipped.Perry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u4j16tUxFHA.3740@.tk2msftngp13.phx.gbl...
> Perry,
> When you view the history for the backup job with BackupExec what error
> message is recorded, if any?
>> (for now we are doing a full backup dump in non production hours to a
>> location that is backed up)
> Using? Are you using Enterprise Manager? If not, does that work? Any
> errors in the SQL errorlog for the backup failing?
> HTH
> Jerry
> "Perry" <reply@.to.ng> wrote in message
> news:ejIiKnUxFHA.1032@.TK2MSFTNGP12.phx.gbl...
>> Hi group
>> Working with backup exec 10, sql agent and advanced open file options
>> included to backup our databases. We have 11 SQL databases varying in
>> size from 200 to 5000mb. There is one new dbase which is the database for
>> a website that is now not being backed up (size, approx 1.5 GB), this has
>> never worked since it was created last week.
>> (for now we are doing a full backup dump in non production hours to a
>> location that is backed up)
>> Additional info:
>> -Backupexec runs under a domain admin account
>> -SQL Server runs under another account
>> -owner for databases is domain admin
>> -2003 server for SQL (2000/sp3a) and the webserver (IIS6.0)
>> -backupexec shows fileskipped messages without specifying the file
>> -no usable info in eventvwr
>> -there are jobs running on this database (although not when the backup is
>> run)
>> -no locks/active connections are shown in the enterprise manager
>> Are there any issues with sql databases that are used by websites?
>> Has anybody have experienced similar problems and have a sollution
>> available that might work for me?
>> thx.
>> Perry
>>
>>
>