0

I am attempting to configure a MSSQL 2005 Express db to flush its in memory data to disk so that the data can be captured by using disk snapshot software. I have been unable to find any particular way to accomplish this through any tools currently available for MSSQL Express. My option up to this point has been to shut the MSSQL service down, take the snapshot, and then restart the service. I would like to not have to dump the database out to a file, as this would defeat the purpose of taking a disk snapshot.

Does anyone know of a way to force MSSQL Express to flush its in memory data to disk, or is stopping and restarting the service the best course of action?

danorth
  • 65
  • 5

1 Answers1

1

VSS enabled backups.

Holocryptic
  • 5,665
  • 2
  • 29
  • 37
  • Thank you for the response. Does VSS actually write the in memory data back to disk, or does it copy to temporary storage area to allow for an application to backup just that chunk of data? The disk solution that I'm using is actually mirroring the primary disk, and then taking snapshots at defined intervals. Unless the data is written back to the primary disk, the snapshot will not be able to snap a consistent copy of the database. – danorth Aug 13 '10 at 03:58
  • @danorth, it flushes to disk. While it's in the 'freeze' state, it becomes a copy-on-write operation until the backup is complete. In a nutshell, it VSS enabled backups give data-consistent backups while still allowing normal operation of the database. The link I provided gives a pretty in-depth explanation of how VSS works with SQL, and you can find more info by searching VSS on MSDE/google. – Holocryptic Aug 13 '10 at 12:35