Local backup and restore of a SQL database |

Contents

I personally do not like using the GUI screens in SQL Server Management Studio to perform BACKUP and RESTORE operations. There is a risk of selecting a wrong option or referencing the wrong file every time I use them.

As programmers, I believe we need to automate as much as possible. Repetitive actions should be scripted, not only to improve our efficiency, but also to reduce the risk of errors. The worst case scenario in this situation is that I restore a backup over the top of the live database.:

The live database has been selected as the destination of the restore. — Oh no, he’s picked the wrong database

Here’s my go-to SQL script for backing up and restoring a SQL database to local/on-premise database servers. Every time I execute this command, I do so with absolute confidence that it will do exactly as I intended.

DECLARE @fullPathToBackupFile AS varchar(500) = N'C:\Temp\YourDatabase.bak'

BACKUP DATABASE YourDatabase TO DISK=@fullPathToBackupFile WITH INIT

RESTORE FILELISTONLY FROM DISK = @fullPathToBackupFile

USE master

ALTER DATABASE YourDatabase_TEST SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE YourDatabase_TEST FROM DISK=@fullPathToBackupFile WITH REPLACE,
MOVE 'YourDatabase' TO 'C:\MSSQL\DATA\YourDatabase_TEST.mdf',
MOVE 'YourDatabase_log' TO 'C:\MSSQL\LOG\YourDatabase_TEST.ldf'
GO