Record Locks: Recovery mode "SIMPLE" & SP2

I have several databases that perform daily backups. Ever since I installed SP2, the backup jobs are failing. The logs state that it's because the database recovery mode cannot be SIMPLE, but need to be either FULL or Bulk-Logged.

Can anyone tell me if this is true, and that I do have to change my recovery mode?

Here's the error:

NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3042
Report was generated on "ICIS-SQL-SERVER".
Maintenance Plan: DB Backup
Duration: 00:08:05
Status: Warning: One or more tasks failed..
Details:
Back Up Database (Transaction Log) (ICIS-SQL-SERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-03-10T05:08:09.
Task end: 2007-03-10T05:08:09.
Failed:(-1073548784) Executing the query "BACKUP LOG [hl7 db_SamirTesting] TO DISK = N'D:\\mssql\\backup\\hl7 db_SamirTesting\\hl7 db_SamirTesting_backup_200703100508.trn' WITH NOFORMAT, NOINIT, NAME = N'hl7 db_SamirTesting_backup_20070310050809', 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.

Command:BACKUP LOG [hl7 db_SamirTesting] TO DISK = N''D:\mssql\backup\hl7 db_SamirTesting\hl7 db_SamirTesting_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_SamirTesting_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [dg_efilm_153] TO DISK = N''D:\mssql\backup\dg_efilm_153\dg_efilm_153_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''dg_efilm_153_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [hl7 db] TO DISK = N''D:\mssql\backup\hl7 db\hl7 db_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10

Back Up Database (Transaction Log) (ICIS-SQL-SERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-03-10T05:08:09.
Task end: 2007-03-10T05:08:09.
Failed:(-1073548784) Executing the query "BACKUP LOG [dg_efilm_153] TO DISK = N'D:\\mssql\\backup\\dg_efilm_153\\dg_efilm_153_backup_200703100508.trn' WITH NOFORMAT, NOINIT, NAME = N'dg_efilm_153_backup_20070310050809', 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.

Back Up Database (Transaction Log) (ICIS-SQL-SERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-03-10T05:08:09.
Task end: 2007-03-10T05:08:09.
Failed:(-1073548784) Executing the query "BACKUP LOG [hl7 db] TO DISK = N'D:\\mssql\\backup\\hl7 db\\hl7 db_backup_200703100508.trn' WITH NOFORMAT, NOINIT, NAME = N'hl7 db_backup_20070310050809', 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.

Back Up Database (Full) (ICIS-SQL-SERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All databases
Type: Full
Append existing
Task start: 2007-03-10T05:00:06.
Task end: 2007-03-10T05:08:08.
Success
Command:EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\master''
GO
EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\model''
GO
EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\msdb''
GO
EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\hl7 db_SamirTesting''
GO
EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\dg_efilm_153''
GO
EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\hl7 db''
GO
BACKUP DATABASE [master] TO DISK = N''D:\mssql\backup\master\master_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''master_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [model] TO DISK = N''D:\mssql\backup\model\model_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''model_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [msdb] TO DISK = N''D:\mssql\backup\msdb\msdb_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''msdb_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [hl7 db_SamirTesting] TO DISK = N''D:\mssql\backup\hl7 db_SamirTesting\hl7 db_SamirTesting_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_SamirTesting_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [dg_efilm_153] TO DISK = N''D:\mssql\backup\dg_efilm_153\dg_efilm_153_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''dg_efilm_153_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [hl7 db] TO DISK = N''D:\mssql\backup\hl7 db\hl7 db_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10

Please help,

-tim

Check what kind of recovery model is adopted on the databases that are involved in this backup schedule.|||

I have this issue aswell but with the ReportServer database and the ReportServerTempDB database. ReportServer recovery mode is set to full

ReportServerTempDB recovery mode is set to simple.

We have a database maintenance plan set to back up all user databases (at the top of the window it says that simple will be ignored'. The maintenance plan runs but fails as it tries to back up the ReportServerTempDB database.

The messages in the history of the plan say…

9/5/2007 09:15 Database 'ReportServerTempDB' will not be backed up because it does not have its recovery model set to Full or Bulk Logged.

9/5/2007 09:15 Executing the query "BACKUP LOG [ReportServerTempDB] TO DISK = N'G:\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\ReportServerTempDB\\ReportServerTempDB_backup_200705091638.trn' WITH NOFORMAT, NOINIT, NAME = N'ReportServerTempDB_backup_20070509163838', 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.

It appears that it reports it as an error when it checks the recovery mode, but then tries to back it up anyway. I do not have any problems with any other databases except the reporting services ones. At the moment I have both set to full recovery mode, just to get the maintenance plan to work. Any ideas anyone? This worked fine on Sp1 with the database set to Simple recover mode..

Thank you!

|||You cannot backup the system database and report server based databases in the transaction log schedule, as the error is obvious due to the configuration for system databases recovery model and you will not be able change that.

Wednesday, March 28, 2012

Recovery mode "SIMPLE" & SP2

No comments:

Post a Comment

Record Locks

Blog Archive

About Me