Restore most recent backup

• dan-572483

  SSCoach

  Points: 15870

  More actions

  December 22, 2010 at 3:31 pm

  #244658

  I'm building a maintance job to restore a backup file copied from another server and run DBCC on it.

  The source folder has copies from multiple days on it, so the folder on the local server has those multiple copies as well, with names similar to:

  DB_Backup_201012200200.bak

  DB_Backup_201012210200.bak

  DB_Backup_201012220200.bak

  So how do I automate restoring only the prior day's backup?

  I am looking at writing a restore script that determines the previous day's backup name as follows:

  declare @DDate as DateTime

  declare @Date as varchar(8)

  declare @BKName as varchar(26)

  Set @DDate = DateAdd(day,-1,GetDate())

  Set @Date = cast (Year(@DDate) as varchar(4)) + cast (Month(@DDate) as varchar(2)) + cast (Day(@DDate) as varchar(2))

  set @BKName 'DB_Backup_' + @Date +'0200.bak'

  I can then use @BKName as the backup file in the RESTORE command.

  Is this the best approach, or is there a better way to do this?

• alen teplitsky

  SSC-Dedicated

  Points: 30014

  More actions

  December 23, 2010 at 9:18 am

  #1265983

  i think it's part of SQL 2005 and later, but you can run it during a backup. i use Netbackup and my scripts are set up to check for consistency during the backup process

• dan-572483

  SSCoach

  Points: 15870

  More actions

  December 23, 2010 at 1:11 pm

  #1266051

  Are you referring to the Verify Backup or Perform checksum checkboxes? Theses aren't the same as a consistency check, are they?

• Wildcat

  SSCarpal Tunnel

  Points: 4774

  More actions

  December 23, 2010 at 2:20 pm

  #1266069

  dan-572483 (12/22/2010)

  ---

  I'm building a maintance job to restore a backup file copied from another server and run DBCC on it.

  The source folder has copies from multiple days on it, so the folder on the local server has those multiple copies as well, with names similar to:

  DB_Backup_201012200200.bak

  DB_Backup_201012210200.bak

  DB_Backup_201012220200.bak

  So how do I automate restoring only the prior day's backup?

  I am looking at writing a restore script that determines the previous day's backup name as follows:

  declare @DDate as DateTime

  declare @Date as varchar(8)

  declare @BKName as varchar(26)

  Set @DDate = DateAdd(day,-1,GetDate())

  Set @Date = cast (Year(@DDate) as varchar(4)) + cast (Month(@DDate) as varchar(2)) + cast (Day(@DDate) as varchar(2))

  set @BKName 'DB_Backup_' + @Date +'0200.bak'

  I can then use @BKName as the backup file in the RESTORE command.

  Is this the best approach, or is there a better way to do this?

  I'd use this approach.

  BTW, for the @BKName, you can use:

  set @BKName = 'DB_Backup_' + CONVERT(VARCHAR, GETDATE() - 1, 112) + '0200.bak'