We are decommissioning our old SQL Server 2000 Ent. instance in favor of SQL Server 2008 R2 Ent. My planned migration path is:
- Terminate client connections (2000)
- Full Backup (2000)
- Restore (2008 R2)
I am being asked to provide conclusive proof that every single transaction "made it" and that the data is an exact replication of what existed on the 2000 instance.
I hope that I can use the following documentation as evidence:
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.
Best Answer
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_dblogone 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.
So a procedure like following should be pretty much bulletproof:
-m)