Tell me more ×
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.
up vote 14 down vote favorite
2

We are looking at replacing traditional SQL server backups with a VSS based backup of mdf and ldf files. As a db person I'm somewhat twitchy about this, and yet I can find no evidence that this won't work?

Can anyone suggest a trial I can setup that would demonstrate where we can lose transactions with this strategy? [Yanking out the power cord during a long running transaction is fine].

The system we are looking at creates an initial snapshot of the mdf and ldf files and then copies across changes. I can't imagine a scenario where that could fail us.

Hopefully you can help me convince my boss that we need to keep traditional backups!

share|improve this question
9  
Unless it can completely synchronize the changes, how can you be sure that the delta in the .mdf (or multiple data files!) is consistent with the .ldf? There's a reason SQL Server doesn't just back up the mdf and ldf files as a "backup"... – Aaron Bertrand♦ Oct 30 '12 at 13:44
4  
The problem is, it's going to be tough to convince your boss if he needs to see evidence of it failing. I bet in most cases this will work "okay" - it'll be the exception, though, that will burn you. Reproducing the case where this fails may be quite tedious. – Aaron Bertrand♦ Oct 30 '12 at 14:06
6  
Related? msdn.microsoft.com/en-us/library/aa953743.aspx. Basically, don't – gbn Oct 30 '12 at 14:19
1  
Also serverfault.com/q/182434/4955 – gbn Oct 30 '12 at 14:20
7  
Well for one, even if your mdf and ldf file copies can be trusted, you've lost the ability to restore to a point in time, which you get from proper + log backups. – Aaron Bertrand♦ Oct 30 '12 at 15:57
show 4 more comments

1 Answer

up vote 4 down vote

Sorry to disappoint you, but VSS Backups of SQL Server are fully supported by Microsoft through the SQL Writer Service (http://technet.microsoft.com/en-us/library/ms175536.aspx).

When the third party backup application wants to take a backup, SQL Writer tells SQL Server to "Freeze IO" on the database. After the IO is frozen, then a snapshot is taken of the mdf and ldf files. After the snapshot completes, IO is unfrozen. This does prevent all write activity to the database while the snapshot is being generated, and could take 60 seconds or longer. The third party backup app is then copies the snapshots of ldf and mdf files. If you test the third party backup application and you see messages in the error log about Freezing and Unfreezing of IO for your databases, then it's using the SQL Writer interface, and the backups are good.

Full Description here: http://technet.microsoft.com/library/Cc966520

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.