Restore a Backup from a Device (SQL Server)
This topic describes how to restore a backup from a device in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL.
Note
Starting with SQL Server 2012 SP1 Cumulative Update 2, SQL Server backup to the Windows Azure Blob storage service is supported. For more information, see Backup and Restore Enhancements, and SQL Server Backup and Restore with Windows Azure Blob Storage Service.
In This Topic
Before you begin:
Security
To restore a backup from a device, using:
SQL Server Management Studio
Transact-SQL
Before You Begin
Security
Permissions
If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database (for the FROM DATABASE_SNAPSHOT option, the database always exists).
RESTORE permissions are given to roles in which membership information is always readily available to the server. Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.
.gif "Arrow icon used with Back to Top link")
[Top]
Using SQL Server Management Studio
To restore a backup from a device
After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.
Right-click the database, point to Tasks, and then click Restore.
Click the type of restore operation you want (Database, Files and Filegroups, or Transaction Log). This opens the corresponding restore dialog box.
On the General page, in the Restore source section, click From device.
Click the browse button for the From device text box, which opens the Specify Backup dialog box.
In the Backup media text box, select Backup Device, and click the Add button to open the Select Backup Device dialog box.
In the Backup device text box, select the device you want to use for the restore operation.
[Top]
Using Transact-SQL
To restore a backup from a device
Connect to the Database Engine.
From the Standard bar, click New Query.
In the RESTORE statement, specify a logical or physical backup device to use for the backup operation. This example restores from a disk file that has the physical name Z:\SQLServerBackups\AdventureWorks2012.bak.
RESTORE DATABASE AdventureWorks2012
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak' ;
[Top]
See Also
Tasks
Restore a Database Backup (SQL Server Management Studio)
Reference
RESTORE FILELISTONLY (Transact-SQL)
RESTORE HEADERONLY (Transact-SQL)
RESTORE LABELONLY (Transact-SQL)
RESTORE VERIFYONLY (Transact-SQL)
Concepts
Restore a Database Backup Under the Simple Recovery Model (Transact-SQL)
Restore a Differential Database Backup (SQL Server)
Restore a Database to a New Location (SQL Server)
Back Up Files and Filegroups (SQL Server)