Skip to content

Always restore with NORECOVERY

May 24, 2012

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)

‘nuff said.

About these ads

From → Blog

5 Comments
  1. w4g3n3r permalink

    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.

  2. John Lancashire permalink

    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.

  3. 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.

  4. dream permalink

    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.

Follow

Get every new post delivered to your Inbox.

Join 4,805 other followers

%d bloggers like this: