Solved

SQL Server - Database Recovery Using VSS Snapshots

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

896 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

12 Experts available now in Live!

Get 1:1 Help Now