SQL Server 2008 - moving data from SQL Server 2000

Question

Our company is getting ready to move next week and we currently use SQL Server 2000 for all our databases, hosted in our own building. They've decided to move the data to a local company for several reasons, but they are running SQL Server 2008.

I'd like advice on a few things:

1. We are trying to setup a test for just a few of our databases to see what breaks with our apps (connection strings, etc.). What is the best way to get a database from our SQL Server 2000 box to the new box without losing data or having to recreate the table, etc. I tried the DB Copy Wizard but restrictions within our network don't allow it. If I create a DB on SQL Server 2008 and perform a RESTORE from the SQL Server 2000 .bak file, I will lose all the transactions, correct? What if I replace the transaction files later? I'm thinking that won't work though.

2. How to properly upgrade a SQL Server 2000 database to SQL Server 2008? I've run the wizard against most of our databases and it came up clean with the exception of a few minor issues that I can resolve.

Our SQL Server 2000 is our production server so it can't go down at any point to copy files.... not until the move. Our customers have been notified that there will be a short outage period between certain dates so that's ok, but I guess what I'm saying is I can't stop the SQL Server Agent right now just to copy log files and such for testing purposes.

Any help/advice is greatly appreciated!

Answer 1

Create a database backup of the SQL Server 2000 and restore it to the SQL Server 2008.

Setup replication making the SQL Server 2000 as the Publisher and the SQL Server 2008 as the Subscriber.

Answer 2

Just back up 2000, and then restore it. You won't lose transactions, no difefrent to doing a full backup on your current server.

You'll need to add any SQl logins and set the permissions etc. You'll need to to do fix users for them as well. If you only use integrated access then you don't need to.

e.g.

Use MyDataBase
EXEC sp_change_users_login Auto_Fix, 'MyUser'

And you'll need to set the compatibility level

Use
exec sp_bcp_dbcmptlevel 'model'
to get it
and then

EXEC sp_dbcmptlevel 'MyDatabase', ??

to set it.

Another option, would be to install 2000 on your new machine restore a back from the old one and then do an inplace upgrade through the 2008 installation wizard. That sort of thing tends to give my sphincters palpitations though.

Answer 3

Not sure which would be the best approach. I think a DB backup and restore would be a good idea. You will loose some data in the mean time, though.

If you were migrating between 2 versions of 2005 or 2008 I would advise a log backup to reduce the data lost but I don't think that this option is available on 2000

One piece of advice I can give you is to take a look at the discontinued features from 2000 and 2005 and from 2005 and 2008 on these links:

• 2005
• 2008