Solved

SQL Server - Database Recovery Using VSS Snapshots

Posted on 2014-07-31
3
465 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now