Restoring Transaction LOG from Backup with multiple files

[craizie]

Does anyone know of a easier "automated" way to restore from a transaction log backup that has multiple files in the backup file?

For example i have a db1.log.bak file and the the file contains 4 seperate incremental log backups (one for every hour since the differential backup).

I know you can manually restore each file with the command:

restore log DB1 from disk='db1.log.bak' WITH FILE = 1,
with norecovery

and run that command for each FILE.

What i would like to do is have a script which can determine how many files are in the backup file and loop through until it restores each filegroup from the backup file.

Any help is apprecaited.

Thanks.

 

[mrdenny]

Do a restore FILELISTONLY from the .bak file. It will tell you how many files there are. You can then use the ROWCOUNT returned from that and loop up to that number doing your restore. My Log shipping FAQ in the Setup and Admin forum has an example.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com