Let me start this blog post with a statement – “Master” database is the brain of SQL Server. We will go in detail why I would call it so.

If you ever worked in SQL Server, one way or other way you touched or used this system database. Master database is the primary configuration database in SQL Server. This system database is storing lots of information like configuration, login details, end points, linked server details, other object details etc. That means, In short – without master system database, the SQL Server will never be able to start its services.

Backing up of Master database is mastermind

Since Master database contains lots of important information as described above, it is very important to make sure we regularly backing up Master database. This would prevent to lose the important information at the time of corruption of this database. (We should also validate the backups as well.) Since master database would be generally small in size, backup process could be an instantaneous one.

Master database comes with simple recovery model, if you have a daily backup scheduled, there should not be a reason for change in recovery model. It is important to understand, not all objects can be scripted out from master databases, backup of mater database is unavoidable.

The most important aspect is that we can’t do transaction log backups for the master database, so there would be a chance of losing data that were made since the last full backup.

Forbidden operations to Master database

1. We cannot drop Master database
2. We cannot take Master database to offline
3. We cannot rename Master database
4. We cannot change the owner of Master database
5. We cannot set Change Data Capture (CDC) on Master database
6. We cannot set Change Tracking on Master database

See Also

Restore SQL Server Master Database Options

If you enjoyed this blog post, feel free to share it with your friends!