SQL Server with Mr. Denny

Jan 11 2017   6:00PM GMT

Should I back up system databases on an Azure VM?

Denny Cherry Denny Cherry Profile: Denny Cherry

Tags:
Graphic with a back up button. Because we can't restore with out backups.

https://www.flickr.com/photos/sonofgroucho/3344527949/

Well that really depends. When your database VM fails for some reason (either someone does something bad like delete the VM by accident, or a Windows patch doesn’t install correctly and the VM won’t start correctly, etc.) do you care about having your logins on the server, your jobs, any SSIS packages deployed to MSDB, any operators, etc. restored to the system. Or do you want to have to redeploy all those things after you rebuild the server and get SQL set back up? If you aren’t OK with redeploying those (or the risk of those deployments is to high) and all the risks that go with that, then doing a restore of those system databases is going to be the way to go.  (Hint, a restore is always going to be more reliable than a redeployment.)

Now I know with AlwaysOn Availability Groups you can just fail over to another node, but at some point you need to rebuild that failed node.

What if you don’t have Availability Groups in place. Maybe it’s a tier 2 server that doesn’t have HA beyond what Azure offers, or maybe it’s a failover cluster so you don’t have multiple copies of the system databases.

Now I get that restoring master isn’t as easy as a user database, but it’s actually pretty easy to restore. It just requires restarting the SQL software in single user mode with a couple of switches (SQLServer.exe -c -f -m) from the command line, then you have to login using another command line window to actually restore the database using sqlcmd and the RESTORE DATABASE command to actually restore master. Restoring msdb is easier as all you have to do is stop the SQL Server Agent (any anything else using msdb) then restore the msdb database like normal.

If I can easily describe how to restore the two databases in a single paragraph then it shouldn’t be all that hard for someone to do.

Now the cost. Your system databases should be small. If you’ve got pruning setup correctly in your msdb database then the backups (which you should be writing to RA-GRS, Readable Geo-Redundant Storage using the Backup to URL feature of SQL Server) should be a few megs per day. Assuming you keep them for 10 days (which is probably more than most people do) and we’ll assume 1 Gig of space needed for backups to make the math easier you’re talking about $1.20 per month to store the backups. If you kept the backups for 31 days then the cost is $3.72 per month for the system backups. (Assuming you don’t have an EA, and you are paying the full retail price, which basically no one should be.)

I’ve shown that system databases are easy to restore.

I’ve shown that they don’t cost much to backup (if you aren’t paying attention, the cost to store these backups is a rounding error in the cost of the SQL VM).

So what’s your excuse now for not backing them the system databases?

Denny

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: