error messages I don't understand -1073548784

  • =====================================

    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.

  • 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.

  • 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