Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

However, if this is not sufficient, the only thing I can think of is to rip through ever row of every table of every database and calculate a checksum (on both instances) as well as get row counts for every table in every database.

Is there any better way to satisfy the "exact replica" verification criteria? I'm also open to better documentation.

3 Answers
3

When you take the backup of database, the last LSN on the source will be X. If any activity will occur (including, say, an automated checkpoint), the source LSN will progress forward to X+n. If there is any activity that occurred on the source and was not captured on in the backup it would leave an imprint in the source log, somewhere between LSN X and X+n. Using fn_dblog one can look at the log and see if any such activity occurred.

An easy way to ensure no such activity occurs after backup is to set the database to read_only immediately after taking the backup. To prevent any activity sneaking in between the backup and the change to read_only you can disable connections or set the database in single_user mode, even start the server in single user mode.

I suppose I could try to talk them into a certain RedGate tool.
–
swasheckApr 17 '13 at 18:43

Idera also has a data-compare tool. However, that really will just do a schema compare, then run through all the rows in each and every table.
–
Max VernonApr 17 '13 at 18:45

I would prefer Redgate as based on my experience, its flexible and does a great job. It also has command line functionality to automate the comparison of schema and data. Though I would not go for it (in your situation) as @MaxVernon already mentioned that it will be just overkill.
–
KinApr 17 '13 at 20:46