What is the practical purpose of backing up the system databases?
irl last edited by
I know that system databases hold logins, security info, jobs, etc.
Assuming I have backup of all system and user databases, and the SQL server crashes, then I will have to re-install the SQL server which will have its own system databases. It does not make sense to restore the system db backups that pertain to the crashed server onto the new server (I don't think this is even allowed).
So, what is the practical purpose of backing up the system databases?
You can restore each of the system databases to a new installation and you should back them up, so you have all of your instance-level data saved. The only exception is
tempdbwhich is ephemeral and not meant to be recoverable, since as the name implies, it's meant for temporary storage.
masterdatabase contains information on all of your Logins and any other system level information.
msdbcontains information on all things pertaining to the SQL Agent such as Jobs, Alerts, and Operators and also stores history tables such as mail history, backup and restore history, etc. Finally the
modeldatabase rarely changes unless you explicitly customize it, but it is used as the default template when a new user database is created, and having a backup of it can save you from if it gets corrupt (as Tibor mention).
Additionally, if you're using Replication and that server is the Distributor then there's another system database that gets created called
distributionwhich is used to facilitate its jobs for distributing the changes from the Publisher to the Subscribers. That should also be backed up and can be restored, as well.
Please see https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-and-restore-of-system-databases-sql-server?view=sql-server-ver15 for more information.
For anecdotal reference, I've been in the situation where my server became corrupt and I had to spin up a new one with a new SQL instance installed, and then restored the system databases too. Quite a relief when you have all the aforementioned instance level data available to you after such an event occurs, and all you need to do is restore the system databases.