Consistent backup of multiple MS SQL databases

All we need is an easy explanation of the problem, so here it is.

We have ~30 DBs on MS SQL server and unfortunatelly DBs rely on each others data, do cross-db joins and system fails on inconsistency of data.

This sounds terrible, and requires fixing ASAP, but i’m not sure that it will happen faster than we need some data from backups.

Is there a way to make backups of 1.5TiB (equally spread across 30DBs) in a way that gives best resulsts in terms of consistency between DBs?

Thanks!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Note that STOPAT refers to a datetime value that each log record has. This has a resolution of 1/300 second (it always ends with .xx0, .xx3 or .xx7).

Several things can happen between two such values.

If you want a true "point in time", then you can use marked transactions. Your backup routine will be more complex, though, since you need to add those markers. Below are two articles from MS that discusses this:

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-of-related-databases-that-contain-marked-transaction?view=sql-server-ver16

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/use-marked-transactions-to-recover-related-databases-consistently?view=sql-server-ver16

Method 2

You can restore to a point in time as outlined in the documentation using the STOPAT clause.

The time of the first finished backup will be the point of consistency.

It would be best if you parallelized the other backups as much as possible because the delay between the first and last backup will cause a data loss.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply