SQL Server will not run without the 5 system databases.

• master
• model
• msdb
• tempdb
• mssystemresource - this one is hidden

In fact, they are so important that if there are problems with them you will have to go through hoops to even get SQL started, and even then it won't work correctly. In some cases, tempdb for example, you can actually restart SQL without that database in order to fix things. Even master can be restored.

If you have a good backup.

Even tempdb can become corrupted, and despite the common belief restarting SQL will not fix it. So what do we do if we don't have a good backup? You should be taking backups of your system databases along with your user databases, but in the real world, things do happen.

There are several ways to fix each of the system databases but occasionally, you can find yourself backed into a corner where none of them are effective.  Because of this, we can use the setup.exe command to replace the system databases, even the resource database (which can't be backed up). Note: You will also have to do this in order to change the instance collation.

Because the system databases contain a lot of information necessary to run SQL Server and because this is a complete replacement of all of the system databases, the first step, is to make sure you have all of the information you'll need to recover properly. This includes things like:

• The contents of sys.configurations.
• Settings like instance collation and version (including latest SP/security patch installed).
• List of user databases and the location of the MDF for each.
• The location of any backups of the system databases just in case.
• Instance level security information (logins, server roles, server permissions, server role membership etc).
• Additional information may be needed depending on what you have set up on the instance. Replication information for example.

When dealing with corruption you may not be able to get all of this information. So if you are the careful type you should consider creating a process that scripts this information out on a regular basis.

Next, you run the setup command. You can follow the link above for the full set of parameters.

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]

As I said above, this is also the only way to change the collation of an instance so unless you want to do all of this again, you need to make sure CollationName is correct. Obviously, this also implies that even though it's an optional parameter I highly recommend including it.

Last but not least, apply all of the settings, attach your user databases, and run all of the code that you collected above.

You'll note this isn't easy. It certainly isn't something you want to try doing for the first time during an emergency. As with many of these types of low occurrence, high impact tasks, I'd recommend trying it out once or twice ahead of time. Just in case.