Re: Upgrade 500 databases from SQL 2000 to SQL 2005

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

q_test wrote:
I have a SQL Server 2000 server with 5 instances and about 500
databases in total. Now I need to upgrade all of them to SQL Server 2005 on
Windows 2003, using side-by-side upgrade, which means, the existing
SQL 2000 software and databases will still be running. I will install
SQL 2005 on the same machine and move a few databases in each
instance for testing. When the test is successful, I will migrate all
to SQL Server 2005.

So the questions are:

1. Can I detach a database from SQL Server 2000 and re-attach to SQL
Server 2005? If this is sufficient, I can write a script to process
all 500 databases.

2. Do I need to re-map database user account after migration to SQL
server 2005? It is still on the same machine, but different instances.

3. Any other suggestions/best-practice to upgrade in a hosting
environment?
Thanks a lot.


You can use the following:

Copy Database Wizard - SQL Server Management Studio, in Object Explorer, expand Databases, right-click a database, point to Tasks, and then click Copy Database.

BACKUP/RESTORE

If you don't mind taking the databases offline, you can use sp_detach_db on the SQL 2000 source databases, copy the database and log files, and then use CREATE DATABASE... FOR ATTACH on SQL 2005.

--
David Gugick
Quest Software
www.quest.com 

.

Relevant Pages

  • Re: Create SQL cluster on 2003
    ... Since you are in a cluster configuration, ... Microsoft SQL Server MVP ... We only about about 20 databases - all ... Move Databases Between Computers That Are Running SQL Server ...
    (microsoft.public.sqlserver.clustering)
  • Re: SQL Server 2005 and file systems, any recommendations
    ... I had to travel to customer to perform a triple upgrade: ... Had the databases been on plain files, the OS upgrade would have been ... since buffering in the file system could cause problems. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Help!! Trying to migrate data from one SQL to another?!?
    ... I came up to the window on the DTS Wizard which has the option to ... I can defend myself on PL/SQL but I am not much of a SQL Server ... The application performs a replication between databases but due to ...
    (microsoft.public.sqlserver.dts)
  • Re: Side-by-side upgrade - moving master, msdb and model
    ... "In some cases, you may want to copy the system databases, including the ... from the source SQL Server 2000 instance to the SQL ... System Databases" in SQL Server 2005 Books Online to see how to do this" ... MVP - Windows Server - Clustering ...
    (microsoft.public.sqlserver.clustering)
  • Re: Side-by-side upgrade - moving master, msdb and model
    ... Microsoft SQL Server MVP ... MVP - Windows Server - Clustering ... from the source SQL Server 2000 instance to the SQL ... > 2005 instance before transferring user databases. ...
    (microsoft.public.sqlserver.clustering)