{"id":3300,"date":"2011-05-04T05:00:00","date_gmt":"2011-05-04T05:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/emergency-database-restores\/"},"modified":"2016-07-28T10:50:24","modified_gmt":"2016-07-28T10:50:24","slug":"emergency-database-restores","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/emergency-database-restores\/","title":{"rendered":"Emergency Database Restores"},"content":{"rendered":"

I was reading a good article by Brent Ozar (blog<\/a>|twitter<\/a>) on a DBA Nightmare<\/a> where I came across this section:<\/p>\n

If you decide to restore the system databases, you should try this ahead of time.  Restoring the master database is different than typical user databases because you can’t use SQL Server Management Studio.  You have to set the SQL Server to run in single-user mode<\/a>, then use SQLCMD to restore the master database<\/a>, then remove the -m parameter that you added to start SQL Server, and start it back up again.  If you’re using a third-party product to do your database backups, it’ll require separate instructions .<\/p>\n

Which immediately brought Red Gate SQL Backup Pro<\/a> to my mind, since I’ve been working with it so much lately. This isn’t something I’ve tested while I’ve been learning about the product so I wasn’t sure if we had a good set of documentation on it. I shouldn’t have worried. <\/p>\n

I followed my nose, not even trying to do a serious search. I opened the help file, noticed a topic called “Backing up and Restoring.” I opened that and saw another one called “Restoring Backups.” I opened that up and scrolled through, the first place I looked mind you, fully expecting to have to start to search, when right there near the end of the documentation on the basic restore operation I found this section:<\/p>\n

<\/a> <\/p>\n

Restoring the master database<\/p>\n

To restore the master database, you must use the SQL Backup command line to ensure that the SQL Server is started in single user mode.<\/p>\n

    \n
  1. Stop the SQL Server service. <\/li>\n
  2. Open a command prompt window, and navigate to the SQL Server instance’s Binn<\/em> folder. <\/li>\n
  3. Start SQL Server in single user mode using sqlservr.exe c m<\/strong> for an unnamed instance, or sqlservr.exe c m s {instancename}<\/strong> for a named instance. <\/li>\n
  4. Use the SQL Backup command line interface to restore the master database.\n

    For example, at the command prompt, type:<\/p>\n

    SQLBackupC -SQL “RESTORE DATABASE master FROM DISK = ‘C:Backupsmaster 20070101.sqb'”<\/p>\n

    For a named instance, use the -I<\/strong> parameter to specify the instance name, for example:<\/p>\n

    SQLBackupC -SQL “RESTORE DATABASE master FROM DISK = ‘G:Backupsmaster 20070101.sqb'” -I Instance2<\/p>\n

    SQL Backup restores the master database, and SQL Server automatically stops the SQL Server service when the restore operation is complete.<\/p>\n<\/li>\n

  5. You can now start the SQL Server in normal mode. <\/li>\n<\/ol>\n

    On the one hand, I’m really happy that we’ve got that right there in the documentation, where it’s really easy to find. On the other hand, I was already planning a new article in my head and now I can’t do that one. Talk about mixed feelings. <\/p>\n

    By the way, because of how it works, Hyperbac<\/a> wouldn’t be a concern in this same situation.<\/p>\n

    For what it’s worth, go and read Brent. It falls right in line with what I’ve been saying. Your backups are only good if you can restore them. You only know that you can restore them after the restore is completed. If you haven’t tested your restore operations, and by that, I don’t just mean that you’ve tested the files, but that you’ve tested your knowledge and ability, then you don’t have a clue if you can actually restore your database. I’d rectify that situation immediately.<\/p>\n","protected":false},"excerpt":{"rendered":"

    I was reading a good article by Brent Ozar (blog|twitter) on a DBA Nightmare where I came across this section: If you decide to restore the system databases, you should try this ahead of time.  Restoring the master database is different than typical user databases because you can’t use SQL Server Management Studio.  You have……<\/p>\n","protected":false},"author":221792,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[],"class_list":["post-3300","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/3300","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221792"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=3300"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/3300\/revisions"}],"predecessor-version":[{"id":42030,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/3300\/revisions\/42030"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=3300"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=3300"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=3300"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=3300"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}