Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Automate restore script Expand / Collapse
Author
Message
Posted Friday, June 28, 2013 6:55 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 6:42 PM
Points: 667, Visits: 2,055
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
Post #1468755
Posted Monday, July 01, 2013 1:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:59 AM
Points: 2,078, Visits: 2,410
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’! **
Post #1468950
Posted Tuesday, July 02, 2013 5:20 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 6:42 PM
Points: 667, Visits: 2,055
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

Post #1469796
Posted Wednesday, July 03, 2013 12:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:59 AM
Points: 2,078, Visits: 2,410
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’! **
Post #1469853
Posted Wednesday, July 03, 2013 4:58 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 6:42 PM
Points: 667, Visits: 2,055
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'
Post #1470265
Posted Thursday, July 04, 2013 12:16 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:59 AM
Points: 2,078, Visits: 2,410
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’! **
Post #1470313
Posted Thursday, July 04, 2013 12:05 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 6:42 PM
Points: 667, Visits: 2,055
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'
Post #1470494
Posted Thursday, July 04, 2013 1:36 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:59 AM
Points: 2,078, Visits: 2,410
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’! **
Post #1470501
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse