Solved

SQL Server - Database Recovery Using VSS Snapshots

Posted on 2014-07-31
3
535 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

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.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

728 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