June 28, 2013 at 6:55 pm
Hi,
I'm using Ola's backup script (http://ola.hallengren.com/) to backup the database.
The problem I'm facing the Restore process.
It took 4 hours to restore one database. The actual database restore is only 30 mins. But it took time to prepare rhe restore script. We are using litespeed to backup.
It took 5 mins to prepare restore script to restore full differential but took 2 hrs to manually prepare restore script to restore transaction log backups. Please advise better way to automate or build dynamic restore script to restore log backups?
Restore scenario that I performed:
full backup: Occurs Weekly on every Sunday 7pm
Diff backup: Occurs weekly on Mon, Tue, Wed, Thu, Fri and Saturday at 7pm
Log backup: Occurs every 15 mins
Restore scenario:
On 06/28/2013, at Friday 3:35 pm database wss_content_DB1 got corrupted and need to restore with no or minimal data loss
1. Restore recent full backup with norecovery. ie resotre the full backup performed on 06/22/2013, Sunday 7pm.
2. Restore the most recent differential backup with no recovery. i.e restore the differential backup performed on
06/27/2013, Thursday 7pm
3. Restore all log backups after the last differential backup i.e restore log backups from Thursday till last transaction
backup available on Friday 3:30 pm.
Script used:
--restore fullback with norecovery
exec master.dbo.xp_restore_database @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\FULL\VS1$ABCINS1_WSS_Content_DB1_FULL_20130623_013445.lsbak',
@with = 'MOVE ''WSS_Content_DB1'' TO ''E:\SQLData\ABCINS1\WSS_Content_DB1.mdf''',
@with = 'MOVE ''WSS_Content_DB1_1'' TO ''F:\SQLData\ABCINS1\WSS_Content_DB1_1.ndf''',
@with = 'MOVE ''WSS_Content_DB1_log'' TO ''G:\SQLLogs\ABCINS1\WSS_Content_DB1_log.ldf'''
@with = NORECOVERY,
@with ='STATS = 10'
go
--Restore diffrential backup with norecovery
exec master.dbo.xp_restore_database @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\DIFF\VS1$ABCINS1_WSS_Content_DB1_DIFF_20130627_190614.lsbak',
@with = 'MOVE ''WSS_Content_DB1'' TO ''E:\SQLData\ABCINS1\WSS_Content_DB1.mdf''',
@with = 'MOVE ''WSS_Content_DB1_1'' TO ''F:\SQLData\ABCINS1\WSS_Content_DB1_1.ndf''',
@with = 'MOVE ''WSS_Content_DB1_log'' TO ''G:\SQLLogs\ABCINS1\WSS_Content_DB1_log.ldf'''
@with = NORECOVERY,
@with ='STATS = 10'
go
--Resotre log backups with no recovery
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130627_191635.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130627_193120.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130627_194620.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130627_200121.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130627_201619.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130627_203119.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130627_204618.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130627_210118.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130627_211617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130627_213117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130627_214618.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130627_220117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130627_221617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130627_223117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130627_224617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130627_230117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130627_231617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130627_233117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130627_234617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_000137.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_001618.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_003117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_004617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_010117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_011617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_013117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_014617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_020118.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_021617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_023117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_024617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_030117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_031617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_033117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_034616.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_040117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_041617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_043117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_044617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_050117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_051617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_053117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_054617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_060118.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_061617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_063117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_064617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_070117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_071617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_073117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_074617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_080117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_081617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_083117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_084617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_090117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_091617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_093117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_094617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_100117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_101617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_103117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_104617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_104617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_110117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_111617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_113117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_114617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_120118.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_121617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_123117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_124617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_130117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_131617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_133117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_134617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_140117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_141617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_143117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_144617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_150117.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_151617.lstrn',
@with = NORECOVERY,
@with ='STATS = 10'
go
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1',
@filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log\VS1$ABCINS1_WSS_Content_DB1_LOG_20130628_153117.lstrn',
@with = RECOVERY,
@with ='STATS = 10'
go
Thanks
Gary
July 1, 2013 at 1:22 am
You can build the command for the LOG restore by querying the MSDB..BACKUPFILE table. Something like this:
Removed because the code wasn't correct
See post below for correct code
You can adjust and/or expand the above code to be more flexible/robust/etc. But this code will give you a good starting point to build upon.
July 2, 2013 at 5:20 pm
What out put should we expect here?
I'm getting the below command when I execute the given script. I'm not getting all log backup commands? Please advise..
(No column name)
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1', @filename=' Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\Log', @with = NORECOVERY, @with ='STATS = 10' FROM [msdb]..[backupfile] bf INNER JOIN [msdb]..[backupset] bs ON bf.backup_set_id = bs.backup_set_id WHERE database_name = 'WSS_Content_DB1' bs.type = 'L' AND backup_start_date > '20130628 13:45' -- adjust to the time of used FULL/DIFF backup ORDER BY backup_finish_date
July 3, 2013 at 12:11 am
I noticed an apostrof to little!
You'll need to add an extra apostrof after the text stats=10
I've edited the post above
July 3, 2013 at 4:58 pm
After running the below script, I'm able to generate the script:
Script:
SELECT 'exec master.dbo.xp_restore_log
@Database=''WSS_Content_DB1'',
@filename=''Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\LOG'',
@with = NORECOVERY,
@with =''STATS = 10'''
FROM [msdb]..[backupfile] bf
INNER JOIN [msdb]..[backupset] bs
ON bf.backup_set_id = bs.backup_set_id
WHERE database_name = 'WSS_Content_others' AND
bs.type = 'L'
AND backup_start_date > '20130630 13:45' -- adjust to the time of used FULL/DIFF backup
ORDER BY backup_finish_date
Generated Script:
I'm pasting only sample rows. I got too many rows.
Question:
Here, after the backup path (Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\LOG), there are actual log backup files like "VS1$ABCINS1_WSS_Content_DB1_LOG_20130703_154617.lstrn". But our generated script does not have the actual log backup files in it. It generating until the log backup path not the actual log backup files. I did NOT see any use with out having the actual log flies. Please advise
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1', @filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\LOG', @with = NORECOVERY, @with ='STATS = 10'
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1', @filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\LOG', @with = NORECOVERY, @with ='STATS = 10'
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1', @filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\LOG', @with = NORECOVERY, @with ='STATS = 10'
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1', @filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\LOG', @with = NORECOVERY, @with ='STATS = 10'
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1', @filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\LOG', @with = NORECOVERY, @with ='STATS = 10'
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1', @filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\LOG', @with = NORECOVERY, @with ='STATS = 10'
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1', @filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\LOG', @with = NORECOVERY, @with ='STATS = 10'
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1', @filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\LOG', @with = NORECOVERY, @with ='STATS = 10'
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1', @filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\LOG', @with = NORECOVERY, @with ='STATS = 10'
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1', @filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\LOG', @with = NORECOVERY, @with ='STATS = 10'
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1', @filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\LOG', @with = NORECOVERY, @with ='STATS = 10'
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1', @filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\LOG', @with = NORECOVERY, @with ='STATS = 10'
July 4, 2013 at 12:16 am
I'm sorry, due to too little time on my hands I didn't generate the correct code and didn't test this in my own environment. Today I'm able to give you a better and tested answer.
Below is the code you can use:
USE MSDB
SELECT
-- build a restore string and combine this with values from the tables
'exec master.dbo.xp_restore_log
@Database=''' + database_name + ''',
@filename=''' + physical_device_name + ''',
@with = NORECOVERY,
@with =STATS = 10'
FROM [backupmediafamily] bmf
INNER JOIN [backupset] bs
ON bmf.media_set_id = bs.media_set_id
WHERE database_name = 'WSS_Content_DB1'-- adjust to the required database name
AND bs.type = 'L'-- adjust to the kind of backup (D=FULL, L=LOG)
AND [backup_start_date] > '20130701'-- adjust to the required start date
This code creates a string and combines this string with values (databasename and backup filename) from the SELECT statement. The result are multiple strings you can copy/past and execute as a SQL commands. The filter is set on databasename, type of backup and date of backup. Keep in mind that some options are hardcoded in the string (like NORECOVERY). You have to adjust this before executing the generated commands.
July 4, 2013 at 12:05 pm
Thanks Hanshi,
But we do not have backup device. Every 15 mins, log backup creates a new log backup file under the backup path
Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\LOG
So in the @filename, I'm giving the log backup path as @filename=''Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\LOG''
still the script that generated have only the backup path but no the actual log backup files
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1', @filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\LOG', @with = NORECOVERY, @with ='STATS = 10'
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1', @filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\LOG', @with = NORECOVERY, @with ='STATS = 10'
I'm expecting the below output:
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1', @filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\LOG\WSS_Content_DB1_20130704_104616.lstrn', @with = NORECOVERY, @with ='STATS = 10'
exec master.dbo.xp_restore_log @Database='WSS_Content_DB1', @filename='Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\LOG\WSS_Content_DB1_20130704_110117.lstrn', @with = NORECOVERY, @with ='STATS = 10'
July 4, 2013 at 1:36 pm
gary1 (7/4/2013)
So in the @filename, I'm giving the log backup path as @filename=''Z:\Backups\VS1$ABCINS1\WSS_Content_DB1\LOG''
That is not what you have to do. You are changing the variable part into a hard-coded string. This hard-coded string will be returned in each row selected, hence every generated commandline has exactly the same text as filename.
If you look at my code, you see I select the value for @filename from each row out of the column "physical_device_name". This value has the full path included, so you can just run the code and leave the SELECT part of my code unchanged.
June 19, 2015 at 12:47 pm
I was facing the same issue and wrote a PowerShell script to do the work for me, it will run on a machine with PowerShell 4.0 installed and SQL Management Studio it installs the Management objects. You can restore to a different server, will send an email when complete if dbMil is setup and can restore to a network share if running 2012
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply