You are designing a database named DB1.
Changes will be deployed to DB1 every Wednesday night.
You need to recommend a strategy to deploy the changes to DB1. The strategy must meet
the following requirements:
• The strategy must not disrupt backup operations.
• DB1 must be online while the changes are deployed.
• You must be able to undo quickly any changes made to objects.
What should you recommend?More than one answer choice may achieve the goal. Select
the BEST answer.
A.
Perform a copy-only database backup before the changes are deployed. If the
deployment fails, restore the database to another server and recover the original objects
from the restored database.
B.
Create a database snapshot. If the deployment fails, recover the objects from the
database snapshot.
C.
Create a database snapshot. If the deployment fails, revert the database to the database
snapshot.
D.
Perform a full database backup before the changes are deployed. If the deployment fails,
restore the database to another server and recover the original objects from the restored
database.
Answer should be B, correct?
Yep definitely B: https://msdn.microsoft.com/en-us/library/ms189281.aspx
I think Also B is the right answet
B is correct, C not because to recover changes databases are offline.
B
http://www.sqlshack.com/sql-server-database-snapshots/
B
But the requirement said: “You must be able to undo quickly any changes made to objects”
Say we got 282 Changes to objects (Tables, stored procedures and so on).
Using recover from Objects will take hours to create a script which deletes or updates the specific updated objects to the original ones. There is no automatism to create such script.
Another statement is that the changes occur every wednesday. If these changes are diffrent every week, we need to create a new recoer script for every week.
And at last, the requirement is saying “DB1 must be online while the changes are deployed.”. It is not needed to be online during a recovery of the changes. Only the rollout should be online, which is fulfilled by a snapshot strategy.
So I go with C, because it works in any case, it works quick and does not need any custom scripts to get special objects
+1
One problem with option B is, if you just restore some objects instead of the entire database, there could be integrity issues.
Say we have Table A and Table B. The two tables have some columns referenced to each other (FK). Table A was changed by the deployment (adding a new column, etc.) but B was not. Then we decide to roll back the deployments, both tables might have been updated with new information. If we only recover A, the data in both tables will be not in sync then we will have integrity issue.
C.
C) Create a database snapshot. If the deployment fails, revert the database to the database snapshot.
— Create database snapshot TestDB_ss_20161230
Create Database TestDB_ss_20161230 On
( Name = TestDB,
Filename = ‘R:\SNAPSHOTS\TestDB_ss_20161230.ss’ )
As Snapshot Of TestDB;
Go
–Backup Log
Use master;
Backup Log TestDB To Disk = ‘R:\SQL_BACKUPS\TestDB_Log.trn’;
— Reverting TestDB to TestDB_snapshot_20161230
Use master;
Restore Database TestDB
From Database_Snapshot = ‘TestDB_ss_20161230’;
Go
— Backup reverted database
Use master;
Backup Database TestDB To Disk = ‘R:\SQL_BACKUPS\TestDB_Full.bak’;
I will go with C.