April 29, 2010 at 8:17 am
=====================================
Background:
SQL Server 2005 on Win 2008 R2. When we rebuilt our DB server after a hard drive crash last week, the scheduled backups, set up in SQL Server under Maintenance Plans, just stopped working. The errors didn't make sense.
So we recreated the entire backup setup. Now we do this:
Full Backup
Runs every Sunday at 8:00pm, for all databases
Deletes all .bak files in all directories older than 8 days
Does full backup of all db’s to a .bak file on \\oxygen\SQLBackups\Carbon\<DBName>
Differential Backup
Runs every Wednesday at 12:00pm, for all databases
Deletes all .dif files in all directories older than 8 days
Does a differential backup of all db’s to a .dif file on \\oxygen\SQLBackups\Carbon\<DBName>
Transaction Log Backup
Runs every 4 hours, every day, for all databases
Deletes all .trn files in all directories older than 8 days
Does a transaction log backup of all db’s to a .trn file on \\oxygen\SQLBackups\Carbon\<DBName>
So here are a few of the errors. It appears that only the database called webdata, and the master database, are having problems.
Executing the query "BACKUP LOG [master] TO DISK = N'\\\\oxygen\\SQLBackups\\carbon\\master\\master_backup_201004290800.trn' WITH NOFORMAT, NOINIT, NAME = N'master_backup_20100429080009', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "Cannot back up the log of the master database. Use BACKUP DATABASE instead.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
another one:
Executing the query "BACKUP LOG [webdata] TO DISK = N'\\\\Oxygen\\SQLBackups\\Carbon\\webdata\\webdata_backup_201004290800.trn' WITH NOFORMAT, NOINIT, NAME = N'webdata_backup_20100429080010', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
another one:
Executing the query "BACKUP LOG [master] TO DISK = N'\\\\Oxygen\\SQLBackups\\Carbon\\master\\master_backup_201004290800.trn' WITH NOFORMAT, NOINIT, NAME = N'master_backup_20100429080009', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "Cannot back up the log of the master database. Use BACKUP DATABASE instead.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
and
Executing the query "BACKUP LOG [webdata] TO DISK = N'\\\\Oxygen\\SQLBackups\\Carbon\\webdata\\webdata_backup_201004290800.trn' WITH NOFORMAT, NOINIT, NAME = N'webdata_backup_20100429080010', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
and
Executing the query "declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'webdata' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'webdata' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''webdata'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'\\\\Oxygen\\SQLBackups\\Carbon\\webdata\\webdata_backup_201004290800.trn' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
" failed with the following error: "Cannot open backup device '\\\\Oxygen\\SQLBackups\\Carbon\\webdata\\webdata_backup_201004290800.trn'. Operating system error 2(The system cannot find the file specified.).
VERIFY DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
and
Database 'webdata' will not be backed up because it does not have its recovery model set to Full or BulkLogged.
If I'm reading this correctly, at least some of them say the disk is full, which is not the case. Not even close. But I'm not sure I understand the wording of the other messages.
April 29, 2010 at 8:27 am
Executing the query "BACKUP LOG [master] TO DISK = N'\\\\oxygen\\SQLBackups\\carbon\\master\\master_backup_201004290800.trn' WITH NOFORMAT, NOINIT, NAME = N'master_backup_20100429080009', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "Cannot back up the log of the master database. Use BACKUP DATABASE instead.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
You can't run t-log backups on the master database, only FULL backups are allowed.
Executing the query "BACKUP LOG [webdata] TO DISK = N'\\\\Oxygen\\SQLBackups\\Carbon\\webdata\\webdata_backup_201004290800.trn' WITH NOFORMAT, NOINIT, NAME = N'webdata_backup_20100429080010', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
You can't run t-log backups on databases that use the SIMPLE recovery model.
Executing the query "BACKUP LOG [master] TO DISK = N'\\\\Oxygen\\SQLBackups\\Carbon\\master\\master_backup_201004290800.trn' WITH NOFORMAT, NOINIT, NAME = N'master_backup_20100429080009', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "Cannot back up the log of the master database. Use BACKUP DATABASE instead.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
and
Executing the query "BACKUP LOG [webdata] TO DISK = N'\\\\Oxygen\\SQLBackups\\Carbon\\webdata\\webdata_backup_201004290800.trn' WITH NOFORMAT, NOINIT, NAME = N'webdata_backup_20100429080010', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Repeats of the errors previously listed.
Executing the query "declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'webdata' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'webdata' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''webdata'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'\\\\Oxygen\\SQLBackups\\Carbon\\webdata\\webdata_backup_201004290800.trn' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
" failed with the following error: "Cannot open backup device '\\\\Oxygen\\SQLBackups\\Carbon\\webdata\\webdata_backup_201004290800.trn'. Operating system error 2(The system cannot find the file specified.).
VERIFY DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The verify of the backup could not be accomplished as the file could not be found since the backup itself failed and the file was not created.
Database 'webdata' will not be backed up because it does not have its recovery model set to Full or BulkLogged.
Same as above error message regarding the backup (not the verify) as a t-log backup was attempted on a database using the SIMPLE recovery model. T-log backups are only valid for databases using the FULL or BULK_LOGGED recovery models.
April 29, 2010 at 9:22 am
You can't run t-log backups on the master database, only FULL backups are allowed.
You can't run t-log backups on databases that use the SIMPLE recovery model.
Wow. So glad I asked, but so bummed I didn't ask several hours ago.
Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply