A dynamic script to restore SQL Server database from a stored procedure

Create a centralised, generic and re-useable stored procedure using dynamic SQL script that can restore databases using a number of parameters as part of a wider maintenance plan

Process

The process of restoring a database can be scripted from SSMS. When this is saved into a Stored Procedure, it can be called from other processed or servers, and is particularly useful as part of a joined up maintenance plan.

Below we've created two scripts.

With the simple script use the code generated from SSMS and replace the content of the Stored Procedure.

With the generic script, single databases could be restored using variables, meaning you could re-use it.

Simple Script

USE [utilities]GOCREATE PROC [maint].RestoreDatabase_{dbname} AS BEGINRESTORE DATABASE [dbname] FROM DISK=N'c:\backupfolder\{dbname}.bak' WITH FILE= 1,MOVE N'{dbname}' TO N'd:\database\{dbname}.mdf',MOVE N'{dbname}_log' TO N'e:\database\{dbname}.ldf',NOUNLOAD, REPLACE, STATS= 10,STANDBY=N'e:\database\ROLLBACK_UNDO_{dbname}.bak'ENDGO

Re-usable script

This script uses a number of parameters to enable it to be called from elsewhere, so can be re-used and called cross server if necessary.

@dbname - Database name that you want called
@Directory_Bak - Folder where the backups are stored
@Directory_Dat - It's best to keep log files and database files on different disks, so this is the directory you want to store it on
@Directory_Log - As above, use a separate directory for your log file
@Directory_Stand - This was developed with log shipping in mind, so this can be used to do a restore from your main database to the reporting database

SQL

USE [utilities]GOCREATE PROC [maint].RestoreDatabase(@dbname NVARCHAR(100),@Directory_Bak NVARCHAR(100),@Directory_Dat NVARCHAR(100),@Directory_Log NVARCHAR(100),@Directory_Stand BIT) AS BEGINDECLARE @SQL NVARCHAR(MAX)='RESTORE DATABASE ['+@dbname+'] FROM DISK=N'''+@Directory_Bak+''+@dbname+'.bak'' WITH FILE= 1,MOVE N'''+@dbname+''' TO N'''+@Directory_Dat+@dbname+'.mdf'',MOVE N'''+@dbname+'_log'' TO N'''+@Directory_Log+@dbname+'.ldf'',NOUNLOAD, REPLACE, STATS=10'--Standby CodeIF ISNULL(@Directory_Bak,'')<>''SET @SQL=@SQL+',STANDBY=N'''+@Directory_Stand+'ROLLBACK_UNDO_'+@dbname+'.bak'''EXEC master..sp_executesql @SQLENDGO