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. 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.
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.
4. Restore SQL Server Database Using SSMS
The SQL Server Management Studio (SSMS) is an awesome graphical tool for managing databases on SQL Server. Here are the useful screenshots of the database restoration in SQL Server with SQL Server Management Studio (SSMS). Under the General tab, selecting 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.
Conclusion
In this tutorial, you have learned to restore the database from a backup file in SQL Server.
