Consistent backup of multiple MS SQL databases



  • 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!


  • QA Engineer

    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


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2