SQL Server Management Studio: Automatically Generating Script for Restoring a Backup File

I was developing an SSIS package and as part of the development process I would test and then restore a clearn version of the database, again and again and again using the Restore Database dialog. Now, I can figure out how to automate the restore process by writing a T=SQL script using RESTORE DATABASE but this is not necessary. The Restore Database dialog contains a Script button (see below) that will write the script for you:

The Script button creates a RESTORE DATABASE script corresponding to the options select on Restore Database dialog. By default clicking on the Script button create a RESTORE DATABASE script as a new query editor window in SQL Manager. Click on the down arrow to the right of the Script button displays the following:

It is possible to not only create a new query editor window but save the script to a file, save the script to the Clipboard or to create a SQL Agent Job that performs the restore.

An example of the RESTORE DATABASE created is as follows:

USE [master]

ALTER DATABASE [Buddy] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE [Buddy] FROM  DISK = N'C:\temp\Buddy.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5

ALTER DATABASE [Buddy] SET MULTI_USER

GO

It took two seconds to create the above script while writing it by handle would have meant a half hour to an hour of diddling.

The Restore Database dialog is displayed from within SQL Manager by right clicking on the database to restore (the database below is Buddy) and selecting Tasks | Restore | Database:

Selecting Tasks | Restore | Database display the Restore Database dialog:

The Restore Database dialog shown already has a backup file selected the will be restored. SQL Manager 2014 remembers the previous backup created and pre-populates the Restore Database dialog with the last backup created. The previous screenshot shows how the Script button added the file path to the RESTORE DATABASE command it generated (see below)

RESTORE DATABASE [Buddy] FROM  DISK = N'C:\temp\Buddy.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5

Selecting the Options tab from the Restore Database dialog allows the restore to be configured more precisely. With regards to the script generated the following options were checked:

• Overwrite the existing database (WITH REPLACE)
• Close existing connections to destination database

The Options tab of the Restore Database dialog for the previous options is as follows:

The REPLACE option is incorporated into the script as follows (demarcated in boldface):

RESTORE DATABASE [Buddy] FROM  DISK = N'C:\temp\Buddy.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5

The "Close existing connections to destination database" options is handled in the script as follows  (demarcated in boldface):

USE [master]

ALTER DATABASE [Buddy] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE [Buddy] FROM  DISK = N'C:\temp\Buddy.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5

ALTER DATABASE [Buddy] SET MULTI_USER

GO

Having used SQL Server since SQL Server 2000, it is pleasant to notice usability enhancements like remembering the last database backup in the Restore Database dialog and obviously, having the Script button available on the Restore Database dialog. A Script button is similarly available on the Backup Database dialog.