DRAG DROP
You are the database administrator of a SQL Server 2012 data warehouse implemented as
a single database on a production server. The database is constantly updated by using SQL
Server Integration Services (SSIS) packages and SQL Server Analysis Services (SSAS)
cube writeback operations.
The database uses the full recovery model. A backup strategy has been implemented to
minimize data loss in the event of hardware failure.
SQL Server Agent jobs have been configured to implement the following backup operations:
A full database backup every day at 12:00 A.M.
Differential database backups every day at 6:00 A.M., 12:00 P.M., and 6:00 P.M.
Transaction log backups every hour on the hour.
At 2:38 P.M. a SSIS package corrupts the data in a fact table. The corruption cannot be
undone. You are notified at 3:15 P.M. You immediately take the database offline to prevent
further data access and modification.
You need to restore the data warehouse and minimize downtime and data loss.
Which four actions should you perform in sequence? (To answer, move the appropriate
actions from the list of actions to the answer area and arrange them in the correct order.
Answer: See the explanation
Explanation:
Box 1:Box 2:
Box 3:
Box 4:
Note:
* (box 1)
/ For a database using the full or bulk-logged recovery model, in most cases you must back
up the tail of the log before restoring the database. Restoring a database without first
backing up the tail of the log results in an error, unless the RESTORE DATABASE statement
contains either the WITH REPLACE or the WITH STOPAT clause, which must specify a time
or transaction that occurred after the end of the data backup.
/ If the database is online and you plan to perform a restore operation on the database,
before starting the restore operation, back up the tail of the log using WITH NORECOVERY:
BACKUP LOG database_name TO <backup_device> WITH NORECOVERY* To restore a database to a specific point in time or transaction, specify the target recovery
point in a STOPAT, STOPATMARK, or STOPBEFOREMARK clause.
* (incorrect, box 4): The STOPBEFOREMARK and STOPATMARK options have two
parameters, mark_name and lsn_number. The mark_name parameter, which identifies a
transaction mark in a log backup, is supported only in RESTORE LOG statements.
The lsn_number parameter, which specifies a log sequence number, is supported in both
RESTORE DATABASE statements and RESTORE LOG statements.
