You are the database administrator of your company. The network contains an instance of SQL Server 2008. Youare implementing a backup strategy for a database named Empdb stored on the SQL server. You want to be ableto instantly create a copy of the Empdb database to create reports.
Which type of backup should you create?
A.
a copy-only backup
B.
a full backup
C.
a snapshot backup
D.
a differential backup
Explanation:
You should create a snapshot backup of the Empdb database. A snapshot backup is a special type of backupthat is created instantaneously without affecting the server. Snapshot backups use a split-mirror provided by anindependent software and hardware vendor, which prevents the use of SQL Server resources to create thebackup. You should use the following syntax to create a snapshot backup:
CREATE DATABASE database_snapshot_name ON ( NAME = logical_file_name , FILENAME = ‘os_file_name’ ) [ ,…n ] AS SNAPSHOT OF source_database_name ;
Snapshot backups are beneficial because they allow you to perform a restore operation as quickly as thesnapshot backup is created. A snapshot backup cannot be used to perform an online restore because SQLServer does not support this. When you restore a snapshot backup, the database is automatically taken offline. Asnapshot backup can be included in a piecemeal restore. A piecemeal restore is a process that allows you torestore and recover a database with multiple filegroups in stages. You should not create a copy-only backup of the Empdb database because copy-only backups are not createdas quickly as snapshot backups. A copy-only backup is a special type of backup that is taken outside of yourconventional SQL Server backup processing. The following syntax is used to create a copy-only backup:
BACKUP DATABASE <database_name> TO <backup_device>… WITH COPY_ONLY;
You should not create a full backup of the Empdb database because full backups are not created as quickly assnapshot backups. A full backup contains an entire copy of the specified database. A full backup takes aconsiderable amount of time to complete depending on the amount of data stored in the database. You should not create a differential backup of the Empdb database. A differential backup contains only data thathas changed since the last full database backup. Differential backups are smaller and can be taken more quicklythan full database backups, but they are not created as quickly as snapshot backups.Objective:
Maintaining a SQL Server DatabaseSub-Objective:
Restore databases.References:
MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration > BackingUp and Restoring Databases in SQL Server > Backup and Restore in Large Mission-Critical Environments >Snapshot Backups