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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We are decommissioning our old SQL Server 2000 Ent. instance in favor of SQL Server 2008 R2 Ent. My planned migration path is:

  1. Terminate client connections (2000)
  2. Full Backup (2000)
  3. 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.

share|improve this question
up vote 9 down vote accepted

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.

So a procedure like following should be pretty much bulletproof:

  • restart source server in single user mode (-m)
  • connect and take the backup
  • change database to read_only
  • restore the database on the new server
  • run any tests you have on the new server
  • connect applications to new server
  • decommission or restart old server
share|improve this answer

I would say:

  1. disable connections on the 2000 server.
  2. Create a backup on the 2000 server, then verify it against the database.
  3. Restore onto the 2008 server.
  4. Ensure connections are still disabled on the 2000 server.
  5. Change the name on the 2000 server, and disable the SQL Server service.
  6. Sign off that you restored the verified copy of the database onto the new server.
  7. Start the new server.

I fail to see how there could possibly be missing transactions on the new server, assuming there are no transactions taking place on the source machine during and after the backup process.

For proof, the only thing you could do is compare the data in the source and target databases; however that is total overkill since the backup and verify will do exactly that.

share|improve this answer
I suppose I could try to talk them into a certain RedGate tool. – swasheck Apr 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 Vernon Apr 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. – Kin Apr 17 '13 at 20:46

There are many ways to do it.

a. Backup from 2000 instance and restore it on 2008R2 instance. This will require your application downtime until you restore the backup successfully to 2008R2 instance.

Also, you have to kill all the connections to the 2000 instance as you dont want to loose the data.

b. Set up logshipping from 2000 to 2008R2. When failing over, you just have to take a tail logbackup and restore at the secondary (2008R2) instance and then bring the database online.

Option b will be requiring less downtime.


You can have Log-shipping between SQL 2000 and SQL 2005/2008R2. BUT you cannot keep secondary in STANDBY mode.

Some references on Implementing Log shipping on SQL 2000. Note that this applies for setting up log-shipping from 2000 to 2005/2008/2008R2 as well.

Implementing Log Shipping

Migrating a SQL Server 2000 Log Shipping Configuration to SQL Server 2008

Custom Log Shipping

Logshipping using Powershell

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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