[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 617
  • Last Modified:

SQL Server - Database Recovery Using VSS Snapshots

Hi,

I work for a company that uses a backup solution which backs up a client's SQL Server databases using file snapshots created via VSS.  The databases being backed up are all set to Full Recovery model.

I was told by the third-party backup solution provider that the way to recover a full recovery model database to a point in time is to do the following:

1. Take the database in question offline via SQL Server Management Studio
2. Replace the MDF and LDF files for the database on the server with the ones taken in the VSS snapshot backup
3. Bring the database back online via SQL Server Management Studio

I have two questions regarding this procedure:

1.  Is there anything else that needs to be done to make sure the database will come online properly in a consistent state or is it a simple as this 1-2-3 approach?
2.  If this approach works properly, and snapshots are being taken every hour on the server for all databases, is there any reason to enable the full recovery model assuming the client does not require a more granular recovery model than hourly? Do I even want the transaction logs if I can recover the MDF up to an hour's granularity?

This is an issue for me because the VSS snapshots do not truncate the transaction logs and so they grow and grow to fill the disk, but I don't want to spend the time to setup a full recovery model maintenance plan for the databases which includes consistent t-log backups if I can easily recover up to the hour using the VSS snapshot backups.

Basically, I am asking if I can do the MDF file replace and avoid the transaction logs altogether without any concern. Not being a SQL DBA, I am unsure.

Thanks in advance,

Jon
0
KPI1
Asked:
KPI1
  • 2
1 Solution
 
Daniel WilsonCommented:
The LDF file snapshot must match the MDF.

I honestly don't know whether the file snapshot method (taking snapshots while the DB is active) works or not.  However, if you DO get a good snapshot of the 2 of them at the same point in time, replacing those 2 files from backup would restore to that point in time.  You'd be looking at a CREATE DATABASE ... ATTACH syntax.

Given this method, there's no reason whatsoever to run Full Recovery Mode.  It would honestly do no good at all.

Before relying on the method, I would test it, restoring the DB on another machine, even a development desktop.
0
 
KPI1Author Commented:
Hi Daniel,

In your professional opinion, can you tell me whether I am safer/better off just using the built-in SQL utilities to backup the database and the transaction logs for each database and then utilizing the VSS snapshot backup utility to back THOSE SQL backup files up to the backup appliance?

I am thinking that this gives me the most flexibility since I could always pull back a copy of the full SQL Server database backup and any subsequent log backups and then restore them as needed to a point-in-time by rolling the logs forward.  In theory does this make the most sense?
I am concerned about the snapshots approach as well since I have never used this method to restore a database before.  I have always followed the traditional approach to SQL Server backups.  

But either way, thank you for confirming that the Full Recovery Model is useless if the restoration procedure relies on copying MDF's over from a VSS snapshot.  This was my suspicion.

Best,

Jon
0
 
Daniel WilsonCommented:
I am a fan of the built-in SQL Server backup methods.  They're proven.  I have no experience with the VSS-based approach.

But ... it appears Microsoft also approves of the VSS-based approach.
http://blogs.msdn.com/b/sqlserverfaq/archive/2009/04/28/informational-shedding-light-on-vss-vdi-backups-in-sql-server.aspx

I suspect the VSS approach makes for quicker backups b/c the shadow copy service is actually doing some part of the backup on an ongoing basis.

Running Full Recovery and doing traditional backups (including log backups) does give you the most roll-forward flexibility.  It also is a little more to manage.

So I think you're looking at a tradeoff between flexibility (traditional) and speed (VSS).  With it boiled down to that, I can't say what's right for your application.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now