nerdexam
Microsoft

70-465 · Question #91

You work as a Database Administrator (DBA) for a company named ABC.com. The company uses a Microsoft SQL Server 2012 infrastructure. You have a database named CorpDB. A full backup of CorpDB is…

The correct answer is C. Take a snapshot of the database before deploying the changes. F. Revert the database to the snapshot. This question tests knowledge of SQL Server database snapshots as a rapid rollback mechanism during deployments while preserving the existing backup chain.

Submitted by kev92· Mar 5, 2026Design and implement database solutions for SQL Server

Question

You work as a Database Administrator (DBA) for a company named ABC.com. The company uses a Microsoft SQL Server 2012 infrastructure. You have a database named CorpDB. A full backup of CorpDB is taken every week. A differential backup of CorpDB is taken every night at midnight. A transaction log backup of CorpDB is taken at 8am, 12pm, 4pm and 8pm. You plan to deploy some changes to CorpDB at after the 4pm log backup completes. You need to ensure that you can undo the changes quickly if the deployment fails. During the deployment of the changes, no other changes must be made by users to the database. You need a backup and recovery strategy for deploying the changes. Which two of the following actions would meet the backup and recovery requirements whilst ensuring the regular backup schedule is not disrupted? (Choose two).

Options

  • ATake a full backup of the database before deploying the changes.
  • BTake a copy-only backup of the database before deploying the changes.
  • CTake a snapshot of the database before deploying the changes.
  • DRestore the database from the backup.
  • ERestore the snapshot to another server and recover the required objects from the snapshot.
  • FRevert the database to the snapshot.

How the community answered

(52 responses)
  • A
    2% (1)
  • B
    2% (1)
  • C
    79% (41)
  • D
    12% (6)
  • E
    6% (3)

Why each option

This question tests knowledge of SQL Server database snapshots as a rapid rollback mechanism during deployments while preserving the existing backup chain.

ATake a full backup of the database before deploying the changes.

Taking a full backup before deployment would reset the differential base, causing the next scheduled differential backup to capture all changes since this new full backup rather than the weekly full backup, thereby disrupting the regular backup schedule.

BTake a copy-only backup of the database before deploying the changes.

While a copy-only backup avoids disrupting the differential backup chain, restoring from it would still require a lengthy restore process involving the copy-only full backup plus subsequent log backups, making it much slower than reverting a snapshot.

CTake a snapshot of the database before deploying the changes.Correct

A database snapshot captures the state of the database at a specific point in time using sparse files and copy-on-write technology, without disrupting the existing backup schedule since it is not a traditional backup and does not affect the LSN chain or differential base. It also prevents user changes during the deployment window because the snapshot serves as a read-only reference point while the source database can be restricted.

DRestore the database from the backup.

Restoring the database from a standard backup (weekly full + nightly differential + log backups) would be time-consuming and complex, requiring multiple restore steps, which does not meet the requirement of undoing changes 'quickly'.

ERestore the snapshot to another server and recover the required objects from the snapshot.

Restoring a snapshot to another server to recover objects is a complex, time-consuming process involving object-level recovery, and does not provide the fast, simple rollback of the entire database that reverting the snapshot directly achieves.

FRevert the database to the snapshot.Correct

Reverting a database to a snapshot using the RESTORE DATABASE ... FROM DATABASE_SNAPSHOT statement is near-instantaneous compared to a full restore, allowing the DBA to quickly undo failed deployment changes by returning the database to its pre-deployment state without needing to replay transaction logs or restore multiple backup sets.

Concept tested: SQL Server database snapshots for deployment rollback

Source: https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-snapshots-sql-server

Topics

#Database snapshots#Deployment rollback#Point-in-time recovery#SQL Server backups

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-465 Practice