Restore database

Restoring an SQL database involves taking a backup of the database, and then restoring it into a SQL Server instance.

There are several methods to restore SQL database, including using graphical user interface (GUI) or Transact-SQL (T-SQL).

To restore [AdventureWorks2019] database, You will need to download and move "AdventureWorks2019.bak" file to default backup location of SQL instance. This SQL backup location varies depending on the installation location & version of SQL Server. For Instance, the default location for a default instance of SQL Server 2022 is as follows :

C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup

Here's an example of how to restore database using T-SQL :

1. Run below SQL commands in SSMS as given in below images :

RESTORE FILELISTONLY FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\AdventureWorks2019.bak'
        

USE [master]

RESTORE DATABASE [AdventureWorks2019] 
FROM  DISK = N'C:\Program Files\Microsoft SQL Server
\MSSQL16.MSSQLSERVER\MSSQL\Backup\AdventureWorks2019.bak' 
WITH  FILE = 1,  NOUNLOAD,  STATS = 5
        

That's it! You have successfully restored [AdventureWorks2019] database using T-SQL.

NOTE : You may have come across two different SQL database file types : "Raw Data" files and "Log" files. Raw data files are used to store the actual data of the database with ".mdf" file extension. While log files store a record of all changes made to the database with a ".ldf" file extension.

Here's a step-by-step guide on how to restore SQL database using GUI :

1. Open SQL Server Management Studio. In the "Connect to Server" window, select the Server type as "Database Engine" >> In the "Server name" field, enter the name of the server where SQL Server is installed >> In the "Authentication" field, as of now select "Windows Authentication" otherwise you can also select "SQL Server Authentication" and fill login name and password when/if any SQL user is created at the installation time or later >> Now click the "Connect" button to connect to the SQL Server instance.

2. You have successfully connected to SQL Server. Next, In the Object Explorer, right-click on the "Databases folder" >> Select "Restore Database".

3. In the "General" tab of the "Restore Database" window, select "Device" option >> Click on ellipsis ( three dots ) >> Click on the "OK" button.

4. Change Backup media type option as "File" >> Click the "Add" button to browse for the backup file.

5. Select the "AdventureWorks2019.bak" backup file or any other backup set that you want to restore from SQL Server backup location >> Click on the "OK" button.

Keep clicking on the "OK" button until "restored successful" message appears.

That's it! You have successfully restored [AdventureWorks2019] database using GUI.

NOTE : In the "Files" tab, You may have come across two different SQL database file types : "Raw Data" files and "Log" files. Raw data files are used to store the actual data of the database with ".mdf" file extension. While log files store a record of all changes made to the database with a ".ldf" file extension.

Frequently Asked Questions :

Monitor the restoration process using progress indicators provided by your database management system.

Restoring a database means recovering a database to a previous state, typically by importing a backup file to replace the existing data.

The command used to restore a database in SQL can vary depending on the database management system being used, such as "RESTORE DATABASE" for SQL Server.

Restore a SQL database by executing the appropriate restore command for your database system, ensuring compatibility with backup files and monitoring the process for completion.