Can I "recover" just a log? - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Can I "recover" just a log?

Author

Topic

zekmoe
Starting Member

5 Posts

Posted - 2008-05-29 : 13:08:09

I have the need to read a SQL server 2000 transaction log. I've tried to use a tool (SQL Log Rescue) which barfs at the end of the process. I'm thinking this MAY be due to the log file size(about 4 gig). I want to try and restore or recover this transaction log to another test db, and then shrink the log file. I'm hoping that will allow us to read the log file.
Anyway, when I try
RESTORE LOG my_database FROM bak1
go
I get
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, Line 1
RESTORE LOG is terminating abnormally.

I get the same message trying to restore using Enterprise manager. Is there something basic I'm missing to be able to apply this log, to a copy of the db, for the sole purpose of shrinking it? Again, I'm not concerned with the validity of the data, just being able to read the transactions to trace something.

Bob
Not a downstroke, fistpicker

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-29 : 13:11:34

To restore a log, you must first restore a full backup using with norecovery or with standby and then also restore either all of the other tlogs in between or throw in a diff in there. The other restores need to specify with norecovery or with standby as well.

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

zekmoe
Starting Member

5 Posts

Posted - 2008-05-29 : 13:40:07

So if I understand (which I doubt!) I would:
1) First take a backup of the existing test database
2) Then:
RESTORE DATABASE {databasename} FROM {device} WITH NORECOVERY
RESTORE LOG {databasename} FROM {device} WITH NORECOVERY

with log being the path to my log file?

Bob
Not a downstroke, fistpicker

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-29 : 13:41:59

You can not now take a backup of the database and then apply an old log. You must restore a full backup that occurred prior to the log you are trying to restore.

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources