1

I have a requirement to compare a database with the same database's backup file and restore the database from the backup file only if they are different.

Use case: I have a test server and I want to restore the database on it using a backup file on a remote file system iff they both differ. I was thinking about comparing the hashes(but read somewhere that there is a limit on the size). Any insights as to how this can be achieved? Also, I want to know how to generate a hash from the database and compare it with the backup file's hash.

jarlh
  • 42,561
  • 8
  • 45
  • 63
oyeesh
  • 551
  • 9
  • 21

2 Answers2

2

I have a requirement to compare a database with the same database's backup file and restore the database from the backup file only if they are different.

There's no way to compare a backup to a live database without doing a restore to a different location then comparing the schema definition and all the data.

You can use replication to keep the prod and test versions in sync. It's already built in.

how to generate a hash from the database and compare it with the backup file's hash

The backup file and the db files are different formats and the hash will never match even if they actually represent the same database state.

Terry Carmen
  • 3,720
  • 1
  • 16
  • 32
  • How about I generate a backup file for my database on test server and then generate a has and then compare it with the other backup file's hash? Is that possible? – oyeesh Sep 27 '18 at 16:44
  • @Shrav That wouldn't work because each backup contains metadata about when it was done, by who, database states, etc. and these change even if the data doesn't. If even a single byte in the backup is different by one bit, the hash comparison will fail. – Terry Carmen Sep 27 '18 at 16:56
2

You can compare the current database LSN with the backup LSN.

Note that, due to checkpoints, your database and the original database will quickly diverge, even if the data is not touched.

The closest thing to what you probably want is log shipping. It allows you to minimize the traffic between your original DB and the test DB, but it requires your test DB to be read-only, and to establish and maintain a correct log backup sequence on prod.

The short answer to what you ask is: is not possible.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569