question

Moz avatar image
Moz asked

Windows Server Backup vs. SQL Server Backup

If one were to backup the Windows Server on which SQL Server resides is it absolutely necessary to backup the SQL Server instance and what would be the reasons for the database backup? I'm am asking specifically where SQL Server is a data warehouse implementation. Thanks.
backupswindows-server-2008-r2
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Tim avatar image
Tim answered
If you backup the SQL Server OS and associated drives then you could potentially have a much faster SQL Server rebuild if you were to lose the OS. Have SQL Server backups allow you to recover your data. If you only have the SQL Server backups and do not have a mirror, a log ship partner or a cold standby then you would have to build another SQL server to restore your backups to. If you had what is referred to as a bare metal backup (OS) then you could perform a bare metal restore then perform an operational restore of your most recent SQL Server backups and fully recover. The key question to be asking yourself is what is your SLA. What is your RPO and RTO (Recovery Point Objective and Recovery Time Objective) and make sure your backup and recovery plans meet that. If your system can withstand a 24 hours of down time but only 15 minutes of data lose then rebuilding the SQL Server might be acceptable and you want to make sure you have tlog backups frequently enough to meet the 15 minute data loss SLA. I know many shops that do not perform OS level backups on their database servers simply because they have a mirror or log shipped partner. I know other shops that are 100% virtual and make clones or backup the VM completely for restores. It all comes down to your risk level and SLA's. In all shops I have dealt with, they can not allow their Data Warehouse to be down for any significant amount of time so ....
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Moz avatar image Moz commented ·
Thanks a lot Tim for a useful answer. I can see some of the issues I will have to weigh up now. I think ideally we are looking at OS and SQL backup (belt and braces). Thanks sqlaj for the additional info also.
1 Like 1 ·
sqlaj 1 avatar image sqlaj 1 commented ·
I agree with what Tim said. We do full VM backups only excluding the data files and logs. Just this past weekend we had issues with our Dev domain and lost a bunch of VMs. Restored from tape and then restored databases from backup. Of course there were a few additional steps in there, but my $.02 worth is if you can (all resources considered) backup the OS and SQL.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
One thing to keep in mind, SQL Server backups take into account transactions. OS level backups (with the exception of certain VM and SAN snapshot mechanisms) absolutely do not. So while the OS backup can backup the files that define the database, there is a very high probability that you will not be able to restore those backups. I wrote more about this issue in an article on preventable backup errors: https://www.simple-talk.com/sql/database-administration/7-preventable-backup-errors/ This is an additional worry to everything that @Tim referred to.
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Moz avatar image Moz commented ·
Grant thanks a lot for that important information. I have checked out the link and understand some of the pitfalls a little better now.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.