Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

Is it possible to fully recover a database from a snapshot plus transaction log backups? By snapshot I mean a crash-consistent clone of the Server - in this case an LVM snapshot that is shipped offsite while the live server continues to operate and generate logs.

--edit

To clarify, the LVM snapshots are of the entire Virtual Machine including OS, SQL Server and everything else. This snapshot is then transported to a remote site and successfully started on the host. I know I can at this point restore a full backup and recover with the logs, but I wondered if it was possible to skip the full backup/restore step and use the snapshot as a base?

share|improve this question
Are these SQL aware snapshots? Does SQL get told a backup is happening and freeze/quiesce the IOs and log that a backup took place? – Mike Walsh Oct 31 '11 at 13:32
@Mike no - they are as if someone has pulled the power cord – Jack Douglas♦ Oct 31 '11 at 13:39
just to make sure we are on the same page. You mean a snapshot when the server is off and all services are stopped? Basically - SQL is always writing to the log and data file and that order and consistency needs to be maintained. If you take a snapshot with SQL running, you are not guaranteeing that consistency and will likely be unable to recover. – Mike Walsh Oct 31 '11 at 14:07
@Mike, I mean a snapshot of a running server - like a vss snapshot (though in this case I am using LVM). I will have transaction logs from before, during and after the snapshot available during recovery. I think you are saying recovery will not be possible in this scenario, is that right? My hope was that SQL Server would be able to return the 'crashed' database to a consistent state from the transaction logs and then continue to apply them beyond the point of the 'crash' – Jack Douglas♦ Oct 31 '11 at 14:10
@JackDouglas - are you able to attach the database files from that LVM snapshot and have the database online? – Marian Oct 31 '11 at 14:16
show 3 more comments

2 Answers

up vote 1 down vote accepted

Based on your answer to the first comment question...

no - they are as if someone has pulled the power cord

You've got your answer. They aren't a consistent backup, so SQL isn't aware that a backup happened so you can't put it into a restoring state and restore logs over it.

share|improve this answer
Thanks, it is handy to have this confirmed :) – Jack Douglas♦ Oct 31 '11 at 19:14
2  
No problem. I'm here to deliver bad news. :) – mrdenny♦ Oct 31 '11 at 19:22
up vote 1 down vote

Going from this: "I can at this point restore a full backup and recover with the logs, but I wondered if it was possible to skip the full backup/restore step and use the snapshot as a base?".

In order to restore the log backups the database needs to be put in a 'restoring' state. Now I'm not actually sure if you can put the online database in that state without actually making a real restore of a full backup. The normal way would be to restore the db from full backup using the NORECOVERY option (expects log backups) and then restore the following log backups.

I'm not sure if there's a way to circumvent it.

PS: Jack, you don't need to mark this as the answer yet, let's let the wiser SQL Server admins here speak their own experience, maybe I'm just plain dumb and missing something fairly obvious :-).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.