Backup/Recovery, disaster recovery, sql server, syndicated
Always, always, ALWAYS restore a SQL Server database with the NORECOVERY option.
It’s trivial to switch the database online.
Not trivial to recover from an accidental restore with RECOVERY (the default)
From → Blog
Why does RECOVERY make it harder to recover from an accidental restore?
Because if you make a mistake, you can’t go back. If you have logs to restore, a tail log, a diff, it’s easy to make a mistake. There’s no more effort to add RECOVERY. It’s just a habit you get into. If you always restore with NORECOVERY, then you never make a mistake on a large restore. The bottom line is there’s no penalty for using NORECOVERY, but potentially a big penalty for using nothing or RECOVERY.
An excellent tip! I’m an Oracle DBA with 15 years experience who by default has become the database (per se) lead for all technologies at our site. I just checked out Steve’s tip against BOL and realise that this is effectively what I have been doing with Oracle since forever. (RESTORE then RECOVER). It puts you, the DBA, with your brain and your knowledge in control.
Good tip Steve. Simply knowing the difference makes one an informed DBA. Once you make the choice, it is in your hands but understanding the difference is what makes for a better DBA.
If you get time, I’d love it if you would walk through an example of an “oops!” moment, showing the impact of using RECOVERY vs. NORECOVERY.
Comments are closed.
Blog at WordPress.com. | The Titan Theme.
Get every new post delivered to your Inbox.
Join 5,376 other followers