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's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Our IT department backs up the entire server every night (an SQL Server instance is installed on this server), which should be backing up that server as well as the entire network, in case something goes wrong...

So my manager asked what is the significant of my Full, Differential and Log SQL backups versus whatever the IT department backs up? To save more space on our server rather than keeping these files for a couple of weeks and deleting them, she thinks IT will just provide them!

I know it's not right, as I can restore up to the last 30 minutes with my log backups, IT restores it the next day, but is this the only difference?

Since I save/send my database backup files to the same server, IT will restore them but if I don't have these backup jobs in my maintenance plan then IT can just restore the SQL instance without any of our tables, transactions... etc. Am I getting this right?
Any advice would be really appreciated.

share|improve this question

Database backups give you point-in-time restore capability (provided you have FULL recovery model). Even if your IT people take backups every few minutes, which is extremely unlikely, you'll still have a gap.

Server backups do not replace database backups, they complement them by "archiving" the database backup files long-term (i.e. more than just today).

In the end you and your management have to decide your RPO (the recovery point objective - how much you need to be able to recover in a crash). With only daily server backups and no database backups, you stand to lose a full day's work in the worst case.

Edit: @Sting has a valid point in that shadow copies (the mechanism most likely used to make server backups) are not very likely to take an exactly simultaneous copy of all your database files (including log files), which may lead to inconsistencies when you restore the backup. For instance, if the shadow copy reads the transaction log a few milliseconds before it reads the database file, the database file could contain an uncommitted transaction, but as the transaction was committed a millisecond later, the log will not have any record of it.

share|improve this answer
    
Thank you Daniel, yes we have FULL recovery model, IT runs their nightly server backups at 7pm, I run my database FULL backup at 6pm, I also have daily hourly Differential backups and Log backups every 30 minutes during business hours... So if I understood you correctly, IT can provide me the restore that I need from the 7:pm server backup file but I also will need the 6pm DB backup file since they are not replacing each other and they are different? – Mary Feb 22 at 19:14
2  
With database backups shipped to another server, if your server crashes, you'll have point-in-time restore capability up to the last transaction log backup (no matter what time the server itself is backed up). If you rely only on server backups alone, you'll have to go back to the state the database was last evening at 7 pm. – Daniel Hutmacher Feb 22 at 20:32
1  
This is the best answer I think. As a sysadmin, I'm using my server backup systems to solve issues around DR of the database server or databases as a part of my whole infrastructure. Our DB Admins use SQL backups to solve issues around issues with databases and ransactions in a more focused manner. That's not to say that either type of backup can't pull double duty and solve the other's issues but they do have a slightly different focus... – RobM Feb 23 at 13:57

There is a chance that a restore of mdf and ldf files from shadow copy will be transactionally inconsistent. That means, these shadow restores don't comply with database ACID properties.

https://msdn.microsoft.com/en-us/library/aa480356.aspx

Chances are the restore will probably work, but you'll be left wondering what you're actually getting. (Not to mention, you'd be tasked with testing to make sure server backups/shadow copies are working properly on each and every server) Additionally, there's no way to restore transaction logs to a point in time like you can using SQL Server T-SQL RESTORE LOG/STOPAT.

Until Windows Server backups/restores comply with the SQL Server ACID test, our industry cannot afford taking any chances.

Having said all this, I've been in some of the weirdest meetings. If you convey the issues to IT and they still don't seem to care or they're willing to take the risk, that removes a huge burden from your shoulders. Whatever happens, document the meeting minutes of whatever everyone decides and why everyone decided it and send it out to the meeting attendees.

share|improve this answer
5  
This risk can be much higher depending on how the "system backup" software works and whether data/log files for any database are on different drives. Shadow copies are great until they don't match. – Aaron Bertrand Feb 22 at 19:31
    
Aren't Windows volume snapshots supposed to be consistent? – usr Feb 22 at 22:11
    
@usr I don't think that's true across volumes though. – Andy Feb 22 at 23:30

This all depends on what product your IT department is using for server level backups.

For example, in a virtual environment VMWare will take snapshots of the server. If SQL Server is involved VMWare has an option that most Admins enable (or it could be by default I don't know) that will freeze the IO for the databases during the snapshot. Now while this should only take seconds you have the potential for that to cause problems on your application, and is not really a trusted method to use for restoring database.

If you are using a 3rd party product to do server level backups the likelyhood is this is just taking file-level backups of your databases. In that as well it has to have the ability to take backups of files that are locked, because SQL Server has any attached mdf and ldf files locked from Windows perspective. Symantec's BackupExec for example utilizes Advanced Open File Option to perform this, so it can basically take a picture of that locked file. Just the way that sounds will make most DBAs cringe if the have to restore the database with a backup like that, think about the conistency the database is when it takes that backup. There is no guarentee if the backup is fired while a data load process is ocurring, what part of the data load did that backup get?

SQL Server native backups are trustworthy to the respect they are verified as good backups. You know exactly what state they were in when you fired the backup for a FULL, whether you have this scheduled around data loads and such. A log backup for FULL recovery model guarentees you can restore that database down the second.

If your manager is dead set on using the server level backup I would heavily research the product they are using. I would find out if there is any SQL Server "add-on" or backup agent that can be purchased to let it do VDI backups of the databases.

Something to also consider and discuss with your manager is what involvement you will need to have on verifying and troubleshooting if SQL Server backups fail. I have used Netbackup heavily at previous jobs and had a client few years back wanted to me to go through testing use of Netbackup's SQL Server agent for their environment. This included other DBAs that had to also provide support. I told them upfront that troubleshooting backup failures for SQL Server required you to know a good bit about Netbackup. Netbackup master servers generally are run on Unix servers, so you now have to know some Unix....can be fun but more of a pain if you are already busy. Just something to consider and can be a good discussion point with your manager, and find out who is responsible for troubleshooting failures.

share|improve this answer

There are just less then a million variables in your question. You will need to talk with your IT department on what backups they take. In all likelihood they have or can have up to the minute backups available. How long it takes them to load those depend on more variables.

In a perfect scenario, your IT department is keeping your backups on one or more different servers in different locations. You are probably keeping your backup on the same server your databases are living on. So if the server dies or your building burns down, your IT department can probably restore your files, but the backups you took will be gone with the server.

BUT you can restore your backup, at your speed, whenever you want, provided your server is still alive.

As others have said, it depends on what your needs are, your tolerance for risk, and how important control of recovery time is. If you want to recovery from something stupid you did, your backups are going to be quicker and better. If you want to recover from a disaster outside of your control the IT backup (should be) the better choice.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.