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 Failures

3266 :
The backup data in 'ThursdaylogsAM' is incorrectly formatted. Backups cannot
be appended, but existing backup sets may still be usable.
3041 :
BACKUP failed to complete the command BACKUP LOG [GIS] TO [ThursdaylogsAM]
WITH INIT , NOUNLOAD , NAME = N'ThursdaylogsAM', SKIP , STATS = 10,
DESCRIPTION = N'ThursdaylogsAM', NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='GIS'and
type!='F'
I have a daily backup procedure that started to fail. It was created by
another technician so I don't know if he copied it or used a Wizard to create
it. For no apparent reason, it fails with thi smessage but only on certain
days. There is an individual procedure for each day of the week and other
than the name of the directory/file being written to, all instructions are
the same. i can't figure this one out!!!
Do you see any errors in the sql error log? And the application logs at the
same time? Assuming this was run perhaps as a sqlagent job, does the
sqlagent.out have any error message?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
sql

Backup Failures

3266 :
The backup data in 'ThursdaylogsAM' is incorrectly formatted. Backups cannot
be appended, but existing backup sets may still be usable.
3041 :
BACKUP failed to complete the command BACKUP LOG [GIS] TO [ThursdaylogsAM]
WITH INIT , NOUNLOAD , NAME = N'ThursdaylogsAM', SKIP , STATS = 10,
DESCRIPTION = N'ThursdaylogsAM', NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='GIS'and
type!='F'
I have a daily backup procedure that started to fail. It was created by
another technician so I don't know if he copied it or used a Wizard to create
it. For no apparent reason, it fails with thi smessage but only on certain
days. There is an individual procedure for each day of the week and other
than the name of the directory/file being written to, all instructions are
the same. i can't figure this one out!!!Do you see any errors in the sql error log? And the application logs at the
same time? Assuming this was run perhaps as a sqlagent job, does the
sqlagent.out have any error message?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Backup failure Error Log Messages

I have not been able to find any details about backup failures for
MSSQL 2000. I am researching the following:
1. when a backup fails at a certain point during the process does the
backup:
A. stop there having recorded some details?
B. quit without backing up any of the data it already processed?
C. skip the data it cannot read and move on?
2. Does the error log record any of the details above? For instance, if
there is a table that the backup could not write to .bak, does the
error log report exactly where the error was?

I need a way to obtain a Backup Exception 'Report' that when a backup
fails it can tell me what did not get into the .bak file. I would
prefer automatic notification. Our Oracle DBs have something setup
that can be run when a piece of the DB does not get backed up letting
the Oracle DBA know where there are issues.

Thank you,
StephanieHi
1. B
2. If it is a write failure, yes it will be in the SQL Log. If it is data
corruption, it is in the SQL Log.

For a backup to fail, due to internal SQL Server problems is very very rare,
and I have not seen it on a clean database before.

Most failures are caused by running out of disk space or anti virus messing
around on the file when SQL is trying to write to it.

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/

"Stephanie" <stuttle@.centene.com> wrote in message
news:1123710578.824686.208370@.g47g2000cwa.googlegr oups.com...
>I have not been able to find any details about backup failures for
> MSSQL 2000. I am researching the following:
> 1. when a backup fails at a certain point during the process does the
> backup:
> A. stop there having recorded some details?
> B. quit without backing up any of the data it already processed?
> C. skip the data it cannot read and move on?
> 2. Does the error log record any of the details above? For instance, if
> there is a table that the backup could not write to .bak, does the
> error log report exactly where the error was?
> I need a way to obtain a Backup Exception 'Report' that when a backup
> fails it can tell me what did not get into the .bak file. I would
> prefer automatic notification. Our Oracle DBs have something setup
> that can be run when a piece of the DB does not get backed up letting
> the Oracle DBA know where there are issues.
> Thank you,
> Stephanie

Backup Failure - Operating system error 64

I am receiving the below error message while doing a Database backup as a
schedule dts package job.
Operating system error 64(The specified network name is no longer available.).
or
Operating system error 64(error not found).
18210 :
BackupDiskFile::RequestDurableMedia: failure on backup device '
\\SERVER_NAME\SQLBackup\Production\DatabaseName.ba k'. Operating system error
64(error not found).
18210 :
BackupMedium::ReportIoError: write failure on backup device
'\\\SERVER_NAME\SQLBackup\Production\DatabaseName. bak'. Operating system
error 64(error not found).
Please note, I am backing up to a networked drive/location using the below
command as an example, which is not a mapped drive,
BACKUP DATABASE [DatabaseName] TO DISK =
\\SERVER_NAME\SQLBackup\Production\DatabaseName.ba k' WITH INIT
The same scheduled dts package backup job sometimes run successfully at
the scheduled time and most times failed. The job is ran under the SQL Server
Agent account which has full access permission to the backup network drive.
The backup dts package always run successfully when ran manually using my
login account.
Thank you and kind regards
Michael
Michael,
The OS error 64 usually indicates the network problems.
Intermittent loss of connectivity to the share. To ensure that the
backups for the database are taken properly, I recommend you backup to
the local drive and then create a NT job to move the job to the share
upon completion of the SQL Backup. If the Nt copy job fails, then we
can be sure that the problem is because of network connectivity.
Hope that helps.
Venu
"Michael" <Michael@.discussions.microsoft.com> wrote in message news:<9973687E-E0C3-41C6-BAF3-D0C240895A01@.microsoft.com>...
> I am receiving the below error message while doing a Database backup as a
> schedule dts package job.
> Operating system error 64(The specified network name is no longer available.).
> or
> Operating system error 64(error not found).
> 18210 :
> BackupDiskFile::RequestDurableMedia: failure on backup device '
> \\SERVER_NAME\SQLBackup\Production\DatabaseName.ba k'. Operating system error
> 64(error not found).
> 18210 :
> BackupMedium::ReportIoError: write failure on backup device
> '\\\SERVER_NAME\SQLBackup\Production\DatabaseName. bak'. Operating system
> error 64(error not found).
>
> Please note, I am backing up to a networked drive/location using the below
> command as an example, which is not a mapped drive,
> BACKUP DATABASE [DatabaseName] TO DISK =
> \\SERVER_NAME\SQLBackup\Production\DatabaseName.ba k' WITH INIT
> The same scheduled dts package backup job sometimes run successfully at
> the scheduled time and most times failed. The job is ran under the SQL Server
> Agent account which has full access permission to the backup network drive.
> The backup dts package always run successfully when ran manually using my
> login account.
> Thank you and kind regards
> Michael

Backup Failure - Operating system error 64

I am receiving the below error message while doing a Database backup as a
schedule dts package job.
Operating system error 64(The specified network name is no longer available.
).
or
Operating system error 64(error not found).
18210 :
BackupDiskFile::RequestDurableMedia: failure on backup device '
\\SERVER_NAME\SQLBackup\Production\Datab
aseName.bak'. Operating system error
64(error not found).
18210 :
BackupMedium::ReportIoError: write failure on backup device
'\\\SERVER_NAME\SQLBackup\Production\Dat
abaseName.bak'. Operating system
error 64(error not found).
Please note, I am backing up to a networked drive/location using the below
command as an example, which is not a mapped drive,
BACKUP DATABASE [DatabaseName] TO DISK =
\\SERVER_NAME\SQLBackup\Production\Datab
aseName.bak' WITH INIT
The same scheduled dts package backup job sometimes run successfully at
the scheduled time and most times failed. The job is ran under the SQL Serve
r
Agent account which has full access permission to the backup network drive.
The backup dts package always run successfully when ran manually using my
login account.
Thank you and kind regards
MichaelMichael,
The OS error 64 usually indicates the network problems.
Intermittent loss of connectivity to the share. To ensure that the
backups for the database are taken properly, I recommend you backup to
the local drive and then create a NT job to move the job to the share
upon completion of the SQL Backup. If the Nt copy job fails, then we
can be sure that the problem is because of network connectivity.
Hope that helps.
Venu
"Michael" <Michael@.discussions.microsoft.com> wrote in message news:<9973687E-E0C3-41C6-BAF3
-D0C240895A01@.microsoft.com>...
> I am receiving the below error message while doing a Database backup as a
> schedule dts package job.
> Operating system error 64(The specified network name is no longer availabl
e.).
> or
> Operating system error 64(error not found).
> 18210 :
> BackupDiskFile::RequestDurableMedia: failure on backup device '
> \\SERVER_NAME\SQLBackup\Production\Datab
aseName.bak'. Operating system err
or
> 64(error not found).
> 18210 :
> BackupMedium::ReportIoError: write failure on backup device
> '\\\SERVER_NAME\SQLBackup\Production\Dat
abaseName.bak'. Operating system
> error 64(error not found).
>
> Please note, I am backing up to a networked drive/location using the below
> command as an example, which is not a mapped drive,
> BACKUP DATABASE [DatabaseName] TO DISK =
> \\SERVER_NAME\SQLBackup\Production\Datab
aseName.bak' WITH INIT
> The same scheduled dts package backup job sometimes run successfully at
> the scheduled time and most times failed. The job is ran under the SQL Ser
ver
> Agent account which has full access permission to the backup network drive
.
> The backup dts package always run successfully when ran manually using my
> login account.
> Thank you and kind regards
> Michael

Backup Failure - Operating system error 64

I am receiving the below error message while doing a Database backup as a
schedule dts package job.
Operating system error 64(The specified network name is no longer available.).
or
Operating system error 64(error not found).
18210 :
BackupDiskFile::RequestDurableMedia: failure on backup device '
\\SERVER_NAME\SQLBackup\Production\DatabaseName.bak'. Operating system error
64(error not found).
18210 :
BackupMedium::ReportIoError: write failure on backup device
'\\\SERVER_NAME\SQLBackup\Production\DatabaseName.bak'. Operating system
error 64(error not found).
Please note, I am backing up to a networked drive/location using the below
command as an example, which is not a mapped drive,
BACKUP DATABASE [DatabaseName] TO DISK = \\SERVER_NAME\SQLBackup\Production\DatabaseName.bak' WITH INIT
The same scheduled dts package backup job sometimes run successfully at
the scheduled time and most times failed. The job is ran under the SQL Server
Agent account which has full access permission to the backup network drive.
The backup dts package always run successfully when ran manually using my
login account.
Thank you and kind regards
MichaelMichael,
The OS error 64 usually indicates the network problems.
Intermittent loss of connectivity to the share. To ensure that the
backups for the database are taken properly, I recommend you backup to
the local drive and then create a NT job to move the job to the share
upon completion of the SQL Backup. If the Nt copy job fails, then we
can be sure that the problem is because of network connectivity.
Hope that helps.
Venu
"Michael" <Michael@.discussions.microsoft.com> wrote in message news:<9973687E-E0C3-41C6-BAF3-D0C240895A01@.microsoft.com>...
> I am receiving the below error message while doing a Database backup as a
> schedule dts package job.
> Operating system error 64(The specified network name is no longer available.).
> or
> Operating system error 64(error not found).
> 18210 :
> BackupDiskFile::RequestDurableMedia: failure on backup device '
> \\SERVER_NAME\SQLBackup\Production\DatabaseName.bak'. Operating system error
> 64(error not found).
> 18210 :
> BackupMedium::ReportIoError: write failure on backup device
> '\\\SERVER_NAME\SQLBackup\Production\DatabaseName.bak'. Operating system
> error 64(error not found).
>
> Please note, I am backing up to a networked drive/location using the below
> command as an example, which is not a mapped drive,
> BACKUP DATABASE [DatabaseName] TO DISK => \\SERVER_NAME\SQLBackup\Production\DatabaseName.bak' WITH INIT
> The same scheduled dts package backup job sometimes run successfully at
> the scheduled time and most times failed. The job is ran under the SQL Server
> Agent account which has full access permission to the backup network drive.
> The backup dts package always run successfully when ran manually using my
> login account.
> Thank you and kind regards
> Michaelsql

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 failure

All,
I am receiving the following error when I try to backup to tape from SQL using backup under the Management folder in Enterprise manager. Logged in as administrator. Can backup the pubs, Northwind and master databases with no problem.
The database is approx. 8.5GB and the tape can hold 40GB formatted. Using option to overwrite and initialize.
Microsoft SQL-DMO (ODBC SQLState: 42000)
Write on '\\.\Tape0' failed, status = 1117. See the SQL Server error log for more details, backup database is terminating abnormally.
In the error log;
Internal I/O request 0x075CB728: op: write, pBuffer: 0x07d3000, size: 65536, position: 11141120, UMS: internal: 0xC0000185, internalhigh: 0x10000, offset: 0xAA0000, offsethigh: 0x0, m_buf: 0x07d30000, m_len: 65536, m_actualbytes: 65536, m_errcode: 1117, b
ackupfile: \\.\Tape0
Any thought would be appreciated.
Regards,
Dave
can you back it up to disk instead of to tape?
can you backup a large file from disk to tape?
I will do it this way because backup done faster and eliminating the hassle
of dealing with the tape drive.
my 0.02
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:C1CCFEFC-AD93-4715-919F-C468273BD974@.microsoft.com...
> All,
> I am receiving the following error when I try to backup to tape from SQL
using backup under the Management folder in Enterprise manager. Logged in as
administrator. Can backup the pubs, Northwind and master databases with no
problem.
> The database is approx. 8.5GB and the tape can hold 40GB formatted. Using
option to overwrite and initialize.
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> Write on '\\.\Tape0' failed, status = 1117. See the SQL Server error log
for more details, backup database is terminating abnormally.
> In the error log;
> Internal I/O request 0x075CB728: op: write, pBuffer: 0x07d3000, size:
65536, position: 11141120, UMS: internal: 0xC0000185, internalhigh: 0x10000,
offset: 0xAA0000, offsethigh: 0x0, m_buf: 0x07d30000, m_len: 65536,
m_actualbytes: 65536, m_errcode: 1117, backupfile: \\.\Tape0
> Any thought would be appreciated.
> Regards,
> Dave

Backup failure

All,
I am receiving the following error when I try to backup to tape from SQL usi
ng backup under the Management folder in Enterprise manager. Logged in as ad
ministrator. Can backup the pubs, Northwind and master databases with no pro
blem.
The database is approx. 8.5GB and the tape can hold 40GB formatted. Using op
tion to overwrite and initialize.
Microsoft SQL-DMO (ODBC SQLState: 42000)
Write on '\\.\Tape0' failed, status = 1117. See the SQL Server error log for
more details, backup database is terminating abnormally.
In the error log;
Internal I/O request 0x075CB728: op: write, pBuffer: 0x07d3000, size: 65536,
position: 11141120, UMS: internal: 0xC0000185, internalhigh: 0x10000, offse
t: 0xAA0000, offsethigh: 0x0, m_buf: 0x07d30000, m_len: 65536, m_actualbytes
: 65536, m_errcode: 1117, b
ackupfile: \\.\Tape0
Any thought would be appreciated.
Regards,
Davecan you back it up to disk instead of to tape?
can you backup a large file from disk to tape?
I will do it this way because backup done faster and eliminating the hassle
of dealing with the tape drive.
my 0.02
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:C1CCFEFC-AD93-4715-919F-C468273BD974@.microsoft.com...
> All,
> I am receiving the following error when I try to backup to tape from SQL
using backup under the Management folder in Enterprise manager. Logged in as
administrator. Can backup the pubs, Northwind and master databases with no
problem.
> The database is approx. 8.5GB and the tape can hold 40GB formatted. Using
option to overwrite and initialize.
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> Write on '\\.\Tape0' failed, status = 1117. See the SQL Server error log
for more details, backup database is terminating abnormally.
> In the error log;
> Internal I/O request 0x075CB728: op: write, pBuffer: 0x07d3000, size:
65536, position: 11141120, UMS: internal: 0xC0000185, internalhigh: 0x10000,
offset: 0xAA0000, offsethigh: 0x0, m_buf: 0x07d30000, m_len: 65536,
m_actualbytes: 65536, m_errcode: 1117, backupfile: \\.\Tape0
> Any thought would be appreciated.
> Regards,
> Davesql

Backup Failure

I've encountered an error while performing a database backup. I can't seem t
o
find this error anywhere:
BACKUP failed to complete the command master.dbo.sp_sqlexec;1
Does anyone have any insight into this error?
Thank You!How do you execute the backup? For troubleshooting purposes, try executing a
BACKUP command from
Query analyzer.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:6DCC9B4B-4EE8-428A-842B-7E191FFE5AB6@.microsoft.com...
> I've encountered an error while performing a database backup. I can't seem
to
> find this error anywhere:
> BACKUP failed to complete the command master.dbo.sp_sqlexec;1
> Does anyone have any insight into this error?
> Thank You!|||The backup is executed as a scheduled job. This job has been running for
months without any issues - then suddenly...
When run manually, works just fine...
"Tibor Karaszi" wrote:

> How do you execute the backup? For troubleshooting purposes, try executing
a BACKUP command from
> Query analyzer.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
> news:6DCC9B4B-4EE8-428A-842B-7E191FFE5AB6@.microsoft.com...
>|||Is it a TSQL job? How many jobsteps does the job contain? Where did you get
the error message from?
The job output of a job step in the job?
If you are uncertain, define a different output file for each jobstep and se
e exactly which jobstep
this message is from. Then post the TSQL commands you have in that jobstep.
I'm asking because the
error you posted doesn't look like an error that the BACKUP command would pr
oduce.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:D2BB3DBC-BFC5-4581-877B-6BE65F7E0E23@.microsoft.com...
> The backup is executed as a scheduled job. This job has been running for
> months without any issues - then suddenly...
> When run manually, works just fine...
> "Tibor Karaszi" wrote:
>

backup failure

Hi
My DB backup and transaction backup of a database keep on failing with
the following error:
Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC
SQL Server Driver][SQL Server]Write on
'E:\SqlServer\testdb_db_200604221313.BAK' failed, status = 112. See the
SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
terminating abnormally.
Any idea what it really means ?
Thanks
DavidYou can get the description of Win32 error codes with NET HELPMSG from a
command prompt:

NET HELPMSG 112

Yields:

There is not enough space on the disk.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"David Greenberg" <davidgr@.iba.org.il> wrote in message
news:444B9239.2010804@.iba.org.il...
> Hi
> My DB backup and transaction backup of a database keep on failing with the
> following error:
> Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Write on
> 'E:\SqlServer\testdb_db_200604221313.BAK' failed, status = 112. See the
> SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
> terminating abnormally.
> Any idea what it really means ?
> Thanks
> David

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