Restore Transaction logs - 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

SQL Server 2000 Forums

SQL Server Administration (2000)

Restore Transaction logs

Author

Topic

MediaPirate
Yak Posting Veteran

52 Posts

Posted - 2004-09-29 : 16:45:17

Here's the details:

I have a full backup of my database and the full transaction logs from 2am on the 27th. At 12am on the 28th I table was cleared in my database. At 2am another full backup with a full transaction log was backed up. I was wondering if I could either 1, restore the full backup from the 27th and use the full log from the 28th and restore all data from the 28th's log up to 12am? If so, how would I go about doing this? Or is there another method?

SQL 2000 SP3a, Windows 2003 STD.

chadmat
The Chadinator

1974 Posts

Posted - 2004-09-29 : 17:01:57

If I understand correctly, then yes you can. You need the full backup from the 27th and the Logbackup from the 28th.

Look up Point In Time restore in Books Online for the proper syntax.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

MediaPirate
Yak Posting Veteran

52 Posts

Posted - 2004-09-29 : 18:09:52

I have a full backup of the 27th restored and a full backup of the 29th restored. I made a transaction backup of the 29th's logs and stored it in a \dbname29thlogs\dbname29th.trn file. If I click on the 27th's database and perform a restore, select Show backups for 29th , first back to restore 9/29/04 6pm , check Point in time restore it will not let me select a time before 6pm. I want to restore the transaction logs from the 29th at no later than 12:01:00 am on the 29th to the 27th's database.

Any help would be appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-29 : 18:15:31

Could you list out the backups that you have like this:

Full 09/27 6am
Tlog 09/27 7am
Tlog 09/27 6pm
Full 09/28 9am
...

Or whatever, then tell us what date and time to restore to. Then we'll be able to tell you which files you'll need and what commands to use.

Tara

MediaPirate
Yak Posting Veteran

52 Posts

Posted - 2004-09-29 : 21:45:13

First Backup to restore: 09/29/2004 2:25:42 AM -
"Backup Set Date"
09/29/2004 2:25:42 AM FULL BACKUP
09/29/2004 11:06:35 AM FTRAN
09/29/2004 12:00:18 PM FTRAN
09/29/2004 1:00:27 PM FTRAN
09/29/2004 2:00:27 PM FTRAN
09/29/2004 3:00:27 PM FTRAN
09/29/2004 4:00:27 PM FTRAN

First Backup to restore: 09/28/2004 2:05:42 AM
"Backup Set Date"

09/28/2004 2:17:10 AM FULL BACKUP
09/28/2004 2:17:10 AM FULL BACKUP

09/27/2004 2:27:10 AM FULL BACKUP

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-30 : 03:48:45

quote:
Originally posted by MediaPirate

I want to restore the transaction logs from the 29th at no later than 12:01:00 am on the 29th to the 27th's database.

First Backup to restore: 09/29/2004 2:25:42 AM -
"Backup Set Date"
09/29/2004 2:25:42 AM FULL BACKUP
09/29/2004 11:06:35 AM FTRAN
09/29/2004 12:00:18 PM FTRAN
09/29/2004 1:00:27 PM FTRAN
09/29/2004 2:00:27 PM FTRAN
09/29/2004 3:00:27 PM FTRAN
09/29/2004 4:00:27 PM FTRAN

First Backup to restore: 09/28/2004 2:05:42 AM
"Backup Set Date"

09/28/2004 2:17:10 AM FULL BACKUP
09/28/2004 2:17:10 AM FULL BACKUP

09/27/2004 2:27:10 AM FULL BACKUP

not sure what you mean but do you mean 12 pm?, you can

1. 09/29/2004 2:25:42 AM FULL BACKUP
2. 09/29/2004 11:06:35 AM FTRAN
3. 09/29/2004 12:00:18 PM FTRAN

1. restore the full backup and set with norecovery
2. restore the tlog backup and set with norecovery
3. restore the tlog backup and set with recovery

no need for point-in-time since the last tlog was backed up at 12:00:18 pm.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources