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.
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?
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.
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 :-).