Restore database from the most recent backup on another server

• dface_21

  Mr or Mrs. 500

  Points: 509

  More actions

  January 18, 2017 at 8:33 am

  #339501

  I want to setup a SQL agent job that restores over an existing database.  This will be done a couple times a month, the backup file will be located in a shared folder on another server.  This shared folder may have multiples .bak files like below. I need to be able to restore the most recent file if more than one file exists in the folder. 

  QAPROD_2017010639.bak
  QAPROD_2017011739.bak

  Currently I believe I have the restore statement correctly but not sure how add the additional pieces of restoring the most recent backup file.  I need help with adding that part of the query to what I have below.

  USE [master]

  RESTORE DATABASE [QAPROD]

  FROM DISK = N'\\Svdqasql01\Import\QAPROD_2017010639.bak'

  WITH FILE = 1, MOVE N'RPMFOUND' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\HRPROD.mdf',

  MOVE N'RPMFOUND_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\HRPROD.ldf', NOUNLOAD, REPLACE, STATS = 5

  GO

• rvsc48

  SSCertifiable

  Points: 7738

  More actions

  January 18, 2017 at 11:11 am

  #1923493

  See if this script will help you.  To get the most recent backup, the msdb database is utilized - 
  -- This works really well on the source

  DECLARE @databaseName sysname
  DECLARE @backupStartDate datetime
  DECLARE @backup_set_id_start INT
  DECLARE @backup_set_id_end INT

  -- set database to be used
  SET @databaseName = '<your_database_name_here>'

  SELECT @backup_set_id_start = MAX(backup_set_id)
  FROM  msdb.dbo.backupset
  WHERE database_name = @databaseName AND type = 'D'

  SELECT @backup_set_id_end = MIN(backup_set_id)
  FROM  msdb.dbo.backupset
  WHERE database_name = @databaseName AND type = 'D'
  AND backup_set_id > @backup_set_id_start

  IF @backup_set_id_end IS NULL SET @backup_set_id_end = 999999999

  SELECT backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' FROM DISK = '''
                 + mf.physical_device_name + ''' WITH NORECOVERY'
  FROM    msdb.dbo.backupset b,
             msdb.dbo.backupmediafamily mf
  WHERE    b.media_set_id = mf.media_set_id
             AND b.database_name = @databaseName
            AND b.backup_set_id = @backup_set_id_start
  UNION
  SELECT backup_set_id, 'RESTORE LOG ' + @databaseName + ' FROM DISK = '''
                 + mf.physical_device_name + ''' WITH NORECOVERY'
  FROM    msdb.dbo.backupset b,
             msdb.dbo.backupmediafamily mf
  WHERE    b.media_set_id = mf.media_set_id
             AND b.database_name = @databaseName
            AND b.backup_set_id >= @backup_set_id_start AND b.backup_set_id < @backup_set_id_end
            AND b.type = 'L'
  UNION
  SELECT 999999999 AS backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' WITH RECOVERY'
  ORDER BY backup_set_id
  ]

• dface_21

  Mr or Mrs. 500

  Points: 509

  More actions

  January 18, 2017 at 12:11 pm

  #1923520

  RVSC48 - Wednesday, January 18, 2017 11:11 AM

  See if this script will help you.  To get the most recent backup, the msdb database is utilized - 
  -- This works really well on the source

  DECLARE @databaseName sysname
  DECLARE @backupStartDate datetime
  DECLARE @backup_set_id_start INT
  DECLARE @backup_set_id_end INT

  -- set database to be used
  SET @databaseName = '<your_database_name_here>'

  SELECT @backup_set_id_start = MAX(backup_set_id)
  FROM  msdb.dbo.backupset
  WHERE database_name = @databaseName AND type = 'D'

  SELECT @backup_set_id_end = MIN(backup_set_id)
  FROM  msdb.dbo.backupset
  WHERE database_name = @databaseName AND type = 'D'
  AND backup_set_id > @backup_set_id_start

  IF @backup_set_id_end IS NULL SET @backup_set_id_end = 999999999

  SELECT backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' FROM DISK = '''
                 + mf.physical_device_name + ''' WITH NORECOVERY'
  FROM    msdb.dbo.backupset b,
             msdb.dbo.backupmediafamily mf
  WHERE    b.media_set_id = mf.media_set_id
             AND b.database_name = @databaseName
            AND b.backup_set_id = @backup_set_id_start
  UNION
  SELECT backup_set_id, 'RESTORE LOG ' + @databaseName + ' FROM DISK = '''
                 + mf.physical_device_name + ''' WITH NORECOVERY'
  FROM    msdb.dbo.backupset b,
             msdb.dbo.backupmediafamily mf
  WHERE    b.media_set_id = mf.media_set_id
             AND b.database_name = @databaseName
            AND b.backup_set_id >= @backup_set_id_start AND b.backup_set_id < @backup_set_id_end
            AND b.type = 'L'
  UNION
  SELECT 999999999 AS backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' WITH RECOVERY'
  ORDER BY backup_set_id
  ]

  Thanks for the response and excuse my ignorance.  I did run the script it did give me the most recent backup.  The thing is that these backup files are coming from a vendor that will put in this location by one of the developers.  The just want a job setup so they can easily run to overwrite the existing database from the previous backup file.