Restore from log and backup questions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Restore from log and backup questions

Author

Topic

NickStan
Starting Member

36 Posts

Posted - 2012-05-01 : 05:49:12

Hi guys

We have someone drop a table on the 26th of April 2012.

Unfortunately we did not have any backups of the database :(
We did have backups of the transaction log files before issue happened.

On the 27th I took a backup of our database.

Now my question is what order to you need to restore your database and log files?

Does the backup file need to happen before the transaction log files or can it happen after the logs files? Both transaction logs are before the backup.

My other question is, whenever I try to run this:

CREATE PROCEDURE sp_restore_from_log AS
BEGIN

EXEC sp_Kill_All_processes

ALTER DATABASE REPORTS SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE LOG REPORTS FROM DISK = '.trn path ' WITH NORECOVERY
RESTORE LOG REPORTS FROM DISK = '.trn path ' WITH RECOVERY

ALTER DATABASE REPORTS SET MULTI_USER

END

DROP PROCEDURE sp_restore_from_log

I get the following error message:

Server: Msg 4306, Level 16, State 1, Procedure sp_restore_from_log, Line 9
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Procedure sp_restore_from_log, Line 9
RESTORE LOG is terminating abnormally.
Server: Msg 4306, Level 16, State 1, Procedure sp_restore_from_log, Line 10
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Procedure sp_restore_from_log, Line 10
RESTORE LOG is terminating abnormally.

Do I need to restore the database and the logs at the same time?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-05-01 : 12:32:07

The backup has to happen BEFORE the transaction log backups.

Regarding your stored procedure, you must use RESTORE DATABASE WITH NORECOVERY first.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

NickStan
Starting Member

36 Posts

Posted - 2012-05-02 : 06:15:10

Hi Tara

I thought as much but thanks for confirming.

Nick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources