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.
Best Answer
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):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 withoutCOPY_ONLY
will break the log chain. As a precaution, you can restrict users to use onlyCOPY_ONLY
backups.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 withBUFFERCOUNT
andMAXTRANSFERSIZE
.You should use backup compression as well as enable Instant file initialization.
Refer to