master db deleted

• RGP

  SSCarpal Tunnel

  Points: 4381

  More actions

  March 11, 2015 at 4:59 am

  #298661

  Hi folks

  I have a couple of questions

  1. Accidentally master database deleted on one node of cluster can sql server be made up on the other node? In a two node cluster?

  2.what is the sequence of starting the resources in the cluster?

  Thanks

• Lowell

  SSC Guru

  Points: 323504

  More actions

  March 11, 2015 at 6:22 am

  #1783243

  if the service is running, it's not possible to delete the master database either accidentally or on purpose; it has a file lock on it by the SQL Service.

  if the service was not running at all on either node of the cluster, and you physically deleted the master.mdf from the shared disk resource, you'd be in serious trouble; it might require a recovery of the file(s) from any disk backups from the server(if you had any), or a reinstall of the services on each node.

  you might be able to bring the instance up in single user mode and restore from backup, but i've never had that happen to me, so i'm not sure.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• dan-572483

  SSCoach

  Points: 15870

  More actions

  March 11, 2015 at 11:42 am

  #1783404

  Assuming you have a master DB backup, it can be restored, but the process is cumbersome, not well documented, and differs slightly depending on which version of SQL you're using. SQL 2008 R2 is slightly different than SQL 2008.

  Essentially it is

  1) Launching a file in the MSSQL folder that will recreate the system databases. You may want to move/rename the msdb and model data and log files so they don't get overwritten. This will allow SQL Service to start, but it will not recognize the user databases.

  2) Configure SQL to start in Single User mode. Open the SQL command line interface, then run a RESTORE DATABASE command to restore the master database from your master db backup (assuming you have one). You can't do this from Management Studio because that maintains a connection to master, and you can't restore and DB if there is another connection to the DB.

  3) When the restore completes, the SQL service will shut down. Reconfigure for multiuser access. Restore/fix the other system DBs and you should be good to go.

  Please google with the exact version of SQL to get the details of the process I've outlined.

  To prevent this, during downtimes I periodically place a copy of master.mdf into a folder on the C drive. If you've done this after updates, config changes and adding/dropping DBs you may be able to skip steps 1-3 above.