Facebook Twitter Instagram
    TecAdmin
    • Home
    • FeedBack
    • Submit Article
    • About Us
    Facebook Twitter Instagram
    TecAdmin
    You are at:Home»Databases»SQL Server»How To Restore SQL Server Database

    How To Restore SQL Server Database

    By RahulJuly 12, 20213 Mins Read

    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.

    Advertisement

    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;
    

    Restore Database in SQL Server with Query

    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';
    

    Restore Database in SQL Server with Query 2

    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 backup file and click Ok
    4. Go to Files tab
    5. If the files location is differnt than source. Select checkbox “Relocate all files to folder”
    6. Select the MDF and LDF files directory, This will update files path as well
    7. Now, go to Options tab
    8. Select checkbox Overwrite the existing database (WITH REPLACE)
    9. Uncheck the box Take tail-log backup before restore
    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, selecting a database backup file to restore.

    Restore Database in SQL Server

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

    Restore Database in SQL Server

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

    Restore Database in SQL Server with SSMS

    Finally, completed the database restoration.

    Restore Database in SQL Server with SSMS

    Conclusion

    In this tutorial, you have learned to restore the database from a backup file in SQL Server.

    database SQL SQL SErver
    Share. Facebook Twitter Pinterest LinkedIn Tumblr Email WhatsApp

    Related Posts

    PowerShell Script to Backup SQL Server Databases

    10 MySQL Performance Tuning Tips for Faster Database Queries

    How to Install PostGIS on Ubuntu 22.04 & 20.04

    Add A Comment

    Leave A Reply Cancel Reply

    Advertisement
    Recent Posts
    • Setting Up Angular on Ubuntu: Step-by-Step Guide
    • Converting UTC Date and Time to Local Time in Linux
    • Git Restore: Functionality and Practical Examples
    • Git Switch: Functionality and Practical Examples
    • Git Switch vs. Checkout: A Detailed Comparison with Examples
    Facebook Twitter Instagram Pinterest
    © 2023 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy

    Type above and press Enter to search. Press Esc to cancel.