Restore database from the most recent backup on another server

Question

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

rvsc48 SSCertifiable Points: 7685 More actions · Answer 1

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 · Answer 2

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.