How To Restore SQL Server Database

Microsoft SQL Server (MSSQL) is a relational database management system used on Windows servers. The latest versions are also available for the Linux platform. It’s a good practice to backup databases regularly, especially in production environments. So in case of any failure, we can restore a database from backup files. The SQL server provides options to back up and restore the full database or transactions logs.

In this how-to guide, we will learn to restore the SQL Server database using T-SQL statements and SQL Server Management Studio.

How to Restore SQL Server Database

We can restore a SQL Server database from a backup file either using the SQL queries or SQL Server Management Studio (SSMS). Use one of the below options to restore a SQL Server database from a backup file.

1. Restore SQL Database with T-SQL

Use the RESTORE DATABASE query to restore a SQL server databse from a backup file.

For example, You have a database backup file created with BACKUP commant at C:\backups\Test_db.bak . Then execute the following T-SQL statement to restore backup Test_db database from file.

RESTORE DATABASE [Test_db]
FROM DISK = 'D:\backups\Test_db.bak';

In most cases above command failed to restore the database and you need to go with the next query.

2. Restore SQL Database (WITH REPLACE)

Include the WITH REPLACE option to overwrite any existing data. The WITH REPLACE tells the SQL Server to discard any active contents in the transaction log and complete the restore.

RESTORE DATABASE [Test_db]
FROM DISK = 'D:\backups\Test_db.bak'
WITH REPLACE;

3. Restore SQL Database (WITH MOVE)

It might be the destination server database has placed files in a different location than the origin backup server. In that case, you need to define MDF and LDF file locations.

First identity the logical name of both files of the database. To find the logical name, right-click on the database, click properties and select the Files tab. Here you can find the logical names.

Use the below query with the correct logical names, file locations, and backup files.

RESTORE DATABASE [Test_db]
FROM DISK = 'D:\backups\Test_db.bak'
WITH REPLACE,
MOVE 'Test_db' TO 'D:\MSSQL\Data\Test_db.mdf',
MOVE 'Test_db_log' TO 'D:\MSSQL\Log\Test_db_log.ldf';

4. Restore SQL Server Database (Using SSMS)

The SQL Server Management Studio (SSMS) is an awesome graphical tool for managing databases on SQL Server.

1. Right-click on database >> Tasks >> Restore >> Database
2. Select Device and click on three dots (…) in front of that
3. Select the backup file and click Ok
4. Go to the Files tab
5. If the location of the files is different from the source. Select checkbox “Relocate all files to folder”
6. Select the MDF and LDF files directory, This will update the path of the file as well
7. Now, go to the Options tab
8. Select checkbox Overwrite the existing database (WITH REPLACE)
9. Uncheck the box Take a tail-log backup before restoring
10. Click OK to complete database restore in SQL server

Here are the useful screenshots of the database restoration in SQL Server with SQL Server Management Studio (SSMS).

Under the General tab, select a database backup file to restore.

Under the files tab, If required, select the relocate check box and enter MDF and LDF folder.

In the Options tab, select the WITH replace option. Also, uncheck the tail-log checkbox.

Finally, completed the database restoration.