Backing up & restoring 10-20 SQL Server databases to a ~synchronous state?

Question

I need to backup up 10-20 SQL Server 2008 R2 databases with sizes between 10-50 GB, while they are online and used simultaneously by a single enterprise app. I also need to restore them to a state that is largely synchronized across all databases (I can afford up to a few seconds of desync between databases). The purpose is to capture production data for QA/DEV environments.

I would strongly like to not demand databases run in full recovery and to come up with a backup method that is dedicated to capturing data for QA environments and remains independent of a main backup process which is not under my control.

For my customers, it will take 1-2 hours to capture 20 full backups at ~30 GB each. This makes taking full backups sequentially unacceptable as the databases would be too desynchronized when running in simple recovery.

I'm looking for an idea better than these:

IDEA 1: SAN-level snapshot of VM disks. xcopy MDFs/LDFs from snapshot.

Once the copied files are attached to a different server instance, its recovery process should produce consistent databases that are snapshot pretty much simultaneously.
Googling around convinced me this is a bad idea, at least because I may get desync vs. master/msdb/etc.

IDEA 2: Orchestrate a complex backup & sync-restore across all databases

This requires me demanding databases run in full recovery, which I don't want. Start parallel backups for all databases well before the deadline (T0). Once T0 is reached, backup all logs (should take at most a few minutes). Take the resulting myriad of backups and try to restore them & roll logs forward/back to obtain a somewhat consistent state across databases, relative to T0.
This requires a lot of planning & scripting to have it used reliably so I would go to great lengths to avoid it.

Am I missing some other solution?

P.S.1: I would've loved being able to use db snapshots. The idea was to initiate a snapshot on each db (which should be over in seconds), then fully backup each one sequentially over the following minutes/hours. Then restore all of them on a different server and revert each one to the snapshot. AFAIK this scenario is not possible because snapshots can't be backed up along with the database. They can only be rolled back in place, on the server where they were created. In addition, they require Enterprise Edition which I don't have for all customers.

P.S.2: If you know of a 3rd party solution capable of producing cross-db synchronized backups please mention it.

Answer 1

I need to backup up 10-20 SQL Server dbs used simultaneously by a single enterprise app, while they are online, in such a way as to restore them to a state that is largely synchronized across all dbs

What you are looking for is a consistent backup across all your customer databases, you should use FULL backups along with Marked Transactions (emphasis in bold added):

When you make related updates to two or more databases, related databases, you can use transaction marks to recover them to a logically consistent point. However, this recovery loses any transaction that is committed after the mark that was used as the recovery point. Marking transactions is suitable only when you are testing related databases or when you are willing to lose recently committed transactions.

Make sure that you take adhoc transaction log backup with COPY_ONLY, else your recovery will be a pain, since any adhoc transaction log backup without COPY_ONLY will break the log chain. As a precaution, you can restrict users to use only COPY_ONLY backups.

I need a solution for SQL Server versions 2008 R2 and later. Db sizes is up to 50 GB per db and the time to backup all of them is likely over 1-2 hours.

Marked transactions will work for your situation. The only thing to make parallel backups is to STRIPE them, but then you end up making sure that you don't lose your stripes of backup. To make them faster, you can play with BUFFERCOUNT and MAXTRANSFERSIZE.

You should use backup compression as well as enable Instant file initialization.

Refer to

• Using Marked Transactions (Full Recovery Model)
• How to increase SQL Database Full Backup speed using compression and Solid State Disks ?

Answer 2

If you are running full backups as well as transaction log backups (and you should if you consider this data important) you could just copy over the backups and transaction log backups to the test system and perform a point in time restore to restore the databases to +- the same time.

Depending on whether all databases reside on the same SQL Server machine or how well the servers clocks are synchronized you should be able to match the 'couple of seconds desynchronization' target.

It may be a bit of a band-aid solution but would meet the requirements and be fairly simple and inexpensive.

If you don't have full backups and transaction log backups from your important databases (which are in full recovery) you really need to revise your backup strategy. SAN level snapshots really moot the point of having a database in full recovery mode as you won't be able to do a point in time restore anyway.

Please read what MrDenny has to say about it

Answer 3

Under the circumstances that you have indicated have you looked at VSS backups through a VSS provider that is either 3rd party or Microsoft based? You can perform a COPY_ONLY backup that will not break your production recovery chain and you should end up with a backup that of all of the databases that you can then recover elsewhere to within your reasonable margins. Keep in mind that a VSS backup has some of the same mechanisms and downfalls as database snapshots in that a very active database could cause a disk space issue due to the sparse files used. Take a look at the TechNet resources on the SQL Writer service here and VSS backups of SQL Server here.

To do this through Windows Server Backup you will follow the wizard steps for a manual backup ensuring that you select VSS copy backup on the custom configuration settings under VSS Settings. This will allow your Windows Server backup to not interfere with any other backups taken on the server. See Windows Server Backup reference for details.

Answer 4

I'll vote @Kin's as the answer because it was the first one matching what the question asked. I ended up finding an additional answer and I'll describe it below.

For customers using simple recovery model I'll require a copy of MDFs & LDFs extracted from a temporary disk snapshot taken at T0 at the hypervisor or SAN level. I can use these to recover the dbs at the state from T0.

For customers using full recovery model I'll require either:

• Copies from their MAIN backup process of the latest full backup completed before T0 + minimal chain of subsequent transaction log backups covering T0. I can then perform a point in time recovery to T0.

• Access to perform my own auxiliary COPY_ONLY backups. I'll start them all in parallel at T0, which should take no more than a few seconds and was my main no 1 concern. Then, at restore, I'll perform a point in time recovery to FirstLSN from each backup. The beauty of this is that it doesn't require me to interact at all with the MAIN backup process, which was my other concern, they can even truncate the logs while my COPY_ONLY backups are running without affecting their coherence.

Answer 5

I do this several times a year for QA and other environments that are copies of production. For restores, full recovery mode is really necessary and restoring to a point in time works well. There is also a lot of replication and it is rare that we have 'row not found' errors after restoring to a point in time. We also use the SAN clone/snapshot method for a geographically distant copy of production and that also works well for having the databases synchronized.