SQL Server Restore


InterServer Web Hosting and VPS
Sony Playstation PS4 Pro Custom Build NVMe SSD Drive - Enclosure case and Crucial NVMe SSD
PS4 Pro Custom External SSD NVMe Drive

How I built a PS4 Pro Custom External SSD NVMe Drive...

NUC8i7BEH with memory
Intel NUC8I7BEH Review and Custom Build

This is my review of the Intel NUC8i7BEH and a summary of my build...

Card image cap
Asus M5A88-M EVO Review

This is my review of the Asus M5A88-M EVO motherboard...

Corsair Vengeance Blue 8GB (2x4GB) DDR3 PC3-12800C9 1600MHz Dual Channel Kit
Corsair Vengeance Blue Review

This is my review of the Corsair Vengeance Blue 8GB (2x4GB) DDR3 PC3-12800C9 1600MHz Dual Channel Kit memory...

Robotic Arm with USB PC Interface Review
Robotic Arm with USB PC Interface Review

This is my review of the Robotic Arm with USB PC Interface...

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'

Sponsored Links
SolarSystem - AngularJS Demo
SolarSystem - Angular JS Demo

Check out my first Angular JS demo hosted in Azure, it helped win my first Angular contract. Put the sound on.