restoring transaction logs in order via script? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

SQL Server Administration (2005)

restoring transaction logs in order via script?

Author

Topic

eddiefdz
Starting Member

21 Posts

Posted - 2011-08-04 : 08:37:07

Hello,

I run a daily full backup of my database and then after that, i run a transaction log backup every 20 minutes.

When it comes to having to restore that database to a single point in time during the day, i would have to restore the full backup and then apply each of the transaction logs in order until i reach the exact point in time that i need.

If that is the case, I would be looking at restoring about 30, 40, maybe even 50 transaction logs by hand.

*My question is: Is there any way to have all of those transaction logs automatically applied in order via some sort of script or even using the GUI? You would think that you would be able to just select them all from a list and have SQL apply them in a particular order, but you can't. I have to be restoring one at a time.

Any help would be greatly appreciated.

Thanks,
Eddie

Eddie Fernandez
IT Director
MTech

manju3606
Yak Posting Veteran

78 Posts

Posted - 2011-08-04 : 09:10:39

You have to take differential backup and after that take tail log backup and restore that

Manju

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-04 : 09:20:06

see

http://msdn.microsoft.com/en-us/library/ms179451.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

eddiefdz
Starting Member

21 Posts

Posted - 2011-08-04 : 09:55:06

Yes, my point is what if i can't take a differential backup because the main database is corrupt or damaged. I would have to restore the full and then the subsequent transaction log backups. I am just asking if there is a way to do them in bulk instead of having to go one by one.

Eddie Fernandez
IT Director
MTech

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-04 : 10:24:06

No, they have to be restored in the correct sequence, and if one should fail the rest of the sequence cannot be restored.

You can use the following script to get the file names and sequence:

SELECT s.database_name, s.backup_start_date, s.backup_finish_date, s.type, mf.physical_device_name 
FROM msdb..backupset s
INNER JOIN msdb..backupmediafamily mf ON s.media_set_id=mf.media_set_id 
WHERE s.database_name='myDB' and s.backup_start_date between '2011-08-01' and '2011-08-02'
ORDER BY s.backup_start_date

You can further modify that to generate the T-SQL RESTORE commands needed.

Peter99
Constraint Violating Yak Guru

498 Posts

Posted - 2011-08-04 : 13:41:06

You have to restore them manually one by one as far as I know.

gvpmusicarte
Starting Member

1 Post

Posted - 2011-08-05 : 12:52:13

Hello,

My issue is kind of similar to Eddie's one, however, it differs in a few things:

-The client I am working for provided me his database however, it is not a .bak file but a query file

Everytime I've tried so far to run it opening the file in the SQL and executing it I get a message saying the file can't be opened

Perhaps...Is there another way to open it?

Guillermo Vargas ©

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources