SQL DBA Blog

Restoring a database in SQL Server is a straightforward task, and one that SQL Database Administrators have to perform thousands of times throughout their career.

A database restore can be done via GUI (SSMS Wizard) or via command (TSQL/PowerShell). The method you choose to restore a database is usually driven by the number of databases you have to restore. If you have to restore more than 5 databases, perhaps it’s time to automate. If you are an experienced engineer you might write the restore command as second nature, always having it as a preference rather than restoring via the SSMS GUI Wizard.

One complexity when restoring databases is that your database backup media might be split into more than one file – this just means we need to add more paths during the restore.

In the demo below I am running a Full Backup of a database, dropping it, and restoring it back online. Following this, I do the same again but split the backup media into more than one file.

# Restore a Database via Command
# Restore a Database with Multiple Files

Restore a SQL Server Database via Command

I have 2 user databases on my local MSSQL instance here : [ThePetShop] & [lemonadestand]

To prep for this database restore demo, I’m going to run a Full Backup for the [lemonadestand] database to a local directory.

-- Perform full database backup to local temp directory with compression
BACKUP DATABASE [lemonadestand] TO DISK = N'c:\temp\lemonadestand_full_11082022.bak' WITH COMPRESSION;

Next, I’ll drop the database and refresh my SSMS Object Explorer.

We no longer have the [lemonadestand] database available to us.

To restore this database, back to the point in time I ran the backup command, we need to run the RESTORE DATABASE command. I run the command and refresh SSMS.

-- Restore database ms sql
RESTORE DATABASE [lemonadestand] FROM  DISK = N'C:\TEMP\lemonadestand_full_11082022.bak';

The database is now back online and ready to use again.

Restore a Database with Multiple Files

This time we are going to perform another Full Backup of the [lemonadestand] database, but this time splitting the backup media across 2 files instead of 1.

Backing up to multiple files is often used to improve performance for backups, and/or for managing available disk space on an MSSQL host.

-- Run Full database backup to local temp directory with compression
BACKUP DATABASE [lemonadestand] TO DISK = N'c:\temp\lemonadestand_full_11082022_part1.bak', DISK = N'c:\temp\lemonadestand_full_11082022_part2' WITH COMPRESSION;

-- Drop the lemonadestand database
DROP DATABASE [lemonadestand];

-- Restore the lemonadestand database
RESTORE DATABASE [lemonadestand] FROM  DISK = N'C:\TEMP\lemonadestand_full_11082022_part1.bak', DISK = N'c:\temp\lemonadestand_full_11082022_part2';

That’s it for this one. Have a look at the Restoring Databases Tag for more tips on this area – I hope to touch on points in time recovery more.