How to Restore Database in SQL Server - Steps to Restore a Full Database Back Up


Feb 26, 2012
Page: 1/3

Introduction

In this article we will take a look at the step you need to follow to restore a SQL Server Database from a Full Database Backup. The steps mentioned in this article are applicable for SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012. If you are interested in knowing the steps you need to follow to back up a database then read the following article titled “How to Back up Database in SQL Server”.

"This article has a total of 3 pages including this page. Click the links below to open all 3 pages in a seperate window to learn How to Restore a Full Database Back up in SQL Server. Page 1, Page 2, Page 3"

Permissions Required to Restore Database Backup in SQL Server

In order to restore 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)

Sample 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




A 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 back up using either :-

Restore Database Using SQL Server Management Studio
Restore Database Using TSQL Scripts

Let us take a look at both the above mentioned options to restore a SQL Server Database from a Full Database Backup in detail.

SQL Server Database Backup Options and Commands a Step by Step Tutorial with Examples

This tutorial will give you compressive information on different database backup options which is available to a DBA or a Developer within SQL Server. The detailed topics mentioned in this tutorial will help you understand database backups in depth whether you are new to SQL Server or an Expert.  Continue Reading...

Restore a Full Database Backup Using SQL Server Management Studio

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

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 - Steps to Restore a Full Database Back Up

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.

Restore Database Dialog Box in SQL Server Management Studio

Click on Next Page to continue reading rest of the article…

Next Page..

Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article




Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."