You manage an instance of SQL Server 2008 that contains five databases. You perform database snapshots forall five databases on a daily basis.
A user reports that he accidentally deleted a table from the Mktgdb database.
You verify that a snapshot named Mktgdbsnapshot contains the deleted table. You want to revert the Mktgdb database to the Mktgdbsnapshot snapshot.
Which permission do you require to perform this task?
A.
the LOAD DATABASE permission
B.
the RESTORE DATABASE permission
C.
the ALTER DATABASE permission
D.
the CREATE DATABASE permission
Explanation:
You require the RESTORE DATABASE permission. A database snapshot is a special type of backup thatcaptures the state of data in a database at a point in time at which the snapshot creation was started. You canrevert to a snapshot backup when data in an online source database is damaged. You should use the following Transact-SQL statement to revert to a database snapshot: RESTORE DATABASE <database_name> FROM DATABASE_SNAPSHOT = <‘snapshot_name’>;
To perform a revert operation, you must have the RESTORE DATABASE permission on the source database. You do not need the LOAD DATABASE permission because this permission is no longer available in SQL Server 2008. This permission has been replaced with the RESTORE DATABASE permission. You do not need the ALTER DATABASE
or CREATE DATABASE permission because neither of thesepermissions is required to revert to a database snapshot. The ALTER DATABASE permission allows you tomodify a database, or modify the files and filegroups that are associated with the database. The CREATEDATABASE permission allows you to create a new database or database snapshot. This permission also allowsyou to attach a database using a set of existing detached files.Objective:
Maintaining a SQL Server DatabaseSub-Objective:
Manage database snapshots.References:
MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Development > Designing andImplementing Structured Storage (Database Engine) > Designing and Implementing Structured Storage Hot-toTopics > Databases How-to Topics > Database Snapshots How-to Topics > How to: Revert a Database to aDatabase Snapshot (Transact-SQL) MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Development > Designing andImplementing Structured Storage (Database Engine) > Databases > Database Snapshots > Reverting to aDatabase Snapshot