SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

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.

How to Restore Database In SQL Server

Recommendation: Read SQL Server Database Backup Tutorial with Examples to understand database backups in depth whether you are new to SQL Server or an Expert.

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.

How to Restore Database Using SQL Server Management Studio


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

Ashish Mehta

Ashish Kumar Mehta is a database manager, trainer and technical author. He has more than a decade of IT experience in database administration, performance tuning, database development and technical training on Microsoft SQL Server from SQL Server 2000 to SQL Server 2014. Ashish has authored more than 325 technical articles on SQL Server across leading SQL Server technology portals. Over the last few years, he has also developed and delivered many successful projects in database infrastructure; data warehouse and business intelligence; database migration; and upgrade projects for companies such as Hewlett-Packard, Microsoft, Cognizant and Centrica PLC, UK. He holds an engineering degree in computer science and industry standard certifications from Microsoft including MCITP Database Administrator 2005/2008, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Advertisement