How to Rebuild SQL Server 2012 Master Database
All the system configuration settings and information is saved in the master DB. SQL instance specific settings such as linked servers, system config, endpoints and logon accounts. Note that SQL Instance will not start if master DB is missing. There are limitations to what you can do with the master DB. It makes very much sense that you cannot change the collation. Adding filegroups or files is not allow and you may not drop this database. This includes taking this DB offline.
SQL Agent jobs and scheduling information is saved in msdb DB. Model DB contains a base setting for new DBs created on that SQL instance. This allows database administrators set a template for newly created databases. When you create database mail accounts and database mail profiles, it will be saved in msdb DB. Backup jobs and restore jobs history is handled by msdb DB. This includes data, time, devices, etc.
In some cases you may need to rebuild these databases mentioned above. If you encounter data corruption in these databases, you must rebuild them. When you perform a rebuild, these databases are simply dropped and new default DB is created, just like a brand new SQL Instance install. It is implied that all data saved in these databases will be lost and restored to back to default configuration.
To backup system settings prior to rebuilding the master db, run:
- SELECT * FROM sys.configurations;
- Copy and paste information to notepad. Now, to rebuild the databases, rerun setup with REBUILDDATABASE option. To do this, browse to installation CD and run:
- Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=SQLInstance [ /SQLCOLLATION=CollationName]