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!
Best Answer
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