Home / server / Questions / 182434
Accepted
tylerl
Asked: 2010-09-18 16:30:20 +0800 CST

SQL Server Backups using Volume Shadow Copy

  • 772

Many well-known backup utilities for Windows use the Volume Shadow Copy Service built in to newer versions of windows to create a point-in-time snapshot of the disk in order to safely back up open files.

But how well does this mechanism play with SQL Server? I know that an SQL Server VSS Writer exists to help backup systems interoperate with SQL Server, but not all backup systems know about SQL Server and take advantage the backup-related API that it provides.

So my question is, what about the programs that don't know anything about SQL Server?

They presumably get a point-in-time snapshot of the MDF and LDF files as they existed at one single instant. And presumably the SQL Server data format is robust enough that such a snapshot would be guaranteed to be consistent and usable.

Is that the way things work, or am I missing something? Are there "gotchas" about using such a backup mechanism, or would I be safe?

windows sql-server backup vss

4 Answers

  1. Best Answer

    Short answer: they don't play very well together unless you've got SQL server configured appropriately (and even then I wouldn't recommend it).

    There's a lot of information about VSS & MSSQL playing together here:
    MSKB919023: SQL Server 2005 connectivity and Volume Shadow Copy Service (VSS).

    One word of warning though:

    presumably the SQL Server data format is robust enough that such a snapshot would be guaranteed to be consistent and usable

    That's a big assumption, and one that's not always safe to make unfortunately. That's why your MSSQL maintenance plans are essential to ensuring you can recover from disasters.

    • 8

  2. You never backup MDF and LDF files.

    You run backups (plans, SQL, SMO, whatever) then backup these backup files.

    Frankly, I don't get a fig what any vendor says about VSS. I also dislike any vendor that has a SQL Server plugin for backups. You use SQL Server native backups only

    FYI: you also exclude MDFs and LDFs from virus scanning too.

    • 6

  3. If you don't have software that has a SQL "option" then:

    Create SQL maintenance plans or scheduled Backup jobs for the databases, dump them to disk, then backup the server. Or when you dump them, dump them straight to your disk-to-disk backup location if you have one.

    • 2

  4. The main problem with VSS backup is caused by caching in sql server.

    The current state on your disk does not always contain all database changes. Backupsoftware must be aware of this to ensure that sql server write all of it's caches before you take am snapshot.

    Having that said: the safest method is still backuping up in sql management/with sql server tools.

    And you should exclude all sql server data and backup files from virusscanners (.bak, .mdf, .ldf).

    • 2