One developer's SQL blog

While going through Brent Ozar’s 6-Month DBA Training Plan, I got myself a good challenge quite fast, after reading “Automating Restores” – to automate restores on backups taken using Ola Hallengren Backup Solution.
After reading the email and being pointed to MSQLTips’s script – a great script and post on how to automate restores, my starting point, i wanted to enhance the script so it can do the following:

• Handle Ola Hallengren backup folder structure
• Have the option to restore the database with a different name
• Create the folders needed for database files if they don’t exist

Before anything, these scripts use xp_cmdshell procedure, so it needs to be enabled:

EXEC sp_configure 'show advanced options', '1'
GO
RECONFIGURE
GO 
EXEC sp_configure 'xp_cmdshell', '1'
GO 
RECONFIGURE
GO

Here’s my demo database i’ve created, and assured it’s in FULL recovery mode.

CREATE DATABASE [TestBackupRestores]
 ON  PRIMARY 
( NAME = N'TestBackupRestores', FILENAME = N'D:\Databases\TestBackupRestores\Data\TestBackupRestores.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TestBackupRestores_log', FILENAME = N'D:\Databases\TestBackupRestores\Log\TestBackupRestores_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [TestBackupRestores] SET RECOVERY FULL
GO

And I already ran Ola’s script to create the stored procedures used for backups.
Using them, I’ve took some *random* backups:

EXEC DatabaseBackup @DataBases = 'TestBackupRestores', @BackupType = 'FULL', @Directory = 'D:\Backups'
EXEC DatabaseBackup @DataBases = 'TestBackupRestores', @BackupType = 'DIFF', @Directory = 'D:\Backups'	
EXEC DatabaseBackup @DataBases = 'TestBackupRestores', @BackupType = 'FULL', @Directory = 'D:\Backups'
EXEC DatabaseBackup @DataBases = 'TestBackupRestores', @BackupType = 'LOG',  @Directory = 'D:\Backups'
EXEC DatabaseBackup @DataBases = 'TestBackupRestores', @BackupType = 'LOG',  @Directory = 'D:\Backups'
EXEC DatabaseBackup @DataBases = 'TestBackupRestores', @BackupType = 'DIFF', @Directory = 'D:\Backups'
EXEC DatabaseBackup @DataBases = 'TestBackupRestores', @BackupType = 'LOG',  @Directory = 'D:\Backups'
EXEC DatabaseBackup @DataBases = 'TestBackupRestores', @BackupType = 'LOG',  @Directory = 'D:\Backups'

With these backups taken, I expect to have a restore sequence for backups taken at lines 3,6,7,8.

Now the code:
I need to provide database name I want to restore as @dbName, optionally, the new database name I want to be restored as – @newDbName and the root backup folder used for stored procedure to take backups as @backupPath.

I got all backup files created and stored the into a table, with fileName and backupType:

-- database backup folder [root folder]\ServerName\DatabaseName
SET @backupPath = CONCAT(@backupPath, '\', @@SERVERNAME, '\', @dbName)

-- get list of files in all subfolders - FULL/DIFF/LOG
SET @cmdshell = CONCAT('DIR /b /s /a-d ', @backupPath)

INSERT INTO @fileList(backupFile) 
EXEC master.sys.xp_cmdshell @cmdshell

UPDATE @fileList SET
	backupType = CASE	WHEN CHARINDEX('\FULL\', backupFile) > 0 THEN 'FULL'
						WHEN CHARINDEX('\DIFF\', backupFile) > 0 THEN 'DIFF'
						WHEN CHARINDEX('\LOG\', backupFile) > 0 THEN 'LOG'
				END

And this is what it gets:

backupFile																				backupType
-------------------------------------------------------------------------------------------------
D:\Backups\SRV\TestBackupRestores\DIFF\SRV_TestBackupRestores_DIFF_20150506_001521.bak	DIFF
D:\Backups\SRV\TestBackupRestores\DIFF\SRV_TestBackupRestores_DIFF_20150506_001532.bak	DIFF
D:\Backups\SRV\TestBackupRestores\FULL\SRV_TestBackupRestores_FULL_20150506_001518.bak	FULL
D:\Backups\SRV\TestBackupRestores\FULL\SRV_TestBackupRestores_FULL_20150506_001524.bak	FULL
D:\Backups\SRV\TestBackupRestores\LOG\SRV_TestBackupRestores_LOG_20150506_001527.trn	LOG
D:\Backups\SRV\TestBackupRestores\LOG\SRV_TestBackupRestores_LOG_20150506_001530.trn	LOG
D:\Backups\SRV\TestBackupRestores\LOG\SRV_TestBackupRestores_LOG_20150506_001534.trn	LOG
D:\Backups\SRV\TestBackupRestores\LOG\SRV_TestBackupRestores_LOG_20150506_001537.trn	LOG

Next, find the latest FULL backup:

SELECT @lastFullBackup = MAX(backupFile)  
FROM @fileList  
WHERE backupType = 'FULL'

If I have a new name for database to be restored as, using RESTORE FILELISTONLY, I’m getting the details about the physical location of the files. I would want the new file names to be renamed if they are using their database name in them, or if there’s a dedicated folder for this database I would want to create a new dedicated folder, or if the same file name is used, on the exact same path, I want to make sure the file name is not the same with the original one.

DECLARE	@dbFiles TABLE (
	LogicalName	NVARCHAR(100), 
	PhysicalName	NVARCHAR(500), 
	... rest of the columns returned by RESTORE FILELISTONLY ...
) 
DECLARE	@dbFilesCompact TABLE (
	LogicalName	NVARCHAR(100), 
	PhysicalName	NVARCHAR(500), 
	NewPhysicalName	NVARCHAR(500))

DECLARE	@filesOnlyCmd NVARCHAR(500) = CONCAT(N'RESTORE FILELISTONLY FROM DISK = ''', @lastFullBackup + '''')

INSERT @dbFiles
EXEC sp_executesql @filesOnlyCmd

INSERT INTO @dbFilesCompact
SELECT	LogicalName, 
	PhysicalName, 
	CASE REPLACE(PhysicalName, @dbName, @newDbName) -- replace database name in file name / path to new database name
		WHEN PhysicalName THEN REPLACE(PhysicalName, '.', '_1.') -- if file name / path is the same as before, add _1 to file_name
		ELSE REPLACE(PhysicalName, @dbName, @newDbName) -- replace database name in file name / path to new database name 
	END
FROM	@dbFiles

And add the MOVE statement for each file:

-- rename the folders \ files so they dont try to overwrite the existing files
		SELECT @cmd += CONCAT(' MOVE ''', LogicalName, ''' TO ''', NewPhysicalName, ''',')
		FROM	@dbFilesCompact

Now, for each new location where the new files should be placed, I need to check it exists, and if not, create the needed folders:

-- need to make sure the folders exists, might be that a new folder needs to be create to put the files into
DECLARE dbFolders CURSOR FAST_FORWARD FOR
	SELECT DISTINCT LEFT(NewPhysicalName, LEN(NewPhysicalName) - CHARINDEX('\', REVERSE(NewPhysicalName)))
	FROM	@dbFilesCompact

DECLARE	@folder NVARCHAR(500)
DECLARE @folderTest TABLE (path NVARCHAR(500))

OPEN dbFolders
FETCH NEXT FROM dbFolders INTO @folder

WHILE @@FETCH_STATUS = 0
BEGIN
	-- command to check if file exists
	SET @cmdshell = CONCAT('DIR /b ', @folder, '\t')
	INSERT @folderTest
	EXEC master.sys.xp_cmdshell @cmdshell
			
	-- if folder does not exist, add the MKDIR to create it
	IF NOT EXISTS (
		SELECT	1
		FROM	@folderTest
		WHERE	ISNULL(path, '') NOT IN ('The system cannot find the path specified.', 'File Not Found', ''))
	BEGIN
		SET @script = CONCAT('EXEC master.sys.xp_cmdshell ''MKDIR ', @folder, '''', CHAR(13), @script)
	END
			
	DELETE @folderTest
	FETCH NEXT FROM dbFolders INTO @folder
END

CLOSE dbFolders
DEALLOCATE dbFolders

The rest of the script remains somehow the same as the one on MSQLTips. Find the latest differential backup, get all transaction log backups since last differential backup and add them to the script. Lastly, RESTORE WITH RECOVERY:

SELECT @lastDiffBackup = MAX(backupFile)  
FROM @fileList  
WHERE backupType = 'DIFF' 
	AND REPLACE(backupFile, 'DIFF', '') > REPLACE(@lastFullBackup, 'FULL', '')

-- check to make sure there is a diff backup 
IF @lastDiffBackup IS NOT NULL 
BEGIN 
	SET @script += CONCAT('RESTORE DATABASE ', @newDbName  + ' FROM DISK = ''' + @lastDiffBackup + ''' WITH NORECOVERY'  + CHAR(13))
	SET @lastFullBackup = @lastDiffBackup 
END 

-- check for log backups 
SELECT @script += CONCAT('RESTORE LOG ', @newDbName, ' FROM DISK = ''', backupFile, ''' WITH NORECOVERY', CHAR(13))
FROM @fileList 
WHERE	backupType = 'LOG' 
	AND REPLACE(backupFile, 'LOG', '') > REPLACE(@lastFullBackup, 'DIFF', '')
ORDER BY REPLACE(backupFile, 'LOG', '')

-- put database in a useable state 
SET @script += CONCAT('RESTORE DATABASE ', @newDbName, ' WITH RECOVERY', CHAR(13))

I have put this into a stored procedure available to download below and here are a few uses:

1. When trying to restore a database with a different name:

DECLARE @restoreScript NVARCHAR(MAX)

EXEC [GetRestoreFromBackupScript] 
	@dbName = 'TestBackupRestores', 
	@newDbName = 'TestBackupRestores_Restore', 
	@BackupPath = 'D:\Backups', 
	@script = @restoreScript

PRINT @restoreScript

which produces:

EXEC master.sys.xp_cmdshell 'MKDIR D:\Databases\TestBackupRestores_Restore\Log'
EXEC master.sys.xp_cmdshell 'MKDIR D:\Databases\TestBackupRestores_Restore\Data'

RESTORE DATABASE TestBackupRestores_Restore FROM DISK = 'D:\Backups\SRV\TestBackupRestores\FULL\SRV_TestBackupRestores_FULL_20150506_001524.bak' 
    WITH MOVE 'TestBackupRestores' TO 'D:\Databases\TestBackupRestores_Restore\Data\TestBackupRestores_Restore.mdf', 
         MOVE 'TestBackupRestores_log' TO 'D:\Databases\TestBackupRestores_Restore\Log\TestBackupRestores_Restore_log.ldf', 
         NORECOVERY
RESTORE DATABASE TestBackupRestores_Restore FROM DISK = 'D:\Backups\SRV\TestBackupRestores\DIFF\SRV_TestBackupRestores_DIFF_20150506_001532.bak' WITH NORECOVERY
RESTORE LOG TestBackupRestores_Restore FROM DISK = 'D:\Backups\SRV\TestBackupRestores\LOG\SRV_TestBackupRestores_LOG_20150506_001534.trn' WITH NORECOVERY
RESTORE LOG TestBackupRestores_Restore FROM DISK = 'D:\Backups\SRV\TestBackupRestores\LOG\SRV_TestBackupRestores_LOG_20150506_001537.trn' WITH NORECOVERY
RESTORE DATABASE TestBackupRestores_Restore WITH RECOVERY

2. Trying to restore the database with the same name, replacing the current one:

DECLARE @restoreScript NVARCHAR(MAX)

EXEC [GetRestoreFromBackupScript] 
	@dbName = 'TestBackupRestores', 
	@newDbName = NULL, 
	@BackupPath = 'D:\Backups', 
	@script = @restoreScript

PRINT @restoreScript

which produces:

RESTORE DATABASE TestBackupRestores FROM DISK = 'D:\Backups\BARBIDUC\TestBackupRestores\FULL\BARBIDUC_TestBackupRestores_FULL_20150506_001524.bak' WITH NORECOVERY, REPLACE
RESTORE DATABASE TestBackupRestores FROM DISK = 'D:\Backups\BARBIDUC\TestBackupRestores\DIFF\BARBIDUC_TestBackupRestores_DIFF_20150506_001532.bak' WITH NORECOVERY
RESTORE LOG TestBackupRestores FROM DISK = 'D:\Backups\BARBIDUC\TestBackupRestores\LOG\BARBIDUC_TestBackupRestores_LOG_20150506_001534.trn' WITH NORECOVERY
RESTORE LOG TestBackupRestores FROM DISK = 'D:\Backups\BARBIDUC\TestBackupRestores\LOG\BARBIDUC_TestBackupRestores_LOG_20150506_001537.trn' WITH NORECOVERY
RESTORE DATABASE TestBackupRestores WITH RECOVERY

You can download the stored procedure here.
You can find here MSQLTips’s script and for Ola Hallengren’s backup scripts please visit his website.

This was quite a good challenge for me following Brent Ozar’s 6-Month DBA Training Plan – Automating Restores read and turned out to be quite handy.
I hope you enjoyed the long read.

Twitter

Linkedin

Facebook