Solved

SQL Server - Database Recovery Using VSS Snapshots

Posted on 2014-07-31
3
485 Views
Last Modified: 2014-08-04
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
Comment
Question by:KPI1
  • 2
3 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 40232005
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
 

Author Comment

by:KPI1
ID: 40232140
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
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 40232251
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question