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.
Always restore with NORECOVERY
·
Currently Reading
administration AI Azure Backup/Recovery career career2 car update Cloud Computing coping database design databases Database Weekly DevOps disaster recovery encryption Friday Poll goals hardware Humor life Microsoft misc PASS powershell Redgate republish sabbatical security software development software development speaking SQL in the City SQLNewBlogger SQL Prompt SQL Saturday sql server syndicated T-SQL T-SQL Tuesday Tesla travel vacation windows words work
Voice of the DBA 
Comments
5 responses to “Always restore with NORECOVERY”
Why does RECOVERY make it harder to recover from an accidental restore?
LikeLike
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.
LikeLike
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.
LikeLike
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.
LikeLike
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.
LikeLike