This post provides you with a script that will generate a restore script for a database using the latest full backup file that exists in a directory. (No need to state the filename explicitly)
You need to provide the following at the start of the script:
- The target database i.e. the database you will restore to
- The directory where the backup file is saved
If you are using the excellent Ola Hallengren maintenance solution (see link) the directory path will look something like below. If you’re not using Ola’s solution, you should be.
This restore script is designed to work with Ola’s solution as it segregates the backup directory structure such that each database has an allocated folder and each full backup file is named with the date and time of the file creation.
The restore script determines which backup file is the latest backup file based on the max name. So for the script to work it is assumed you have an appropriate backup strategy (i.e. using Ola’s solution) were backup types are segregated into different folders, backup names have a date reference and the backup location is dedicated to backups and nothing else, i.e. no trash files in the location.
Some use cases for this solution might be:
- Restoring a nightly backup to another instance for reporting purposes
- Restoring backups to a development environment
- Restoring backups to another server to test the backups
You can use the logic in a stored procedure or as the T-SQL in a job step and schedule accordingly.
/* You need to reference the following: * The target database i.e. the database you will restore to. * The directory where the backup file is saved. If you are using the Ola Hallengren backup scripts the directory path will look something like below. \\ServerWhereBackupsAreSaved\DriveName\InstanceName\TargetDatabase\Full\ Find & Replace the follow text for the target database and directory: TARGET_DATABASE DIR_PATH */ /* Declare Variables */ DECLARE @DatabaseToRestore AS VARCHAR(MAX); DECLARE @DirToSearch AS VARCHAR(MAX); DECLARE @ShellCommand AS VARCHAR(MAX); DECLARE @BackupFile AS VARCHAR(MAX); DECLARE @Sql AS VARCHAR(MAX); /* Set User Variables */ SET @DatabaseToRestore = 'TARGET_DATABASE'; SET @DirToSearch = 'DIR_PATH'; SET @ShellCommand = 'dir ' + @DirToSearch; /* Create Temp Table To Hold xp_cmdshell Output */ IF OBJECT_ID('tempdb..#DirList') IS NOT NULL DROP TABLE #DirList; CREATE TABLE #DirList ( Id INT identity(1, 1) ,line NVARCHAR(1000) ); /* Enable Advanced Options To Enable xp_cmdshell Temporarily */ EXEC master.dbo.sp_configure 'show advanced options' ,1; RECONFIGURE WITH OVERRIDE; EXEC master.dbo.sp_configure 'xp_cmdshell' ,1; RECONFIGURE WITH OVERRIDE; /* Run The Shell Command To Capture And Write Dir Info To Temp Table */ SET @Sql = ' INSERT INTO #DirList (line) EXEC xp_cmdshell ' + '''' + @ShellCommand + '''' + ';'; EXEC (@Sql); EXEC master.dbo.sp_configure 'xp_cmdshell' ,0; /* Disable Advanced Options And xp_cmdshell Again */ RECONFIGURE WITH OVERRIDE; EXEC master.dbo.sp_configure 'show advanced options' ,0; RECONFIGURE WITH OVERRIDE; /* Get The Last Backup File Name And Save To A Variable */ WITH CTE AS ( SELECT SUBSTRING(line, 37, 100) [FileName] FROM #DirList WHERE Id > ( SELECT MIN(Id) FROM #DirList WHERE line LIKE '%<DIR>%..%' ) AND Id < ( SELECT MAX(Id) - 2 FROM #DirList ) ) SELECT @BackupFile = [FileName] FROM CTE WHERE [FileName] = ( SELECT MAX(FileName) FROM CTE ); /* Create The Restore Script */ SET @BackupFile = @DirToSearch + @BackupFile SET @Sql = ' ALTER DATABASE ' + QUOTENAME(@DatabaseToRestore) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE; RESTORE DATABASE ' + QUOTENAME(@DatabaseToRestore) + ' FROM DISK = ' + '''' + @BackupFile + '''' + ' WITH NORECOVERY ,REPLACE; RESTORE DATABASE ' + QUOTENAME(@DatabaseToRestore) + ' WITH RECOVERY; ' /* To Directly Execute The Script Uncomment The EXEC Statement And Delete The Select Statement */ /* EXEC (@Sql) */ SELECT @Sql
2 thoughts on “How to schedule a job to restore the last backup made of a SQL Server database”
This is a great script and I like it. Problem is, I can’t count on the file size accounted for in the SELECT SUBSTRING 37 statement and I wanted something which would work regardless of size. So I leveraged your work as a Primer and produced this. Works like a charm on SQL 2016! Thanks!
Script: Reads filename in directory and restores the
most recent backup (bak) file
Modify code to match database names and paths.
***CAREFUL*** It drops existing connections and kicks users off DB!
Declare @DBNAME varChar(255)
Declare @FileName varChar(255)
Declare @cmdText varChar(255)
Declare @BKFolder varchar(255)
Declare @SQL varchar(255)
–Set DBName and BKFolder
set @FileName = null
set @cmdText = null
Set @DBName = ‘[DBNAME]’
set @BKFolder = ‘\\PATH\to\bak_file\’
IF OBJECT_ID(‘tempdb..#FileList’) IS NOT NULL
DROP TABLE #FileList;
create table #FileList (
–get all the files and folders in the backup folder and put them in temporary table
insert into #FileList exec xp_dirtree @BKFolder,0,1
–get the latest backup file name
select top 1 @FileName = @BKFolder + FileName from #FileList where Filename like ‘%.bak’ order by filename desc
–Set DB to Single_User and kick off current users or connections
EXEC(‘ALTER DATABASE’ + @DBName + ‘SET SINGLE_USER WITH ROLLBACK IMMEDIATE’);
–execute the restore
Set @SQL =
‘RESTORE DATABASE ‘+ @DBNAME + ‘ FROM DISK =”’ + @filename + ”” + ‘ WITH REPLACE;’
–Change Comment below to Print ONLY vice EXECUTE
—-Set to Multi_User and let users and connections back in!
EXEC(‘ALTER DATABASE’ + @DBName + ‘SET MULTI_USER WITH ROLLBACK IMMEDIATE’);
Hi Bill, Glad I could help, thanks for contributing your work.