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.

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

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:08.
Task end: 2007-03-10T05:08:09.
Failed:(0) Database 'hl7 db_SamirTesting' will not be backed up because it does not have its recovery model set to Full or BulkLogged.

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:08.
Task end: 2007-03-10T05:08:09.
Failed:(0) Database 'dg_efilm_153' will not be backed up because it does not have its recovery model set to Full or BulkLogged.

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:08.
Task end: 2007-03-10T05:08:09.
Failed:(0) Database 'hl7 db' will not be backed up because it does not have its recovery model set to Full or BulkLogged.

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.

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.

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

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:08.
Task end: 2007-03-10T05:08:09.
Failed:(0) Database 'hl7 db_SamirTesting' will not be backed up because it does not have its recovery model set to Full or BulkLogged.

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:08.
Task end: 2007-03-10T05:08:09.
Failed:(0) Database 'dg_efilm_153' will not be backed up because it does not have its recovery model set to Full or BulkLogged.

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:08.
Task end: 2007-03-10T05:08:09.
Failed:(0) Database 'hl7 db' will not be backed up because it does not have its recovery model set to Full or BulkLogged.

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.

Recover SQL Server Data/Agent Tasks from old Data folder

Hi,
I have got a sql server installation folder.. (data and log files
folder). The server crashed so installed a new OS and sql server
instance. I have recovered the old drive with the data and other
folders.
I tried copying all the files to the new data folder while the server
is not running and tried to start the server hoping that all my
databases and sql agent task will be there. But needless to say the sql
server wouldn't start like that. So i replaced the files back with the
newly installed files.
Now I have got these data folders with all my databases. I have copied
the mdf and ldf files and attached it to the newly installed server and
I have got back my databases. Is there a way to recover all the sql
agent tasks I used to have? How can I restore my msdb or master
databases..
Any tips would be appreciated..
Thanks
Sazzad
> I have got back my databases. Is there a way to recover all the sql
> agent tasks I used to have? How can I restore my msdb or master
> databases..
Did you back up them?
"Sumon" <sumon.net@.gmail.com> wrote in message
news:1160981526.080016.173980@.h48g2000cwc.googlegr oups.com...
> Hi,
> I have got a sql server installation folder.. (data and log files
> folder). The server crashed so installed a new OS and sql server
> instance. I have recovered the old drive with the data and other
> folders.
> I tried copying all the files to the new data folder while the server
> is not running and tried to start the server hoping that all my
> databases and sql agent task will be there. But needless to say the sql
> server wouldn't start like that. So i replaced the files back with the
> newly installed files.
> Now I have got these data folders with all my databases. I have copied
> the mdf and ldf files and attached it to the newly installed server and
> I have got back my databases. Is there a way to recover all the sql
> agent tasks I used to have? How can I restore my msdb or master
> databases..
> Any tips would be appreciated..
> Thanks
> Sazzad
>
|||Uri Dimant wrote:
[vbcol=seagreen]
> Did you back up them?
>
> "Sumon" <sumon.net@.gmail.com> wrote in message
> news:1160981526.080016.173980@.h48g2000cwc.googlegr oups.com...
If you have backup of MSDB you can restore its backup or if you have
mdf/ldf files you can attach files.
Which version of SQL Server you are using? 2000 or 2005?
Regards
Amish shah

Recover SQL Server Data/Agent Tasks from old Data folder

Hi,
I have got a sql server installation folder.. (data and log files
folder). The server crashed so installed a new OS and sql server
instance. I have recovered the old drive with the data and other
folders.
I tried copying all the files to the new data folder while the server
is not running and tried to start the server hoping that all my
databases and sql agent task will be there. But needless to say the sql
server wouldn't start like that. So i replaced the files back with the
newly installed files.
Now I have got these data folders with all my databases. I have copied
the mdf and ldf files and attached it to the newly installed server and
I have got back my databases. Is there a way to recover all the sql
agent tasks I used to have? How can I restore my msdb or master
databases..
Any tips would be appreciated..
Thanks
Sazzad> I have got back my databases. Is there a way to recover all the sql
> agent tasks I used to have? How can I restore my msdb or master
> databases..
Did you back up them?
"Sumon" <sumon.net@.gmail.com> wrote in message
news:1160981526.080016.173980@.h48g2000cwc.googlegroups.com...
> Hi,
> I have got a sql server installation folder.. (data and log files
> folder). The server crashed so installed a new OS and sql server
> instance. I have recovered the old drive with the data and other
> folders.
> I tried copying all the files to the new data folder while the server
> is not running and tried to start the server hoping that all my
> databases and sql agent task will be there. But needless to say the sql
> server wouldn't start like that. So i replaced the files back with the
> newly installed files.
> Now I have got these data folders with all my databases. I have copied
> the mdf and ldf files and attached it to the newly installed server and
> I have got back my databases. Is there a way to recover all the sql
> agent tasks I used to have? How can I restore my msdb or master
> databases..
> Any tips would be appreciated..
> Thanks
> Sazzad
>|||Uri Dimant wrote:
> > I have got back my databases. Is there a way to recover all the sql
> > agent tasks I used to have? How can I restore my msdb or master
> > databases..
> Did you back up them?
>
> "Sumon" <sumon.net@.gmail.com> wrote in message
> news:1160981526.080016.173980@.h48g2000cwc.googlegroups.com...
> > Hi,
> >
> > I have got a sql server installation folder.. (data and log files
> > folder). The server crashed so installed a new OS and sql server
> > instance. I have recovered the old drive with the data and other
> > folders.
> >
> > I tried copying all the files to the new data folder while the server
> > is not running and tried to start the server hoping that all my
> > databases and sql agent task will be there. But needless to say the sql
> > server wouldn't start like that. So i replaced the files back with the
> > newly installed files.
> >
> > Now I have got these data folders with all my databases. I have copied
> > the mdf and ldf files and attached it to the newly installed server and
> > I have got back my databases. Is there a way to recover all the sql
> > agent tasks I used to have? How can I restore my msdb or master
> > databases..
> >
> > Any tips would be appreciated..
> >
> > Thanks
> >
> > Sazzad
> >
If you have backup of MSDB you can restore its backup or if you have
mdf/ldf files you can attach files.
Which version of SQL Server you are using? 2000 or 2005?
Regards
Amish shah

Recover SQL Server Data/Agent Tasks from old Data folder

Hi,
I have got a sql server installation folder.. (data and log files
folder). The server crashed so installed a new OS and sql server
instance. I have recovered the old drive with the data and other
folders.
I tried copying all the files to the new data folder while the server
is not running and tried to start the server hoping that all my
databases and sql agent task will be there. But needless to say the sql
server wouldn't start like that. So i replaced the files back with the
newly installed files.
Now I have got these data folders with all my databases. I have copied
the mdf and ldf files and attached it to the newly installed server and
I have got back my databases. Is there a way to recover all the sql
agent tasks I used to have? How can I restore my msdb or master
databases..
Any tips would be appreciated..
Thanks
Sazzad> I have got back my databases. Is there a way to recover all the sql
> agent tasks I used to have? How can I restore my msdb or master
> databases..
Did you back up them?
"Sumon" <sumon.net@.gmail.com> wrote in message
news:1160981526.080016.173980@.h48g2000cwc.googlegroups.com...
> Hi,
> I have got a sql server installation folder.. (data and log files
> folder). The server crashed so installed a new OS and sql server
> instance. I have recovered the old drive with the data and other
> folders.
> I tried copying all the files to the new data folder while the server
> is not running and tried to start the server hoping that all my
> databases and sql agent task will be there. But needless to say the sql
> server wouldn't start like that. So i replaced the files back with the
> newly installed files.
> Now I have got these data folders with all my databases. I have copied
> the mdf and ldf files and attached it to the newly installed server and
> I have got back my databases. Is there a way to recover all the sql
> agent tasks I used to have? How can I restore my msdb or master
> databases..
> Any tips would be appreciated..
> Thanks
> Sazzad
>|||Uri Dimant wrote:
[vbcol=seagreen]
> Did you back up them?
>
> "Sumon" <sumon.net@.gmail.com> wrote in message
> news:1160981526.080016.173980@.h48g2000cwc.googlegroups.com...
If you have backup of MSDB you can restore its backup or if you have
mdf/ldf files you can attach files.
Which version of SQL Server you are using? 2000 or 2005?
Regards
Amish shah

recover northstar

I'm new to SQL server.
We have a windows 2003 server, and I installed SQL server
2000 on C and database file on D. I tried to follow a
book to use SQL server to backup Northwind database on
another disk E:
First I backuped the northwind database on E, then I
tried to restore it to E: and gave it another name in the
restore as field: E:\test.ldf E:\test.mdf
It restored OK, then I saw two files in E: test.ldf. and
test.mdf
Then I thought I can delete these two testing files.
It didn't allow me to do so and gave message you can't
delete them because another program is using it. Then I
close SQL server, this time I can delete them.
But when I start SQL server again, I found the NOrthwind
database is missing, no items and suspect shown there.
The worst thing is I deleted the backup file too before I
found this.
What should I do now to recover the northstar database,
do I need to reinstall it? and I can't figure out why I
deleted NOrthwind database in C while I'm not intend to
do so?
Thanks for any suggestionsAnn,
Its a bad practice to delete the mdf and ldf files directly from the OS
level irrespective whether SQL Server is running or not. did you check the
recycle bin if the files are there?If you want to delete a database, use the
T-SQL command DROP DATABASE or enterprise manager after making sure that no
connection is accessing it.If you have only deleted the .mdf and .ldf files
for the test database then it shouldnt affect Northwind database.May be ,
accidentally, you deleted the other files too.Is the Northwind database
being shown as 'suspect'? If so, it may be because the files are
missing.Doublecheck that by referring the errorlogs.Try deleting the
database. The script to recreate Northwind is also installed with sql
setup.Search for the file named 'instnwnd.sql ' in the SQL Server machine
and execute it .If done properly, this would recreate Northwind complete
with data and all stuff.You can either use Query analyzer or osql utility as
explained in:
Northwind Sample Database
http://msdn.microsoft.com/library/?url=/library/en-us/tsqlref/ts_north_2ch1.asp?frame=true
In case you dont find the file in the server, you can find a copy in the SQL
setup CD.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Ann" <xiaowei.cao@.state.mn.us> wrote in message
news:0b4501c34cab$75c46670$a001280a@.phx.gbl...
> I'm new to SQL server.
> We have a windows 2003 server, and I installed SQL server
> 2000 on C and database file on D. I tried to follow a
> book to use SQL server to backup Northwind database on
> another disk E:
> First I backuped the northwind database on E, then I
> tried to restore it to E: and gave it another name in the
> restore as field: E:\test.ldf E:\test.mdf
> It restored OK, then I saw two files in E: test.ldf. and
> test.mdf
> Then I thought I can delete these two testing files.
> It didn't allow me to do so and gave message you can't
> delete them because another program is using it. Then I
> close SQL server, this time I can delete them.
> But when I start SQL server again, I found the NOrthwind
> database is missing, no items and suspect shown there.
> The worst thing is I deleted the backup file too before I
> found this.
> What should I do now to recover the northstar database,
> do I need to reinstall it? and I can't figure out why I
> deleted NOrthwind database in C while I'm not intend to
> do so?
> Thanks for any suggestions
>
>|||Ann,
Don't know the exact steps, but I think what you may have done is
restored the Northwind database from the backup file and told it to move
the physical database files (.mdf (data) and .ldf (log)) to the E drive.
This did not create a new database with the new file names, it restored
the existing database into the new filenames, and deleted the original
database files.
When you tried to delete the files and were told the files were in use,
SQL Server was using them.
When you deleted these files from disk after stopping SQL server, it
caused SQL server to mark the database suspect (unavailable/corrupted).
SQL server databases are not file based databases, you should ignore any
files created by SQL server except the backup files generated by the
backup process.
To create a copy of a database, backup the database, create a new
database with a different database name, restore the backup file to this
new database you just created. Make sure you check the force restore
flag in the options tab of the restore dialog.
Abel Fontes
Ann wrote:
> I'm new to SQL server.
> We have a windows 2003 server, and I installed SQL server
> 2000 on C and database file on D. I tried to follow a
> book to use SQL server to backup Northwind database on
> another disk E:
> First I backuped the northwind database on E, then I
> tried to restore it to E: and gave it another name in the
> restore as field: E:\test.ldf E:\test.mdf
> It restored OK, then I saw two files in E: test.ldf. and
> test.mdf
> Then I thought I can delete these two testing files.
> It didn't allow me to do so and gave message you can't
> delete them because another program is using it. Then I
> close SQL server, this time I can delete them.
> But when I start SQL server again, I found the NOrthwind
> database is missing, no items and suspect shown there.
> The worst thing is I deleted the backup file too before I
> found this.
> What should I do now to recover the northstar database,
> do I need to reinstall it? and I can't figure out why I
> deleted NOrthwind database in C while I'm not intend to
> do so?
> Thanks for any suggestions
>
>

Recover Databases after OS Crash

WE had a OS Crash and now we have to recover the databases.
We dont have any backup after we installed SQL Server. But
we have now all the data files *.mdf & *.ldf for all
databases including system databases.
Is there a way to recover the user and system databases.
Appreciate any help.
Thanks,
VCHi,
Easy way to recover the database after OS crash is,
1. After OS installation, Copy all .MDF and .LDF files to a new folder
(safe location)
2. Install SQL server and same Service packs (as old) in the identical
folder (Same as old installation)
3. Stop the SQL server
4. Copy the .MDF and .LDF files (took in step 1) to the same folders (Same
as old installation).
5. Start SQL server
Now login to query analyzer or enterprise manager and confirm all the
databases are online.
Thanks
Hari
MCDBA
"VC" <anonymous@.discussions.microsoft.com> wrote in message
news:076001c46db4$a7183960$a501280a@.phx.gbl...
> WE had a OS Crash and now we have to recover the databases.
> We dont have any backup after we installed SQL Server. But
> we have now all the data files *.mdf & *.ldf for all
> databases including system databases.
> Is there a way to recover the user and system databases.
> Appreciate any help.
> Thanks,
> VC|||You can try sp_attach_db using the .mdf and ldf files. That is not
guaranteed since you didn't detach them beforehand. The files could be
corrupted by the server crash. If the log file(s) corrupted, you can also
try sp_attach_single_file_db. The fact that you don't have backups implies
that these databases are not important. Maybe you can find a way to recreate
them?
"VC" <anonymous@.discussions.microsoft.com> wrote in message
news:076001c46db4$a7183960$a501280a@.phx.gbl...
> WE had a OS Crash and now we have to recover the databases.
> We dont have any backup after we installed SQL Server. But
> we have now all the data files *.mdf & *.ldf for all
> databases including system databases.
> Is there a way to recover the user and system databases.
> Appreciate any help.
> Thanks,
> VC

Recover Databases after OS Crash

WE had a OS Crash and now we have to recover the databases.
We dont have any backup after we installed SQL Server. But
we have now all the data files *.mdf & *.ldf for all
databases including system databases.
Is there a way to recover the user and system databases.
Appreciate any help.
Thanks,
VC
Hi,
Easy way to recover the database after OS crash is,
1. After OS installation, Copy all .MDF and .LDF files to a new folder
(safe location)
2. Install SQL server and same Service packs (as old) in the identical
folder (Same as old installation)
3. Stop the SQL server
4. Copy the .MDF and .LDF files (took in step 1) to the same folders (Same
as old installation).
5. Start SQL server
Now login to query analyzer or enterprise manager and confirm all the
databases are online.
Thanks
Hari
MCDBA
"VC" <anonymous@.discussions.microsoft.com> wrote in message
news:076001c46db4$a7183960$a501280a@.phx.gbl...
> WE had a OS Crash and now we have to recover the databases.
> We dont have any backup after we installed SQL Server. But
> we have now all the data files *.mdf & *.ldf for all
> databases including system databases.
> Is there a way to recover the user and system databases.
> Appreciate any help.
> Thanks,
> VC
|||You can try sp_attach_db using the .mdf and ldf files. That is not
guaranteed since you didn't detach them beforehand. The files could be
corrupted by the server crash. If the log file(s) corrupted, you can also
try sp_attach_single_file_db. The fact that you don't have backups implies
that these databases are not important. Maybe you can find a way to recreate
them?
"VC" <anonymous@.discussions.microsoft.com> wrote in message
news:076001c46db4$a7183960$a501280a@.phx.gbl...
> WE had a OS Crash and now we have to recover the databases.
> We dont have any backup after we installed SQL Server. But
> we have now all the data files *.mdf & *.ldf for all
> databases including system databases.
> Is there a way to recover the user and system databases.
> Appreciate any help.
> Thanks,
> VC

Recover Databases after OS Crash

WE had a OS Crash and now we have to recover the databases.
We dont have any backup after we installed SQL Server. But
we have now all the data files *.mdf & *.ldf for all
databases including system databases.
Is there a way to recover the user and system databases.
Appreciate any help.
Thanks,
VCHi,
Easy way to recover the database after OS crash is,
1. After OS installation, Copy all .MDF and .LDF files to a new folder
(safe location)
2. Install SQL server and same Service packs (as old) in the identical
folder (Same as old installation)
3. Stop the SQL server
4. Copy the .MDF and .LDF files (took in step 1) to the same folders (Same
as old installation).
5. Start SQL server
Now login to query analyzer or enterprise manager and confirm all the
databases are online.
Thanks
Hari
MCDBA
"VC" <anonymous@.discussions.microsoft.com> wrote in message
news:076001c46db4$a7183960$a501280a@.phx.gbl...
> WE had a OS Crash and now we have to recover the databases.
> We dont have any backup after we installed SQL Server. But
> we have now all the data files *.mdf & *.ldf for all
> databases including system databases.
> Is there a way to recover the user and system databases.
> Appreciate any help.
> Thanks,
> VC|||You can try sp_attach_db using the .mdf and ldf files. That is not
guaranteed since you didn't detach them beforehand. The files could be
corrupted by the server crash. If the log file(s) corrupted, you can also
try sp_attach_single_file_db. The fact that you don't have backups implies
that these databases are not important. Maybe you can find a way to recreate
them?
"VC" <anonymous@.discussions.microsoft.com> wrote in message
news:076001c46db4$a7183960$a501280a@.phx.gbl...
> WE had a OS Crash and now we have to recover the databases.
> We dont have any backup after we installed SQL Server. But
> we have now all the data files *.mdf & *.ldf for all
> databases including system databases.
> Is there a way to recover the user and system databases.
> Appreciate any help.
> Thanks,
> VC