moving databases from SQL2000 to SQL2005 without loss of service.

[ranj]

Hi All

 

You will have to excuse my lack of knowledge with SQL but I have been tasked with retirting some of our oldest servers which are currently still used in production. Most of them are fine but I have hit an Windows 2000 Server with SQL 2000 and one of the databases is for our intraserver. I need to devise a plan to move this database over to an SQL 2005 or 2008 server.

 

Any ideas on where I should start.

 

Also as its a really old server I think there could be other db running on there. Does anyone know how I can find out what other databases are running on that server. If I go to SQL server enterprise manager, go to databases they are absolutely loads of db's but I have been told that most of them have been migrated over to other servers since.

 

I went to current activity and checked the locks/object section and this I think is showing me what databases are currently used because I am assuming if they are locked it means that they are in use.

 

Does anyone know of a better way?

 

Any sites/books others would recommend for a newbie DB administrator it would be greatly appreciated. BTW these servers are VM's on a VMware ESX4 cluster.

 

Thanks

[jinnantonnixx]

The good news is that you can backup the databases and restore to the new server as many times as you like without affecting the live system. The current, live SQL service will only disappear when you shut it down.

 

A common gotcha is forgetting to migrate the SQL logins to the new server.

 

This is a good guide for the task. Read it thoroughly; it's useful.

If you're just testing, ignore the part about setting the database to single-user mode. Doing this will disrupt the service. You don't need to do this when you create a backup for your test.

http://www.dell.com/downloads/global/solutions/public/white_papers/SQL_2000_SQL_2008_Migration_Guide.pdf

Edited October 5, 2011 by jinnantonnixx