Can you take a differential backup of master?
2December 19, 2023 by Kenneth Fisher
In one of the sessions I attended during the Pass Data Community Summit the speaker asked “If master is in the simple recovery model can I take a differential backup of it?” And I of course thought “Yes, of course you can.” Turns out I was wrong.
BACKUP DATABASE master TO DISK = 'NUL' WITH DIFFERENTIAL;
Msg 3024, Level 16, State 0, Line 3
You can only perform a full backup of the master database. Use BACKUP DATABASE to back up the entire master database.
Msg 3013, Level 16, State 1, Line 3
BACKUP DATABASE is terminating abnormally.
Ignore the fact that I took the backup to NUL. That was just me being lazy for the demo. You can try it to a different location if you want, it won’t matter. It turns out, it also doesn’t matter what recovery model master is in. You’ll get the same error. As I understand it master was explicitly excluded in the code for differential backups. Go figure.
In case you are interested model and msdb act normally when it comes to backups and tempdb doesn’t allow any backups.
SQL Studies 
[…] Kenneth Fisher abides by Betteridge’s Law of Headlines: […]
Besides of curiosity – why should I take a DIFF, if a FULL backup is done in 2 seconds, since the master DB is very small.
PS: you can’t restore the master database on another server with a different name (e.g. as [master_prod_server]) and execute an CHECKDB() on it. The restore is possible, but the CHECKDB will always fail because of some special stuff inside the master db. So even if you do your CHECKDB for your prod databases on another server, you have to run it local (on Prod) for the master database (again no problem, since the master should always be very small)