Restoring databases in SQL Server is a routine task and is the responsibility of Database Administrators. The nature of a DBA job often means experiencing periods of calm with sudden spikes in demand, such as needing to restore numerous databases at once, or in a better scenario you are proactively migrating numerous databases to a new hardware. This variability defines the life of a DBA – no two weeks are identical, with unexpected incidents and tasks frequently arising. During quieter moments, it’s essential to capitalize on the opportunity by prioritizing proactive development and enhancing monitoring tasks. These periods of respite are rare and should be utilized effectively to bolster the overall efficiency and reliability of the database environment.
Anyhow, this post contains a guide on how to restore a SQL Server database via command and SSMS GUI. The script method may be best if you need to restore multiple databases or if you are performing the task more than once.
Restoring a Database in SQL Server 2022
Below includes a guide on how to restore a database using SQL command, and restoring a database with SQL Server Management Studio (SSMS).
Restore Database with SQL Command
The command to restore a database in SQL Server is straight-forward and easy to memorise. RESTORE DATABASE requires us to specify the database name, location the database currently resides on disk, and we can add options after WITH in the command.
If copying the SQL below, be sure to replace the databaseName and to the location on disk for your SQL backup file.
USE master; GO -- Restore the Jupiter database RESTORE DATABASE [Jupiter] FROM DISK = N'D:\Development\SQL Database Backups\Jupiter_FullBackup.bak';
If you are restoring a large database, remember to measure disk space requirements and ensure there’s going to be enough space. Also, large db restores may take a long time time to complete, if you’re waiting on a backup or restore to complete we can use a SQL Script to get the estimated database restore time.
Restore Database in Management Studio
To restore a SQL Server database using the SQL Server Management Studio (SSMS) UI:
1. Connect to SQL Server, Right-Click Databases in Object Explorer and select Restore Database.
2. In the prompted Restore Database SSMS window, click Device as the Source, click the three dots to change the directory and point this database restore to the location of your SQL Server backup file(s). You can select multiple files from here including Full, Diffs & TLog backup files and they’ll all line up for restore.
You should be able to click OK and continue with the database restore from here. But before we do this, I’ll show an area we always check as DBAs to verify where the database data and log files will be stored on the disk. Click the files tab to view and relocate the near-live SQL database files.
We can also click the top menu button ‘Script’ to output the TSQL behind this restore.
SQL Script: Get Last Database Restore Dates and Times
Here’s a bonus tip: a SQL script to retrieve the last database restore dates and times. This information can be for tracking database restoration activities, and it retrieves this info from the m s d b system database restorehistory table.
Hope this article was useful for you!
Leave a Reply