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.

Recovery from Delete statement using the transaction logs

I ran some delete statements in Query Analyzer and I
found out it was the wrong database. Can you rollback the
delete statements and recover the data from the
transaction logs?
I assume you didn't protect the delete with BEGIN TRAN? If you did, just do ROLLBACK.
Are you performing regular transaction log backups? Is database in full or bulk logged recovery
mode? If answers to these questions are "no", you will probably need to go to your most recent
backup or just accept the data loss. If answers are "yes", you *might* be in better shape.
Anyhow, I suggest you read my article on the topic at:
http://www.karaszi.com/SQLServer/inf...eral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert" <robert@.expressprogramming.com> wrote in message
news:179b01c51500$f58a6c00$a601280a@.phx.gbl...
>I ran some delete statements in Query Analyzer and I
> found out it was the wrong database. Can you rollback the
> delete statements and recover the data from the
> transaction logs?

Recovery from Delete statement using the transaction logs

I ran some delete statements in Query Analyzer and I
found out it was the wrong database. Can you rollback the
delete statements and recover the data from the
transaction logs'I assume you didn't protect the delete with BEGIN TRAN? If you did, just do
ROLLBACK.
Are you performing regular transaction log backups? Is database in full or b
ulk logged recovery
mode? If answers to these questions are "no", you will probably need to go t
o your most recent
backup or just accept the data loss. If answers are "yes", you *might* be in
better shape.
Anyhow, I suggest you read my article on the topic at:
http://www.karaszi.com/SQLServer/in...veral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert" <robert@.expressprogramming.com> wrote in message
news:179b01c51500$f58a6c00$a601280a@.phx.gbl...
>I ran some delete statements in Query Analyzer and I
> found out it was the wrong database. Can you rollback the
> delete statements and recover the data from the
> transaction logs'

Recovery from Delete statement using the transaction logs

I ran some delete statements in Query Analyzer and I
found out it was the wrong database. Can you rollback the
delete statements and recover the data from the
transaction logs'I assume you didn't protect the delete with BEGIN TRAN? If you did, just do ROLLBACK.
Are you performing regular transaction log backups? Is database in full or bulk logged recovery
mode? If answers to these questions are "no", you will probably need to go to your most recent
backup or just accept the data loss. If answers are "yes", you *might* be in better shape.
Anyhow, I suggest you read my article on the topic at:
http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert" <robert@.expressprogramming.com> wrote in message
news:179b01c51500$f58a6c00$a601280a@.phx.gbl...
>I ran some delete statements in Query Analyzer and I
> found out it was the wrong database. Can you rollback the
> delete statements and recover the data from the
> transaction logs'

recovering multi-log db without logs

We have a problem.
A database was set to use 2 log files, and one of them somehow grew to a bloated 32GB, with a 400MB database! The recovery model was set to full, which will not be used again.
Now, whenever we start SQL Server a system process places an exclusive lock on the database is question and never lets go of it. It doesn't seem to be doing anything but it never lets go, so nothing can be done with the database.

We suspect the massive log file has something to do with the problem.
We want to somehow remove the log files, but can't do a damn thing because of the lock.

We have tried renaming the log files, then detaching the database and reattaching, but sp_attach_db and sp_attach_single_file_db won't recreate log files on databases that use more than one.

We have tried creating a "dummy", empty database with the same name and log files, then moving in the original MDF, but it error out saying we can't use log files from another database.

Does anyone have any suggestions?
No, we don't have backups of any of the log files, only the mdf. This is going to be corrected, but we need to get this thing going!sp_attach_single_file_db should cope with it if you have managed the dettach succesfully.
It should recreate a single log file for you.

Having a backup of the mdf sounds like you are just backing up the file. This might work if you close down sql server or dettach the database first but better to just backup the database.

What is the status of the database when it has the exclusive lock on it@. recovering?|||Thanks for the reply. the attach_single_file_db doesn't work either with a multi-logfile database, if you don't have the log files available. We did correct the problem with a call to Microsoft.

Thanks for the suggestion.|||What did MicroSoft say?|||Originally posted by nigelrivett
What did MicroSoft say?

I guess that under the circumstances they suggested using
sp_attach_single_file_db. But use if anyone is planning on using this command have a look at BOL first.

BTW using full recovery mode is ok as long as you create a job that backs up the transaction log on a regular basis. One can easily set this up in enterprise manager -> sql server agent -> right click on datbase maintence plans -> new etc etc you can even tell it to write over trans backups older than a set period of time say 24 hours.

Making a really good maintaince plain withing sql server is not brain surgery it just takes a little effort.

Recovering Database

I am doing a point in time recover of a database. I point to the backup and
trans logs and begin the recovery. It says (Loading database) and it
starts.. It goes through the backup then applies all the translogs. When
it gets to the end it says, DATABASE RESTORE HAS COMPLETED SUCCESSFULLY. But
when I go to the database, it is still at a status of LOADING. How do I
bring the database online? Thanks
Jason Greene
Hi Jason
You may not have requested that SQL Server run the recovery process after
the last log was loaded in.
From the Query Analyzer, try:
RESTORE DATABASE <name of db> WITH RECOVERY
(Note that you don't need to specify any backup files, as these have already
been loaded. The WITH RECOVERY is the important part.)
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
news:73331569-98FB-4010-A7F9-F4EBA2B9DFF4@.microsoft.com...
>I am doing a point in time recover of a database. I point to the backup
>and
> trans logs and begin the recovery. It says (Loading database) and it
> starts.. It goes through the backup then applies all the translogs.
> When
> it gets to the end it says, DATABASE RESTORE HAS COMPLETED SUCCESSFULLY.
> But
> when I go to the database, it is still at a status of LOADING. How do I
> bring the database online? Thanks
> Jason Greene
>
|||I will try that now and let you know.. Thanks
"Kalen Delaney" wrote:

> Hi Jason
> You may not have requested that SQL Server run the recovery process after
> the last log was loaded in.
> From the Query Analyzer, try:
> RESTORE DATABASE <name of db> WITH RECOVERY
> (Note that you don't need to specify any backup files, as these have already
> been loaded. The WITH RECOVERY is the important part.)
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
> news:73331569-98FB-4010-A7F9-F4EBA2B9DFF4@.microsoft.com...
>
>
|||Thanks for the fast response.. Also, I have another posting with a
question.. I was wondering if you had any insite on it..
I had a hardware crash where my Datafile got corrupted but my Logfile was on
another array of disks so it was fine. Every two hours, I do a backup of my
Transaction Log. When my hardware crashed, it had be awhile since the last
Transaction log backup. Can I do a restore to a point it time. I can not
backup my current logfile because it has to be attached to a datafile an
online in order to be backed up. I tried to restore an old COLD backup of my
datafile and attach the logfile to it to try to back it up and that did not
work.. Any help would be appreciated. I need to recover to the point of
hardware failure..
"Kalen Delaney" wrote:

> Hi Jason
> You may not have requested that SQL Server run the recovery process after
> the last log was loaded in.
> From the Query Analyzer, try:
> RESTORE DATABASE <name of db> WITH RECOVERY
> (Note that you don't need to specify any backup files, as these have already
> been loaded. The WITH RECOVERY is the important part.)
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
> news:73331569-98FB-4010-A7F9-F4EBA2B9DFF4@.microsoft.com...
>
>
|||Answered in your post "Point in Time Recovery" in this group.
--
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/
"Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
news:50564A61-1266-412E-846E-F7376C511708@.microsoft.com...[vbcol=seagreen]
> Thanks for the fast response.. Also, I have another posting with a
> question.. I was wondering if you had any insite on it..
> I had a hardware crash where my Datafile got corrupted but my Logfile was
> on
> another array of disks so it was fine. Every two hours, I do a backup of
> my
> Transaction Log. When my hardware crashed, it had be awhile since the
> last
> Transaction log backup. Can I do a restore to a point it time. I can not
> backup my current logfile because it has to be attached to a datafile an
> online in order to be backed up. I tried to restore an old COLD backup of
> my
> datafile and attach the logfile to it to try to back it up and that did
> not
> work.. Any help would be appreciated. I need to recover to the point of
> hardware failure..
>
>
> "Kalen Delaney" wrote:
|||When I issue that command I get the following error.
Server: Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
"Kalen Delaney" wrote:

> Hi Jason
> You may not have requested that SQL Server run the recovery process after
> the last log was loaded in.
> From the Query Analyzer, try:
> RESTORE DATABASE <name of db> WITH RECOVERY
> (Note that you don't need to specify any backup files, as these have already
> been loaded. The WITH RECOVERY is the important part.)
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
> news:73331569-98FB-4010-A7F9-F4EBA2B9DFF4@.microsoft.com...
>
>
|||Hi
There is another connection using the DB, put it into single user mode
(Right click on it and select properties)
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/
"Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
news:5D6388E6-A38F-4C5F-A715-6433A0A43588@.microsoft.com...[vbcol=seagreen]
> When I issue that command I get the following error.
>
> Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> "Kalen Delaney" wrote:
|||That did it.. Thanks.. Now I got my database back to the last good Translog
that was backed up.. Now I am going to try to see if I can use the Online
Translog that was part of the database when it crashed to see if I can
restore to the point of failure..
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> There is another connection using the DB, put it into single user mode
> (Right click on it and select properties)
> 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/
> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
> news:5D6388E6-A38F-4C5F-A715-6433A0A43588@.microsoft.com...
>
>
|||Hi
No, the transaction log is no good to you as the sequences do not agree
anymore between the log and the DB.
Kiss that data good bye.
The fast that your drive crashed and it took your DB's along with it, how
about getting some RAID?
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/
"Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
news:3B80AE01-78FA-4D68-BD58-96BDDC08D97F@.microsoft.com...[vbcol=seagreen]
> That did it.. Thanks.. Now I got my database back to the last good
> Translog
> that was backed up.. Now I am going to try to see if I can use the Online
> Translog that was part of the database when it crashed to see if I can
> restore to the point of failure..
>
> "Mike Epprecht (SQL MVP)" wrote:
|||It is raid 5 double parity. The entire array died.. All drives at the same
time. I think it was electrical.
On the original note.. I got an old copy of my database .mdf file and
logfile .ldf file. I mounted this database and brought it only. Then I took
the database offline, then swithed the logfile of the old one with the good
current one. Then I restarted the computer.. When it came online, it
brought the database online as Suspect.. Old datafile, current logfile. I
then issued a Backup Logfile command and it backed it up. I then used that
logfile backup to restore the database to the time the database crashed..
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> No, the transaction log is no good to you as the sequences do not agree
> anymore between the log and the DB.
> Kiss that data good bye.
> The fast that your drive crashed and it took your DB's along with it, how
> about getting some RAID?
> 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/
> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
> news:3B80AE01-78FA-4D68-BD58-96BDDC08D97F@.microsoft.com...
>
>

Recovering Database

I am doing a point in time recover of a database. I point to the backup and
trans logs and begin the recovery. It says (Loading database) and it
starts.. It goes through the backup then applies all the translogs. When
it gets to the end it says, DATABASE RESTORE HAS COMPLETED SUCCESSFULLY. Bu
t
when I go to the database, it is still at a status of LOADING. How do I
bring the database online? Thanks
Jason GreeneHi Jason
You may not have requested that SQL Server run the recovery process after
the last log was loaded in.
From the Query Analyzer, try:
RESTORE DATABASE <name of db> WITH RECOVERY
(Note that you don't need to specify any backup files, as these have already
been loaded. The WITH RECOVERY is the important part.)
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
news:73331569-98FB-4010-A7F9-F4EBA2B9DFF4@.microsoft.com...
>I am doing a point in time recover of a database. I point to the backup
>and
> trans logs and begin the recovery. It says (Loading database) and it
> starts.. It goes through the backup then applies all the translogs.
> When
> it gets to the end it says, DATABASE RESTORE HAS COMPLETED SUCCESSFULLY.
> But
> when I go to the database, it is still at a status of LOADING. How do I
> bring the database online? Thanks
> Jason Greene
>|||I will try that now and let you know.. Thanks
"Kalen Delaney" wrote:

> Hi Jason
> You may not have requested that SQL Server run the recovery process after
> the last log was loaded in.
> From the Query Analyzer, try:
> RESTORE DATABASE <name of db> WITH RECOVERY
> (Note that you don't need to specify any backup files, as these have alrea
dy
> been loaded. The WITH RECOVERY is the important part.)
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
> news:73331569-98FB-4010-A7F9-F4EBA2B9DFF4@.microsoft.com...
>
>|||Thanks for the fast response.. Also, I have another posting with a
question.. I was wondering if you had any insite on it..
I had a hardware crash where my Datafile got corrupted but my Logfile was on
another array of disks so it was fine. Every two hours, I do a backup of my
Transaction Log. When my hardware crashed, it had be awhile since the last
Transaction log backup. Can I do a restore to a point it time. I can not
backup my current logfile because it has to be attached to a datafile an
online in order to be backed up. I tried to restore an old COLD backup of m
y
datafile and attach the logfile to it to try to back it up and that did not
work.. Any help would be appreciated. I need to recover to the point of
hardware failure..
"Kalen Delaney" wrote:

> Hi Jason
> You may not have requested that SQL Server run the recovery process after
> the last log was loaded in.
> From the Query Analyzer, try:
> RESTORE DATABASE <name of db> WITH RECOVERY
> (Note that you don't need to specify any backup files, as these have alrea
dy
> been loaded. The WITH RECOVERY is the important part.)
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
> news:73331569-98FB-4010-A7F9-F4EBA2B9DFF4@.microsoft.com...
>
>|||Answered in your post "Point in Time Recovery" in this group.
--
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/
"Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
news:50564A61-1266-412E-846E-F7376C511708@.microsoft.com...[vbcol=seagreen]
> Thanks for the fast response.. Also, I have another posting with a
> question.. I was wondering if you had any insite on it..
> I had a hardware crash where my Datafile got corrupted but my Logfile was
> on
> another array of disks so it was fine. Every two hours, I do a backup of
> my
> Transaction Log. When my hardware crashed, it had be awhile since the
> last
> Transaction log backup. Can I do a restore to a point it time. I can not
> backup my current logfile because it has to be attached to a datafile an
> online in order to be backed up. I tried to restore an old COLD backup of
> my
> datafile and attach the logfile to it to try to back it up and that did
> not
> work.. Any help would be appreciated. I need to recover to the point of
> hardware failure..
>
>
> "Kalen Delaney" wrote:
>|||When I issue that command I get the following error.
Server: Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
"Kalen Delaney" wrote:

> Hi Jason
> You may not have requested that SQL Server run the recovery process after
> the last log was loaded in.
> From the Query Analyzer, try:
> RESTORE DATABASE <name of db> WITH RECOVERY
> (Note that you don't need to specify any backup files, as these have alrea
dy
> been loaded. The WITH RECOVERY is the important part.)
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
> news:73331569-98FB-4010-A7F9-F4EBA2B9DFF4@.microsoft.com...
>
>|||Hi
There is another connection using the DB, put it into single user mode
(Right click on it and select properties)
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/
"Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
news:5D6388E6-A38F-4C5F-A715-6433A0A43588@.microsoft.com...[vbcol=seagreen]
> When I issue that command I get the following error.
>
> Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> "Kalen Delaney" wrote:
>|||That did it.. Thanks.. Now I got my database back to the last good Translog
that was backed up.. Now I am going to try to see if I can use the Online
Translog that was part of the database when it crashed to see if I can
restore to the point of failure..
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> There is another connection using the DB, put it into single user mode
> (Right click on it and select properties)
> 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/
> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
> news:5D6388E6-A38F-4C5F-A715-6433A0A43588@.microsoft.com...
>
>|||Hi
No, the transaction log is no good to you as the sequences do not agree
anymore between the log and the DB.
Kiss that data good bye.
The fast that your drive crashed and it took your DB's along with it, how
about getting some RAID?
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/
"Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
news:3B80AE01-78FA-4D68-BD58-96BDDC08D97F@.microsoft.com...[vbcol=seagreen]
> That did it.. Thanks.. Now I got my database back to the last good
> Translog
> that was backed up.. Now I am going to try to see if I can use the Online
> Translog that was part of the database when it crashed to see if I can
> restore to the point of failure..
>
> "Mike Epprecht (SQL MVP)" wrote:
>|||It is raid 5 double parity. The entire array died.. All drives at the sam
e
time. I think it was electrical.
On the original note.. I got an old copy of my database .mdf file and
logfile .ldf file. I mounted this database and brought it only. Then I too
k
the database offline, then swithed the logfile of the old one with the good
current one. Then I restarted the computer.. When it came online, it
brought the database online as Suspect.. Old datafile, current logfile. I
then issued a Backup Logfile command and it backed it up. I then used that
logfile backup to restore the database to the time the database crashed..
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> No, the transaction log is no good to you as the sequences do not agree
> anymore between the log and the DB.
> Kiss that data good bye.
> The fast that your drive crashed and it took your DB's along with it, how
> about getting some RAID?
> 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/
> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
> news:3B80AE01-78FA-4D68-BD58-96BDDC08D97F@.microsoft.com...
>
>

Recovering Database

I am doing a point in time recover of a database. I point to the backup and
trans logs and begin the recovery. It says (Loading database) and it
starts.. It goes through the backup then applies all the translogs. When
it gets to the end it says, DATABASE RESTORE HAS COMPLETED SUCCESSFULLY. But
when I go to the database, it is still at a status of LOADING. How do I
bring the database online? Thanks
Jason GreeneHi Jason
You may not have requested that SQL Server run the recovery process after
the last log was loaded in.
From the Query Analyzer, try:
RESTORE DATABASE <name of db> WITH RECOVERY
(Note that you don't need to specify any backup files, as these have already
been loaded. The WITH RECOVERY is the important part.)
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
news:73331569-98FB-4010-A7F9-F4EBA2B9DFF4@.microsoft.com...
>I am doing a point in time recover of a database. I point to the backup
>and
> trans logs and begin the recovery. It says (Loading database) and it
> starts.. It goes through the backup then applies all the translogs.
> When
> it gets to the end it says, DATABASE RESTORE HAS COMPLETED SUCCESSFULLY.
> But
> when I go to the database, it is still at a status of LOADING. How do I
> bring the database online? Thanks
> Jason Greene
>|||I will try that now and let you know.. Thanks
"Kalen Delaney" wrote:
> Hi Jason
> You may not have requested that SQL Server run the recovery process after
> the last log was loaded in.
> From the Query Analyzer, try:
> RESTORE DATABASE <name of db> WITH RECOVERY
> (Note that you don't need to specify any backup files, as these have already
> been loaded. The WITH RECOVERY is the important part.)
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
> news:73331569-98FB-4010-A7F9-F4EBA2B9DFF4@.microsoft.com...
> >I am doing a point in time recover of a database. I point to the backup
> >and
> > trans logs and begin the recovery. It says (Loading database) and it
> > starts.. It goes through the backup then applies all the translogs.
> > When
> > it gets to the end it says, DATABASE RESTORE HAS COMPLETED SUCCESSFULLY.
> > But
> > when I go to the database, it is still at a status of LOADING. How do I
> > bring the database online? Thanks
> > Jason Greene
> >
>
>|||Thanks for the fast response.. Also, I have another posting with a
question.. I was wondering if you had any insite on it..
I had a hardware crash where my Datafile got corrupted but my Logfile was on
another array of disks so it was fine. Every two hours, I do a backup of my
Transaction Log. When my hardware crashed, it had be awhile since the last
Transaction log backup. Can I do a restore to a point it time. I can not
backup my current logfile because it has to be attached to a datafile an
online in order to be backed up. I tried to restore an old COLD backup of my
datafile and attach the logfile to it to try to back it up and that did not
work.. Any help would be appreciated. I need to recover to the point of
hardware failure..
"Kalen Delaney" wrote:
> Hi Jason
> You may not have requested that SQL Server run the recovery process after
> the last log was loaded in.
> From the Query Analyzer, try:
> RESTORE DATABASE <name of db> WITH RECOVERY
> (Note that you don't need to specify any backup files, as these have already
> been loaded. The WITH RECOVERY is the important part.)
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
> news:73331569-98FB-4010-A7F9-F4EBA2B9DFF4@.microsoft.com...
> >I am doing a point in time recover of a database. I point to the backup
> >and
> > trans logs and begin the recovery. It says (Loading database) and it
> > starts.. It goes through the backup then applies all the translogs.
> > When
> > it gets to the end it says, DATABASE RESTORE HAS COMPLETED SUCCESSFULLY.
> > But
> > when I go to the database, it is still at a status of LOADING. How do I
> > bring the database online? Thanks
> > Jason Greene
> >
>
>|||Answered in your post "Point in Time Recovery" in this group.
--
--
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/
"Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
news:50564A61-1266-412E-846E-F7376C511708@.microsoft.com...
> Thanks for the fast response.. Also, I have another posting with a
> question.. I was wondering if you had any insite on it..
> I had a hardware crash where my Datafile got corrupted but my Logfile was
> on
> another array of disks so it was fine. Every two hours, I do a backup of
> my
> Transaction Log. When my hardware crashed, it had be awhile since the
> last
> Transaction log backup. Can I do a restore to a point it time. I can not
> backup my current logfile because it has to be attached to a datafile an
> online in order to be backed up. I tried to restore an old COLD backup of
> my
> datafile and attach the logfile to it to try to back it up and that did
> not
> work.. Any help would be appreciated. I need to recover to the point of
> hardware failure..
>
>
> "Kalen Delaney" wrote:
>> Hi Jason
>> You may not have requested that SQL Server run the recovery process after
>> the last log was loaded in.
>> From the Query Analyzer, try:
>> RESTORE DATABASE <name of db> WITH RECOVERY
>> (Note that you don't need to specify any backup files, as these have
>> already
>> been loaded. The WITH RECOVERY is the important part.)
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
>> news:73331569-98FB-4010-A7F9-F4EBA2B9DFF4@.microsoft.com...
>> >I am doing a point in time recover of a database. I point to the backup
>> >and
>> > trans logs and begin the recovery. It says (Loading database) and it
>> > starts.. It goes through the backup then applies all the translogs.
>> > When
>> > it gets to the end it says, DATABASE RESTORE HAS COMPLETED
>> > SUCCESSFULLY.
>> > But
>> > when I go to the database, it is still at a status of LOADING. How do
>> > I
>> > bring the database online? Thanks
>> > Jason Greene
>> >
>>|||When I issue that command I get the following error.
Server: Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
"Kalen Delaney" wrote:
> Hi Jason
> You may not have requested that SQL Server run the recovery process after
> the last log was loaded in.
> From the Query Analyzer, try:
> RESTORE DATABASE <name of db> WITH RECOVERY
> (Note that you don't need to specify any backup files, as these have already
> been loaded. The WITH RECOVERY is the important part.)
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
> news:73331569-98FB-4010-A7F9-F4EBA2B9DFF4@.microsoft.com...
> >I am doing a point in time recover of a database. I point to the backup
> >and
> > trans logs and begin the recovery. It says (Loading database) and it
> > starts.. It goes through the backup then applies all the translogs.
> > When
> > it gets to the end it says, DATABASE RESTORE HAS COMPLETED SUCCESSFULLY.
> > But
> > when I go to the database, it is still at a status of LOADING. How do I
> > bring the database online? Thanks
> > Jason Greene
> >
>
>|||Hi
There is another connection using the DB, put it into single user mode
(Right click on it and select properties)
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/
"Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
news:5D6388E6-A38F-4C5F-A715-6433A0A43588@.microsoft.com...
> When I issue that command I get the following error.
>
> Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> "Kalen Delaney" wrote:
>> Hi Jason
>> You may not have requested that SQL Server run the recovery process after
>> the last log was loaded in.
>> From the Query Analyzer, try:
>> RESTORE DATABASE <name of db> WITH RECOVERY
>> (Note that you don't need to specify any backup files, as these have
>> already
>> been loaded. The WITH RECOVERY is the important part.)
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
>> news:73331569-98FB-4010-A7F9-F4EBA2B9DFF4@.microsoft.com...
>> >I am doing a point in time recover of a database. I point to the backup
>> >and
>> > trans logs and begin the recovery. It says (Loading database) and it
>> > starts.. It goes through the backup then applies all the translogs.
>> > When
>> > it gets to the end it says, DATABASE RESTORE HAS COMPLETED
>> > SUCCESSFULLY.
>> > But
>> > when I go to the database, it is still at a status of LOADING. How do
>> > I
>> > bring the database online? Thanks
>> > Jason Greene
>> >
>>|||That did it.. Thanks.. Now I got my database back to the last good Translog
that was backed up.. Now I am going to try to see if I can use the Online
Translog that was part of the database when it crashed to see if I can
restore to the point of failure..
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> There is another connection using the DB, put it into single user mode
> (Right click on it and select properties)
> 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/
> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
> news:5D6388E6-A38F-4C5F-A715-6433A0A43588@.microsoft.com...
> > When I issue that command I get the following error.
> >
> >
> > Server: Msg 3101, Level 16, State 2, Line 1
> > Exclusive access could not be obtained because the database is in use.
> > Server: Msg 3013, Level 16, State 1, Line 1
> > RESTORE DATABASE is terminating abnormally.
> >
> > "Kalen Delaney" wrote:
> >
> >> Hi Jason
> >>
> >> You may not have requested that SQL Server run the recovery process after
> >> the last log was loaded in.
> >> From the Query Analyzer, try:
> >>
> >> RESTORE DATABASE <name of db> WITH RECOVERY
> >>
> >> (Note that you don't need to specify any backup files, as these have
> >> already
> >> been loaded. The WITH RECOVERY is the important part.)
> >> --
> >> HTH
> >> --
> >> Kalen Delaney
> >> SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
> >> news:73331569-98FB-4010-A7F9-F4EBA2B9DFF4@.microsoft.com...
> >> >I am doing a point in time recover of a database. I point to the backup
> >> >and
> >> > trans logs and begin the recovery. It says (Loading database) and it
> >> > starts.. It goes through the backup then applies all the translogs.
> >> > When
> >> > it gets to the end it says, DATABASE RESTORE HAS COMPLETED
> >> > SUCCESSFULLY.
> >> > But
> >> > when I go to the database, it is still at a status of LOADING. How do
> >> > I
> >> > bring the database online? Thanks
> >> > Jason Greene
> >> >
> >>
> >>
> >>
>
>|||Hi
No, the transaction log is no good to you as the sequences do not agree
anymore between the log and the DB.
Kiss that data good bye.
The fast that your drive crashed and it took your DB's along with it, how
about getting some RAID?
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/
"Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
news:3B80AE01-78FA-4D68-BD58-96BDDC08D97F@.microsoft.com...
> That did it.. Thanks.. Now I got my database back to the last good
> Translog
> that was backed up.. Now I am going to try to see if I can use the Online
> Translog that was part of the database when it crashed to see if I can
> restore to the point of failure..
>
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> There is another connection using the DB, put it into single user mode
>> (Right click on it and select properties)
>> 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/
>> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
>> news:5D6388E6-A38F-4C5F-A715-6433A0A43588@.microsoft.com...
>> > When I issue that command I get the following error.
>> >
>> >
>> > Server: Msg 3101, Level 16, State 2, Line 1
>> > Exclusive access could not be obtained because the database is in use.
>> > Server: Msg 3013, Level 16, State 1, Line 1
>> > RESTORE DATABASE is terminating abnormally.
>> >
>> > "Kalen Delaney" wrote:
>> >
>> >> Hi Jason
>> >>
>> >> You may not have requested that SQL Server run the recovery process
>> >> after
>> >> the last log was loaded in.
>> >> From the Query Analyzer, try:
>> >>
>> >> RESTORE DATABASE <name of db> WITH RECOVERY
>> >>
>> >> (Note that you don't need to specify any backup files, as these have
>> >> already
>> >> been loaded. The WITH RECOVERY is the important part.)
>> >> --
>> >> HTH
>> >> --
>> >> Kalen Delaney
>> >> SQL Server MVP
>> >> www.SolidQualityLearning.com
>> >>
>> >>
>> >> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:73331569-98FB-4010-A7F9-F4EBA2B9DFF4@.microsoft.com...
>> >> >I am doing a point in time recover of a database. I point to the
>> >> >backup
>> >> >and
>> >> > trans logs and begin the recovery. It says (Loading database) and
>> >> > it
>> >> > starts.. It goes through the backup then applies all the
>> >> > translogs.
>> >> > When
>> >> > it gets to the end it says, DATABASE RESTORE HAS COMPLETED
>> >> > SUCCESSFULLY.
>> >> > But
>> >> > when I go to the database, it is still at a status of LOADING. How
>> >> > do
>> >> > I
>> >> > bring the database online? Thanks
>> >> > Jason Greene
>> >> >
>> >>
>> >>
>> >>
>>|||It is raid 5 double parity. The entire array died.. All drives at the same
time. I think it was electrical.
On the original note.. I got an old copy of my database .mdf file and
logfile .ldf file. I mounted this database and brought it only. Then I took
the database offline, then swithed the logfile of the old one with the good
current one. Then I restarted the computer.. When it came online, it
brought the database online as Suspect.. Old datafile, current logfile. I
then issued a Backup Logfile command and it backed it up. I then used that
logfile backup to restore the database to the time the database crashed..
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> No, the transaction log is no good to you as the sequences do not agree
> anymore between the log and the DB.
> Kiss that data good bye.
> The fast that your drive crashed and it took your DB's along with it, how
> about getting some RAID?
> 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/
> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
> news:3B80AE01-78FA-4D68-BD58-96BDDC08D97F@.microsoft.com...
> > That did it.. Thanks.. Now I got my database back to the last good
> > Translog
> > that was backed up.. Now I am going to try to see if I can use the Online
> > Translog that was part of the database when it crashed to see if I can
> > restore to the point of failure..
> >
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> >> Hi
> >>
> >> There is another connection using the DB, put it into single user mode
> >> (Right click on it and select properties)
> >>
> >> 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/
> >>
> >> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
> >> news:5D6388E6-A38F-4C5F-A715-6433A0A43588@.microsoft.com...
> >> > When I issue that command I get the following error.
> >> >
> >> >
> >> > Server: Msg 3101, Level 16, State 2, Line 1
> >> > Exclusive access could not be obtained because the database is in use.
> >> > Server: Msg 3013, Level 16, State 1, Line 1
> >> > RESTORE DATABASE is terminating abnormally.
> >> >
> >> > "Kalen Delaney" wrote:
> >> >
> >> >> Hi Jason
> >> >>
> >> >> You may not have requested that SQL Server run the recovery process
> >> >> after
> >> >> the last log was loaded in.
> >> >> From the Query Analyzer, try:
> >> >>
> >> >> RESTORE DATABASE <name of db> WITH RECOVERY
> >> >>
> >> >> (Note that you don't need to specify any backup files, as these have
> >> >> already
> >> >> been loaded. The WITH RECOVERY is the important part.)
> >> >> --
> >> >> HTH
> >> >> --
> >> >> Kalen Delaney
> >> >> SQL Server MVP
> >> >> www.SolidQualityLearning.com
> >> >>
> >> >>
> >> >> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in
> >> >> message
> >> >> news:73331569-98FB-4010-A7F9-F4EBA2B9DFF4@.microsoft.com...
> >> >> >I am doing a point in time recover of a database. I point to the
> >> >> >backup
> >> >> >and
> >> >> > trans logs and begin the recovery. It says (Loading database) and
> >> >> > it
> >> >> > starts.. It goes through the backup then applies all the
> >> >> > translogs.
> >> >> > When
> >> >> > it gets to the end it says, DATABASE RESTORE HAS COMPLETED
> >> >> > SUCCESSFULLY.
> >> >> > But
> >> >> > when I go to the database, it is still at a status of LOADING. How
> >> >> > do
> >> >> > I
> >> >> > bring the database online? Thanks
> >> >> > Jason Greene
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Hi everybody:
Hi Jason. what command did you execute to backup the transacation log file
when you had "the old datafile with the current logfile"?.
I'm getting the following error when trying to backup the transaction log
file:
--
Database 'NorthwindTest' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for details.
BACKUP LOG is terminating abnormally.
--
The event log shows that the following command its executing:
--
BACKUP LOG [NorthwindTest] TO DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\NorthwindTest.bak' WITH NOINIT , NOUNLOAD , NAME =N'NorthwindTest backup', NOSKIP , STATS = 10, NOFORMAT
--
Any one knows a good procedure to achieve a restore of a database to the
point of failure?
Cheers!
Amando Reséndez
"Jason Greene" wrote:
> It is raid 5 double parity. The entire array died.. All drives at the same
> time. I think it was electrical.
> On the original note.. I got an old copy of my database .mdf file and
> logfile .ldf file. I mounted this database and brought it only. Then I took
> the database offline, then swithed the logfile of the old one with the good
> current one. Then I restarted the computer.. When it came online, it
> brought the database online as Suspect.. Old datafile, current logfile. I
> then issued a Backup Logfile command and it backed it up. I then used that
> logfile backup to restore the database to the time the database crashed..
>
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > No, the transaction log is no good to you as the sequences do not agree
> > anymore between the log and the DB.
> > Kiss that data good bye.
> >
> > The fast that your drive crashed and it took your DB's along with it, how
> > about getting some RAID?
> >
> > 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/
> >
> > "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
> > news:3B80AE01-78FA-4D68-BD58-96BDDC08D97F@.microsoft.com...
> > > That did it.. Thanks.. Now I got my database back to the last good
> > > Translog
> > > that was backed up.. Now I am going to try to see if I can use the Online
> > > Translog that was part of the database when it crashed to see if I can
> > > restore to the point of failure..
> > >
> > >
> > > "Mike Epprecht (SQL MVP)" wrote:
> > >
> > >> Hi
> > >>
> > >> There is another connection using the DB, put it into single user mode
> > >> (Right click on it and select properties)
> > >>
> > >> 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/
> > >>
> > >> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
> > >> news:5D6388E6-A38F-4C5F-A715-6433A0A43588@.microsoft.com...
> > >> > When I issue that command I get the following error.
> > >> >
> > >> >
> > >> > Server: Msg 3101, Level 16, State 2, Line 1
> > >> > Exclusive access could not be obtained because the database is in use.
> > >> > Server: Msg 3013, Level 16, State 1, Line 1
> > >> > RESTORE DATABASE is terminating abnormally.
> > >> >
> > >> > "Kalen Delaney" wrote:
> > >> >
> > >> >> Hi Jason
> > >> >>
> > >> >> You may not have requested that SQL Server run the recovery process
> > >> >> after
> > >> >> the last log was loaded in.
> > >> >> From the Query Analyzer, try:
> > >> >>
> > >> >> RESTORE DATABASE <name of db> WITH RECOVERY
> > >> >>
> > >> >> (Note that you don't need to specify any backup files, as these have
> > >> >> already
> > >> >> been loaded. The WITH RECOVERY is the important part.)
> > >> >> --
> > >> >> HTH
> > >> >> --
> > >> >> Kalen Delaney
> > >> >> SQL Server MVP
> > >> >> www.SolidQualityLearning.com
> > >> >>
> > >> >>
> > >> >> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in
> > >> >> message
> > >> >> news:73331569-98FB-4010-A7F9-F4EBA2B9DFF4@.microsoft.com...
> > >> >> >I am doing a point in time recover of a database. I point to the
> > >> >> >backup
> > >> >> >and
> > >> >> > trans logs and begin the recovery. It says (Loading database) and
> > >> >> > it
> > >> >> > starts.. It goes through the backup then applies all the
> > >> >> > translogs.
> > >> >> > When
> > >> >> > it gets to the end it says, DATABASE RESTORE HAS COMPLETED
> > >> >> > SUCCESSFULLY.
> > >> >> > But
> > >> >> > when I go to the database, it is still at a status of LOADING. How
> > >> >> > do
> > >> >> > I
> > >> >> > bring the database online? Thanks
> > >> >> > Jason Greene
> > >> >> >
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >|||I did it through the Enterprise Manager.
Backup Transaction Logs.
"Amando Resendez" wrote:
> Hi everybody:
> Hi Jason. what command did you execute to backup the transacation log file
> when you had "the old datafile with the current logfile"?.
> I'm getting the following error when trying to backup the transaction log
> file:
> --
> Database 'NorthwindTest' cannot be opened due to inaccessible files or
> insufficient memory or disk space. See the SQL Server errorlog for details.
> BACKUP LOG is terminating abnormally.
> --
> The event log shows that the following command its executing:
> --
> BACKUP LOG [NorthwindTest] TO DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\NorthwindTest.bak' WITH NOINIT , NOUNLOAD , NAME => N'NorthwindTest backup', NOSKIP , STATS = 10, NOFORMAT
> --
> Any one knows a good procedure to achieve a restore of a database to the
> point of failure?
> Cheers!
> Amando Reséndez
>
> "Jason Greene" wrote:
> > It is raid 5 double parity. The entire array died.. All drives at the same
> > time. I think it was electrical.
> >
> > On the original note.. I got an old copy of my database .mdf file and
> > logfile .ldf file. I mounted this database and brought it only. Then I took
> > the database offline, then swithed the logfile of the old one with the good
> > current one. Then I restarted the computer.. When it came online, it
> > brought the database online as Suspect.. Old datafile, current logfile. I
> > then issued a Backup Logfile command and it backed it up. I then used that
> > logfile backup to restore the database to the time the database crashed..
> >
> >
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> > > Hi
> > >
> > > No, the transaction log is no good to you as the sequences do not agree
> > > anymore between the log and the DB.
> > > Kiss that data good bye.
> > >
> > > The fast that your drive crashed and it took your DB's along with it, how
> > > about getting some RAID?
> > >
> > > 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/
> > >
> > > "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
> > > news:3B80AE01-78FA-4D68-BD58-96BDDC08D97F@.microsoft.com...
> > > > That did it.. Thanks.. Now I got my database back to the last good
> > > > Translog
> > > > that was backed up.. Now I am going to try to see if I can use the Online
> > > > Translog that was part of the database when it crashed to see if I can
> > > > restore to the point of failure..
> > > >
> > > >
> > > > "Mike Epprecht (SQL MVP)" wrote:
> > > >
> > > >> Hi
> > > >>
> > > >> There is another connection using the DB, put it into single user mode
> > > >> (Right click on it and select properties)
> > > >>
> > > >> 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/
> > > >>
> > > >> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in message
> > > >> news:5D6388E6-A38F-4C5F-A715-6433A0A43588@.microsoft.com...
> > > >> > When I issue that command I get the following error.
> > > >> >
> > > >> >
> > > >> > Server: Msg 3101, Level 16, State 2, Line 1
> > > >> > Exclusive access could not be obtained because the database is in use.
> > > >> > Server: Msg 3013, Level 16, State 1, Line 1
> > > >> > RESTORE DATABASE is terminating abnormally.
> > > >> >
> > > >> > "Kalen Delaney" wrote:
> > > >> >
> > > >> >> Hi Jason
> > > >> >>
> > > >> >> You may not have requested that SQL Server run the recovery process
> > > >> >> after
> > > >> >> the last log was loaded in.
> > > >> >> From the Query Analyzer, try:
> > > >> >>
> > > >> >> RESTORE DATABASE <name of db> WITH RECOVERY
> > > >> >>
> > > >> >> (Note that you don't need to specify any backup files, as these have
> > > >> >> already
> > > >> >> been loaded. The WITH RECOVERY is the important part.)
> > > >> >> --
> > > >> >> HTH
> > > >> >> --
> > > >> >> Kalen Delaney
> > > >> >> SQL Server MVP
> > > >> >> www.SolidQualityLearning.com
> > > >> >>
> > > >> >>
> > > >> >> "Jason Greene" <JasonGreene@.discussions.microsoft.com> wrote in
> > > >> >> message
> > > >> >> news:73331569-98FB-4010-A7F9-F4EBA2B9DFF4@.microsoft.com...
> > > >> >> >I am doing a point in time recover of a database. I point to the
> > > >> >> >backup
> > > >> >> >and
> > > >> >> > trans logs and begin the recovery. It says (Loading database) and
> > > >> >> > it
> > > >> >> > starts.. It goes through the backup then applies all the
> > > >> >> > translogs.
> > > >> >> > When
> > > >> >> > it gets to the end it says, DATABASE RESTORE HAS COMPLETED
> > > >> >> > SUCCESSFULLY.
> > > >> >> > But
> > > >> >> > when I go to the database, it is still at a status of LOADING. How
> > > >> >> > do
> > > >> >> > I
> > > >> >> > bring the database online? Thanks
> > > >> >> > Jason Greene
> > > >> >> >
> > > >> >>
> > > >> >>
> > > >> >>
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >sql

Recover Transaction Log

Hi - I'm requesting help to understand a concept:
Can you restore a Transaction Log Backup only Transaction Logs backed up
AFTER a Full Backup?
EXAMPLE: Setup assuming 2 hard drives:
* Database MDF files stored on hard drive (A)
* Database Transaction Log files soted on hard drive(B)
First, Lets assume a Full Backup has been peformed nightly for a week at
Midnight.
Second, lets also assume that a week ago I temporaily stoped the
every-2-hour-schedule for a Transaction Log Backup and forgot to turn the
schedule back on - Therefore no Transaction Log Backup occured during the
past week.
Now, lets assume that harddrive (A) crashes at noon today - but hard drive
(B) is ok and still works. I have a succesfull Full Backup at Midnight, so I
know I can restore that.
Question: After Restoring from the Midnight Full Backup, Can I use the
current Transaction Log file as of Noon to restore the remainder of the logs
,
even though it had not been backuped for 6 days? Or is it required that a
transaction log backup have occured at some point after the Full backup?> Question: After Restoring from the Midnight Full Backup, Can I use the
> current Transaction Log file as of Noon to restore the remainder of the lo
gs,
> even though it had not been backuped for 6 days?
No. The reason you can't do that is the when you do the restore, you will ov
erwrite the log records
in the ldf file. What you can do, is *before* you do the restore, backup the
log. You now have a
database backup and a log backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"labsRcool@.community.nospan" <labsRcoolcommunitynospan@.discussions.microsoft
.com> wrote in message
news:ACC73D9F-EE1A-4E2B-B25D-24F4927A8BC6@.microsoft.com...
> Hi - I'm requesting help to understand a concept:
> Can you restore a Transaction Log Backup only Transaction Logs backed up
> AFTER a Full Backup?
> EXAMPLE: Setup assuming 2 hard drives:
> * Database MDF files stored on hard drive (A)
> * Database Transaction Log files soted on hard drive(B)
> First, Lets assume a Full Backup has been peformed nightly for a week at
> Midnight.
> Second, lets also assume that a week ago I temporaily stoped the
> every-2-hour-schedule for a Transaction Log Backup and forgot to turn the
> schedule back on - Therefore no Transaction Log Backup occured during the
> past week.
> Now, lets assume that harddrive (A) crashes at noon today - but hard drive
> (B) is ok and still works. I have a succesfull Full Backup at Midnight, so
I
> know I can restore that.
> Question: After Restoring from the Midnight Full Backup, Can I use the
> current Transaction Log file as of Noon to restore the remainder of the lo
gs,
> even though it had not been backuped for 6 days? Or is it required that a
> transaction log backup have occured at some point after the Full backup?
>
>|||Thanks!
Follow up question:
If I do as you suggest, and Backup the current log, then I'm assuming the
process is as follows:
1)Restore Full Backup from the midnight backup
2)Restore the Log backup that I just "backed up" before the restore.
In regards to the Log restore, I'm assuming then, that the restore process
is smart enough to only include from the transaction log the transactions
that have occured since the Midnight backup...and will not attempt to
re-include(duplicate) the transaction over the last week. (i.e., it's now
just treated as a normal transaction log backup/restore as if it I had been
backing them up like I was supposed to ;)
Thanks!
"Tibor Karaszi" wrote:

> No. The reason you can't do that is the when you do the restore, you will
overwrite the log records
> in the ldf file. What you can do, is *before* you do the restore, backup t
he log. You now have a
> database backup and a log backup.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "labsRcool@.community.nospan" <labsRcoolcommunitynospan@.discussions.microso
ft.com> wrote in message
> news:ACC73D9F-EE1A-4E2B-B25D-24F4927A8BC6@.microsoft.com...
>|||Yes, that is correct. The database backup has all log records produced durin
g that database backup.
So, restore will copy in the data pages, and use the log records in the data
base backup to do the
REDO process. When you then restore the log backup, SQL Server will start th
e REDO operation from
the very last log record in the database backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"labsRcool@.community.nospan" <labsRcoolcommunitynospan@.discussions.microsoft
.com> wrote in message
news:0CED8BB9-8B1D-4E04-BD07-B9525D1A5037@.microsoft.com...[vbcol=seagreen]
> Thanks!
> Follow up question:
> If I do as you suggest, and Backup the current log, then I'm assuming the
> process is as follows:
> 1)Restore Full Backup from the midnight backup
> 2)Restore the Log backup that I just "backed up" before the restore.
> In regards to the Log restore, I'm assuming then, that the restore process
> is smart enough to only include from the transaction log the transactions
> that have occured since the Midnight backup...and will not attempt to
> re-include(duplicate) the transaction over the last week. (i.e., it's now
> just treated as a normal transaction log backup/restore as if it I had bee
n
> backing them up like I was supposed to ;)
> Thanks!
> "Tibor Karaszi" wrote:
>

Recover Transaction Log

Hi - I'm requesting help to understand a concept:
Can you restore a Transaction Log Backup only Transaction Logs backed up
AFTER a Full Backup?
EXAMPLE: Setup assuming 2 hard drives:
* Database MDF files stored on hard drive (A)
* Database Transaction Log files soted on hard drive(B)
First, Lets assume a Full Backup has been peformed nightly for a week at
Midnight.
Second, lets also assume that a week ago I temporaily stoped the
every-2-hour-schedule for a Transaction Log Backup and forgot to turn the
schedule back on - Therefore no Transaction Log Backup occured during the
past week.
Now, lets assume that harddrive (A) crashes at noon today - but hard drive
(B) is ok and still works. I have a succesfull Full Backup at Midnight, so I
know I can restore that.
Question: After Restoring from the Midnight Full Backup, Can I use the
current Transaction Log file as of Noon to restore the remainder of the logs,
even though it had not been backuped for 6 days? Or is it required that a
transaction log backup have occured at some point after the Full backup?
Thanks!
Follow up question:
If I do as you suggest, and Backup the current log, then I'm assuming the
process is as follows:
1)Restore Full Backup from the midnight backup
2)Restore the Log backup that I just "backed up" before the restore.
In regards to the Log restore, I'm assuming then, that the restore process
is smart enough to only include from the transaction log the transactions
that have occured since the Midnight backup...and will not attempt to
re-include(duplicate) the transaction over the last week. (i.e., it's now
just treated as a normal transaction log backup/restore as if it I had been
backing them up like I was supposed to ;)
Thanks!
"Tibor Karaszi" wrote:

> No. The reason you can't do that is the when you do the restore, you will overwrite the log records
> in the ldf file. What you can do, is *before* you do the restore, backup the log. You now have a
> database backup and a log backup.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "labsRcool@.community.nospan" <labsRcoolcommunitynospan@.discussions.microsoft.co m> wrote in message
> news:ACC73D9F-EE1A-4E2B-B25D-24F4927A8BC6@.microsoft.com...
>
sql

Recover Bad Logs

SQL Server 2000
We have a database that crashed and the log file is bad and the last good
backup was october, so the transaction log is bigger than the data file.
Since the transaction log is corrupt,and we have no backups because the last
good backup was october, can I do something with that corrupt transaction log
to get those transactions out of it and bring forward the database to be
current with all transactions?
Need some help. This is a production DB.
Chris Davoli
"Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
news:044D39A3-23A9-424A-93AC-A8D063962B5C@.microsoft.com...
> SQL Server 2000
> We have a database that crashed and the log file is bad and the last good
> backup was october, so the transaction log is bigger than the data file.
> Since the transaction log is corrupt,and we have no backups because the
> last
> good backup was october, can I do something with that corrupt transaction
> log
> to get those transactions out of it and bring forward the database to be
> current with all transactions?
Call Microsoft. Most likely your DB is hosed. But sometimes they can work
miracles.

> Need some help. This is a production DB.
> --
> Chris Davoli
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Recover Bad Logs

SQL Server 2000
We have a database that crashed and the log file is bad and the last good
backup was october, so the transaction log is bigger than the data file.
Since the transaction log is corrupt,and we have no backups because the last
good backup was october, can I do something with that corrupt transaction log
to get those transactions out of it and bring forward the database to be
current with all transactions?
Need some help. This is a production DB.
--
Chris Davoli"Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
news:044D39A3-23A9-424A-93AC-A8D063962B5C@.microsoft.com...
> SQL Server 2000
> We have a database that crashed and the log file is bad and the last good
> backup was october, so the transaction log is bigger than the data file.
> Since the transaction log is corrupt,and we have no backups because the
> last
> good backup was october, can I do something with that corrupt transaction
> log
> to get those transactions out of it and bring forward the database to be
> current with all transactions?
Call Microsoft. Most likely your DB is hosed. But sometimes they can work
miracles.
> Need some help. This is a production DB.
> --
> Chris Davoli
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||"Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
news:044D39A3-23A9-424A-93AC-A8D063962B5C@.microsoft.com...
> SQL Server 2000
> We have a database that crashed and the log file is bad and the last good
> backup was october, so the transaction log is bigger than the data file.
> Since the transaction log is corrupt,and we have no backups because the
> last
> good backup was october, can I do something with that corrupt transaction
> log
> to get those transactions out of it and bring forward the database to be
> current with all transactions?
> Need some help. This is a production DB.
> --
> Chris Davoli
>