XML FeedsIf you've ever had to restore log files and not use the restore to a point in time option, I'm sure you found it to be a pain executing all the restore statements.
A good tip when you have to do this is to use vbscript or some other scripting technology to create your own .sql file that you can just execute and restore the .trn files
So for example, I use Quest Litespeed for compression (see here for more info). This means I have a few procedures at my disposal in which I can use to perform my restores from compressed backups. So given a restore script for Litespeed like this
- exec master.dbo.xp_restore_log @database = N'dbname' ,
- @filename = N'path\backup.trn',
- @filenumber = 1,
- @with = N'STATS = 10',
- @with = N'NORECOVERY',
- @with = N'MOVE N''file.mdf'' TO path\file.mdf''',
- @with = N'MOVE N''log.ldf'' TO path\log.ldf''',
- @affinity = 0,
- @logging = 0
- GO
we can take this code and with a little scripting generate a the call on each .trn file found in a particular folder
- Dim fso, folder, files, results,source
- Dim db
- Set fso = CreateObject("Scripting.FileSystemObject")
- source = Wscript.Arguments.Item(0)
- db = Wscript.Arguments.Item(1)
- If source = "" Or db = "" Then
- Wscript.Echo "Check your source or database value passed please and try again"
- Wscript.Quit
- End If
- Set results = fso.CreateTextFile("RestoreScript.sql", True)
- Set folder = fso.GetFolder(source)
- Set files = folder.Files
- For each f In files
- If InStr(f.Name,".trn") > 0 Then
- results.WriteLine("exec master.dbo.xp_restore_log @database = N'" & db & "' , " & _
- "@filename = N'" & f.Path & "', " & _
- "@filenumber = 1, " & _
- "@with = N'STATS = 10', " & _
- "@with = N'NORECOVERY', " & _
- "@with = N'MOVE N''file.mdf'' TO N''path\file.mdf''', " & _
- "@with = N'MOVE N''log.ldf'' TO N''path\log.ldf''', " & _
- "@affinity = 0, " & _
- "@logging = 0 " & _
- VbCrLf & "GO")
- End If
- Next
- results.Close
Now just run that and you'll get your .sql you can execute and be done with it. I usually only do this on special occasions so there aren't parameters like date range and such passed but you could easily add those in and only grab a certain listing of files.
Thanks for this.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.