2

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!

Improve this question
3
  • FWIW, there's nothing inherently wrong with doing cross-database dependencies (joins, etc), it's actually how the system i presently manage is designed. But I understand your concern.
    – J.D.
    Jun 3 at 11:59
  • @J.D. backups, replication, failover when single DB is bad on replica, migration to posrgres. Everything is hard when you do cross-db joins.
    – Grigory
    Jun 6 at 16:47
  • It depends on your use cases - I have no issues with the system I currently manage which is dependent on multiple source databases being joined together, for any of those features. Migration to PostgreSQL is not something one normally plans for? but if such a case were to arise then it wouldn't matter anyway since you'd likely be dealing with re-architecting. It really just depends on how you architected your database too. The one I manage only has a few core cross-database views that are then consumed by everything else, so those joins and dependencies aren't repeated everywhere.
    – J.D.
    Jun 6 at 18:57

2 Answers 2

Reset to default
13

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

Improve this answer
2
  • Thanks! Since i have multiple DBs i believe i have to insert multiple transactions which will take some time, and it can have similar or even worse accuracy than just point in time. But this is issue of me having multiple DBs :(
    – Grigory
    Jun 6 at 16:49
  • I believe that you only do one BEGIN TRAN, and from that transaction you perform some modification in each database. I.e., then an UPDATE (for instance) hits some table in a different database, then that database also gets that transaction marker. Fortunately, I haven't had the need for this myself, I'm going by the articles I linked to.
    – Tibor Karaszi
    Jun 7 at 11:14
4

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.

Improve this answer
2
  • 1
    Please sort by the vote count to find a better answer. I cannot delete or revoke an accepted answer.
    – Zikato
    Jul 4 at 13:30
  • 1
    I've changed the accepted on to the next one, but I still stick to your answer since it is better for my case. There are no cross-db transactions and therefore there is no possibility to set any consistent marker. Time is the only good marker in these circumstances.
    – Grigory
    Jul 12 at 17:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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