Introduction
In this article we will take a look at the steps 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.
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 Error: 262)
Sample T-SQL Script to create login with DBCREATOR Server Role and DB_OWNER Database Role
USE [master]
GO
GO
DROP USER [BackupRestoreAdmin]
GO
GO
DROP LOGIN [BackupRestoreAdmin]
GO
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 DBA 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 either :-GO
CREATE USER BackupRestoreAdmin FOR LOGIN BackupRestoreAdmin
GO
EXEC sp_addsrvrolemember 'BackupRestoreAdmin', 'dbcreator'
GO
EXEC sp_addrolemember 'db_owner','BackupRestoreAdmin'
GO
- Restore database using SSMS
- Restore database using TSQL scripts
Restore a full database backup using SSMS
Open SSMS and connect ot the appropriate instance of MSSQL Server Database Engine in Object Explorer.
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.
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.
In Specify Backup dialog box choose File as Backup Media and then click the Add button to choose the location of database backup file from which you want to restore the database as shown in the below snippet. Click OK to return to Restore Database dialog box.
In Restore Database Dailog box select the checkbox under the Restore as shown in the below snippet and then select Option Page from the left pane.
In Options Page of Restore Database dialog box select the checkbox next to Overwrite the existing database (WITH REPLACE) and choose the radio button next to Leave the database ready to use by rolling back uncommited transactions. Additional transactional logs cannot be restored.(RESTORE WITH RECOVERY) as shown in the below snippet. Finally click OK to start restoring the SQL Server Database.
To Generate TSQL Script for the database restore click Scripts and choose Script Action to your choice from the different options which are available as shown in the snippet below
Once the database is successfully restored you will get a popup message similar to the one shown in the below snippet.
How to restore a Full Database Backup using TSQL Scripts
The below script can be used to restore AdventureWorks database.
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
Conclusion
In this article you have seen the steps to restore a database from its full backups.
