You are the database administrator for your company. You are administering two SQL Server databases, Sales1 and Sales2 , on your company’s server. According to changes in your company policy, you should create and maintain snapshots of the Sales1 databaseto provide a backup and recovery solution for the database.
Which restrictions apply while creating a snapshot of the Sales1 database?
A.
All the filegroups in the Sales1 database must be online to create a snapshot.
B.
The snapshot must be placed on the same SQL Server instance as the Sales1 database.
C.
You must delete the earlier snapshots of the Sales1 database before creating a new snapshot.
D.
A snapshot of the model database must be created along with the snapshot of the Sales1 database.
Explanation:
The snapshot must be placed on the same SQL Server instance as the Sales1 database. A snapshot is a read-only copy of a database at a specific point in time. Any change made to the database after this point in time is notavailable in the snapshot. A snapshot is always placed on the SQL Server instance on which the SQL Serverdatabase resides. Snapshots are primarily used for reporting purposes and as test databases. They can also beused to recover the database from an unwanted change. They provide a copy of a database using minimal diskspace. The option stating that all the filegroups in the Sales1 database must be online to create a snapshot is incorrect. Itis not necessary to keep all the filegroups online while creating a snapshot of a database. A snapshot can becreated if some filegroups in the database are offline. The offline filegroups will not generate sparse files and willremain offline in the database snapshot. The option stating that you must delete the earlier snapshots of the Sales1 database before creating a newsnapshot is incorrect. You can create multiple snapshots of a database, but the names of two snapshots of adatabase cannot be identical. The database snapshots should be named so that you can easily identify the pointin time at which the snapshot was created. The option stating that a snapshot of the model database must be created along with a snapshot of the Sales1 database is incorrect. You are not required to create a snapshot of the model database along with the snapshotof the Sales1 database. Creating a snapshot of the
model , master , or tempdb database is not allowed.Objective:
Maintaining a SQL Server DatabaseSub-Objective:
Manage database snapshots.References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Development > Designing andImplementing Structured Storage (Database Engine) > Databases > Database Snapshots TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Development > Designing andImplementing Structured Storage (Database Engine) > Databases > Database Snapshots > How DatabaseSnapshots Work