Restoring a database in SQL Server is an essential task for Database Administrators (DBA’s). We need to be able to quickly restore databases as part of Disaster Recovery (DR), as well as for configuring SQL features such as Always On or Database Mirroring.
You can restore databases using the SQL Server Management Studio (SSMS) GUI or by executing SQL commands. Personally, I often use the SSMS Restore Database Wizard to generate a script, which I then run in a query window. This approach is especially helpful when dealing with databases that have multiple data files spread across different drives.
Topics Covered:
> Restoring a SQL Server Database using Command
> Restoring a SQL Server Database with Multiple Files
> Verifying Database Restores
> Further Reading
Restoring a SQL Server Database using Command
For this demo, first I’m going to perform a Full Database Backup on my test database.
-- Perform full database backup to local temp directory with compression BACKUP DATABASE [lemonadestand] TO DISK = N'c:\temp\lemonadestand_full_11082022.bak' WITH COMPRESSION;
This creates a compressed backup file in C:\temp.
We’re going to simulate the need for a restore by dropping the database.
-- Drop the test database DROP DATABASE [lemonadestand];
Now that the database is removed, we can proceed with the restore.
Restore Database Command:
-- Restore test database we just dropped RESTORE DATABASE [lemonadestand] FROM DISK = N'C:\TEMP\lemonadestand_full_11082022.bak';
After executing the restore command, my test database becomes fully operational again.
Restoring a SQL Server Database with Multiple Files
In cases where backup performance needs to be optimized or disk space needs to be managed efficiently, database backups can be split across multiple files. Here’s how to restore a database from multiple backup files.
Restore Database from Multiple Files:
-- 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';
By specifying multiple backup files, SQL Server will automatically read from all files and restore the database accordingly.
Verifying Database Restores
Once the restore process completes, we can verify that the database is online and accessible simply by refreshing the databases in SSMS Object Explorer:
Alternatively you can check the status of databases in SQL Server by running the following query:
-- check database states SELECT name, state_desc, * FROM sys.databases WHERE name = 'lemonadestand';
While reviewing this table we can check the database state as well as configuration settings are set correctly, such as the database owner.
Further Reading
I have more blog posts relating to restoring databases in SQL Server, including the following:
SQL Script: Get Last Database Restore Time
Check when a Database was last restored using the SQL Script provided in this post.
SQL Script: Get Estimated Database Restore Time
A SQL Script to give an estimate for how long a db restore will take, useful for restoring large databases that take hours or days to complete.
Why is the Database in “In Recovery” Mode?
Helping you resolve issues if your SQL Server Database is stuck “in Recovery” Mode.
Database Cannot be Opened as it’s in the middle of a Restore
Again, helping with another common error you might encounter while restoring databases in SQL Server.
I hope this guide was a useful one for you! For more information on this, checkout the Microsoft Docs on Restoring Databases in SQL Server.
Leave a Reply