Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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 tempdb which is ephemeral and not meant to be recoverable, since as the name implies, it's meant for temporary storage.
The master database contains information on all of your Logins and any other system level information. msdb contains 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 model database 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 distribution which 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 Backup & restore: system databases (SQL Server) 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.
How would you restore your jobs then? Do you have all of them scripted in version control? If not, then you can restore msdb to get them back.
Now about the model database. If you never make any changes to it, then indeed, backups are pointless. But some admins are aware of its power and adjust it to their needs. For them backup might be useful. Unless they have it all scripted in version control and find it easier to run the scripts against shiny new database than restore the old one.
masterDatabase