How to Restore Database in SQL Server 2019, 2017, 2014?

This article describes various ways to restore database in SQL Server 2019, 2017, and 2014 versions. You can use SQL Server Management Studio (SSMS) to restore a database from a backup file or to run a T-SQL RESTORE DATABASE script. Read the complete article to repair SQL Server database.

Best Solution to Restore SQL Database

Don’t take risks with your potential data, use SQL Server Database Recovery Tool to recover and restore any type of SQL Server database corrupted, damaged with deleted objects, etc. Restore SQL Server 2019, 2017, and 2014 databases to healthy and live SQL Server databases.

Restore Database from SQL Server Backup

Before proceeding further, let’s know about the differences between each SQL Server backup type:

Full Database Backup

• As the name suggests, a full backup is a full backup of the entire contents of the database, usually a * .bak file.
• This backup can be restored on its own.
• It is the basis for retrieving transaction logs and differentiated backups.

Transaction Log Backup

• A backup of the database transaction log file from the last full backup or transaction log, usually a * .trn file.
• Restore after restoring a full backup.

Differential Backup

• A differential backup is a backup of data that has changed since the last full backup, usually a * .dif file.
• Restore after restoring a full backup.

To restore the database, the user must be a member of the DBCREATOR server role and the DB_OWNER database role, otherwise, you will receive the error message mentioned below when you try to restore the database in SQL Server 2019, 2017, 2014.

CREATE DATABASE permission denied in database 'master'.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 262)

TSQL Script to Restore SQL Database from Backup

Follow the command to restore a database from the last created backup point:

USE [master]
GO

DROP USER [BackupRestoreAdmin]
GO 

DROP LOGIN [BackupRestoreAdmin]
GO

CREATE LOGIN BackupRestoreAdmin WITH PASSWORD='$tr0ngP@$$w0rd'
GO
CREATE USER BackupRestoreAdmin FOR LOGIN BackupRestoreAdmin
GO
EXEC sp_addsrvrolemember 'BackupRestoreAdmin', 'dbcreator'
GO
EXEC sp_addrolemember 'db_owner','BackupRestoreAdmin'
GO

Then follow the below-mentioned commands:

RESTORE DATABASE [AdventureWorks] 
FROM  DISK = N'C:\DBBackups\AdventureWorks.BAK' 
WITH  FILE = 1,  
MOVE N'AdventureWorks_Data' TO N'D:\MSSQL\MSSQL10_50.SQL2008R2\MSSQL\DATA\AdventureWorks.mdf',  
MOVE N'AdventureWorks_Log' TO N'D:\MSSQL\MSSQL10_50.SQL2008R2\MSSQL\DATA\AdventureWorks.ldf',  
NOUNLOAD,  REPLACE,  STATS = 10
GO

Database administrators or users who are members of the DBCREATOR server role and the DB_OWNER database role will be able to restore SQL Server databases from full database backups using SQL Server Management Studio, T-SQL Scripts, or using Powershell Commands.

Use SQL Server Management Studio to Restore Database

Use the mentioned steps to restore the SQL database in SQL Server Management Studio:

Step 1. Open SQL Server Management Studio and log in to the appropriate instance of the Microsoft SQL Server database engine in Object Explorer

Step 2. Right-click the Databases node and select the Restore Database … option from the drop-down list to open the Database Restore dialog

Step 3. On the General page of the Restore Database dialog box, select or enter the name of a new or existing database for the restore operation. Specify the source and location of the backup set to restore in Restore Source. Select the From radio button on your device and click the “…” button to specify the location of the backup file

Step 4. In the Specify Backup dialog box, select File as the backup medium and click the Add button to select the location of the database backup file from which to restore the database. Click OK to return to the Database Restore dialog

Step 5. In the Restore Database dialog box, select the checkbox under Restore, and then select the Options page in the left pane

Step 6. On the Restore Database Options dialog box, select the check box next to Overwrite Existing Database (REPLACE), and then select the radio next to the Prepare Database for Use button by rewriting uncommitted transactions. Other transaction logs cannot be retrieved. (recovered by recovery). Finally, click OK to begin restoring the SQL Server database

Step 7. To generate a TSQL script for restoring the database, click Script and select the script action from the various options

Step 8. You will receive a pop-up message once the database has been successfully restored.

Summing Up

In this article, you saw the steps to restore a database from a full backup. Restore SQL Server 2019, 2017, and 2014 databases using the T-SQL RESTORE DATABASE script and SQL Server Management Studio (SSMS).