How to Restore Database in SQL Server?

Summary

This article mentions different methods to restore the database in SQL Server from the backup files (.bak files). You can RESTORE a database from a backup file using SQL Server Management Studio (SSMS) or execute the RESTORE DATABASE T-SQL script. Please continue to read this article to understand the step-by-step methods to follow to complete the restoration of the database on an instance of SQL Server.

How to Restore Full Database Backup in SQL Server?

This article explains the steps you need to follow to restore a Full Database Backup in SQL Server Using SQL Server Management Studio and by using T-SQL Script.

Permissions Required to Restore Database Backup in SQL Server?

To restore a database a user must be a member of DBCREATOR Server Role and DB_OWNER Database Role else you will receive the below mentioned error message while trying to restore a database in SQL Server.

Error Message

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

TSQL Script to Create Login with DBCREATOR Server Role and DB_OWNER Database Role

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

Database Administrator or a user who is a member of DBCREATOR Server Role and DB_OWNER Database Role will be able to restore a SQL Server database from databases full backup using SQL Server Management Studio, T-SQL Scripts or by using Powershell Commands.

Lets’s get started…

Restore a Full Database Backup Using SQL Server Management Studio

Step 1. Open SQL Server Management Studio and connect to the appropriate instance of Microsoft SQL Server Database Engine in Object Explorer.

Step 2. Right click Databases node and then select Restore Database… option from the drop down list as shown in the below snippet to open up Restore Database dialog box.

Step 3. In General Page of Restore Database dialog box, select or type the name of a new or existing database for your restore operation. In Source for restore specify the source and location of backup sets to restore. Choose From Device radio button and then click the “…” button to specify backup file location.

Click the below Number Buttons to continue reading the rest of the article.