Restoring With Standby

Kenneth Fisher describes the WITH STANDBY option for database restorations:

It then leaves the database in a read-only state. So now you can restore the database back to a point in time (even mid log backup). Check the data. Restore a few minutes into the future. Check the data again. Over and over again until you are where you need to be. It’s still going to be tedious but better than doing the full restore over again each time you need to check, right?

On top of that we can use the same idea and combine it with log shipping. Now you not only have a spare in case of a DR situation but that spare can be read only most of the time (except when actually restoring). You can use it to run reports, ad-hoc queries, etc. (Don’t use it for CHECKDBs.)

Those are a few good uses of the WITH STANDBY option.

Related Posts

Testing dbcreator Privileges

Shane O’Neill wants to know whether the dbcreator built-in role has the ability to back up the databases it creates: I knew from reading the documentation that dbcreator grants permissions to create, alter, drop, and restore databases. My question was does it give permission to backup databases? It seems to give everything else so is backup databases just missing there? […]

Read More

TDE + Copy-Only Backups

Jovan Popvic shows how you can take a copy-only backup of a TDE-protected database in Azure SQL Managed Instance: We are recommending to rely on automatic backups only, with the build-in restore functionality to restore a database from a point-in-time, restore a database to another instance (for instance from production to dev)or Geo-restore functionalities to […]

Read More

Categories

July 2016
MTWTFSS
« Jun Aug »
 123
45678910
11121314151617
18192021222324
25262728293031