Using Syncback to create a copy of system databases

• larry Hennig

  SSChasing Mays

  Points: 656

  More actions

  April 9, 2009 at 12:00 pm

  #208303

  This posting is focused on using SnycBack as a tool for creating local copies of the system databases for recovery purposes. My question is this:

  Do I need to worry about negative impacts on the server's performance or consumption of disk space by VSS if there are other large data or log files existing on the same volume as the system database files?

  Here is a fuller explanation ...

  Our TSM tape backup system skips open MDF and LDF files, so after a server has been recovered, the SQL Server system database files are missing. There are local disk backups of the system databases sitting on the disk, but SQL server cannot be started, so the backups cannot be restored. I would like to avoid having to re-install SQL Server or run the setup REBUILD option to get past this problem.

  My strategy is to maintain a copy of the system database files elsewhere on the server - a copy that will be backed up onto tape along with the rest of the server files (because the copy is not being held open). This copy needs to be from the current build of SQL Server that is on that Windows server, but can be as old as the last date that a patch was applied to that instance of SQL Server

  I know that the safest way to do this is to manually shut down SQL Server long enought to make the copies, but shutting down SQL Server can cause application outages, and is prone to human error (forgetting to do it after applying a patch). So, my objective is to create an automated process for creating these copies, driven by a SQL Agent job that polls for changes in the build level.

  I am experimenting with Syncback as a tool for creating the copies of the system files. Here is what I have learned, followed by a question I am seeking input on ...

  Only the paid version of Syncback will back up the system DB files while SQL Server is running. The free version is not licensed for commercial use (only the freeware utilities are licensed for that purpose). The standard version can be used for this purpose (called by a SQL Agent job).

  Syncback leverages Volume Shadow Copy service (VSS)and SQL Server's VSS Writer. This means that the copies will be a fully-synchronized point-in-time copy of the files. It also means that there is some additional overhead - processor load, I/O load and disk space.

  From what I have read about VSS, the activation of shadowing is at a volume level, not a file level. This has me a little concerned about the potential performance and disk space overhead of running SyncBack on a volume that has one or more other large database files that are actively being udpated while the Synback is running.

  Do I need to worry about negative impacts on the server's performance or consumption of disk space by VSS if there are other large data or log files existing on the same volume as the system database files?

  Please ony reply from experience or specific knowledge of VSS or Syncback.

  Oh, I can add that ntbackup cannnot be used for this purpose due to conflicts with SQL Server's internal backup and restore features (there is a KB article explaining this that says it is by design and don't try to use both ntbackup and SQL Server's backup command on the same computer.