SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automate restore script


Automate restore script

Author
Message
gary1
gary1
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4954 Visits: 2254
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
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8738 Visits: 3718
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.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
gary1
gary1
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4954 Visits: 2254
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
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8738 Visits: 3718
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

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
gary1
gary1
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4954 Visits: 2254
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'
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8738 Visits: 3718
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.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
gary1
gary1
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4954 Visits: 2254
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'
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8738 Visits: 3718
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.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
mleslie7
mleslie7
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 9
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

https://onedrive.live.com/redir?resid=C978D0C58B2EA487!1087&authkey=!ALuOt_BmdK8PIWw&ithint=file%2cps1
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search