Always restore with NORECOVERY

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 way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.

5 Responses to Always restore with NORECOVERY

  1. w4g3n3r says:

    Why does RECOVERY make it harder to recover from an accidental restore?

    • way0utwest says:

      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 says:

    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 says:

    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.