SQL Server Restore

SQL Server Restore

Here we look at what the SQL Server Restore command in Microsoft's SQL Server.

Although you can restore using Sql Server Management Studio (SSMS), sometimes I find it easier and quicker to have some handy scripts around for that process. You can restore multiple databases in 1 script, it saves clicking around the Restore user interface in SSMS.

Restore

Here is a Restore script which will close your current connections by setting the database to Single mode, then perform the restore, then put the database back into Multi user mode so it's working as normal again:

-- Restore MyDatabase
ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK AFTER 1

RESTORE DATABASE MyDatabase FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\MyDatabase.bak' WITH REPLACE

ALTER DATABASE MyDatabase SET MULTI_USER

Restore a different database's backup

I can find myself having multiple versions of the same database, each with slightly different names, so each of those has different sets of data filenames too. One problem which can occur when trying to restore a backup from a differently named database, is that it also expects to be restored to the same data filenames that it was backed up from. So this script here fixes that situation my specifying we will use a new filename for the data file during the restore process:

-- Restore MyDatabase from the backup of DEV-MyDatabase but use these mdf and ldf filenames
ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK AFTER 1

RESTORE DATABASE MyDatabase FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\DEV-MyDatabase.bak' WITH REPLACE,

MOVE 'DEV-MyDatabase_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\MyDatabase.mdf',

MOVE 'DEV-MyDatabase_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\MyDatabase.ldf'

ALTER DATABASE MyDatabase SET MULTI_USER

Examine a backup file to the backup's logical names

To restore from a different database you need to know the logical names for the data and log. In order to get this information you can run the following on the .bak file to retrieve the names:

-- Get the logical names from the back up file
RESTORE FILELISTONLY FROM DISK= 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\DEV-MyDatabase.bak'