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 SSMS.
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.
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 lemonadestand database RESTORE DATABASE [lemonadestand] FROM DISK = N'C:\TEMP\lemonadestand_full_11082022.bak';
The database is now back online and ready to use again.
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 a 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 point in time recovery more.