Move multiple databases from SQL 2000 to new SQL 2005 server

  • Hi

    We currently have one SQL2000 server we want to retire, and therefor we need to migrate all the databases to a new SQL2005 server.

    Does any one know a simple way of doing this?

    Like

    Get all database names

    Detach the databases

    Move the log and data files

    Attach the databases on the new server

  • There are couple of ways to do this... But first I hope you ran the upgrade adviser on your SQL 2000 Databases that you are planning to migrate.

    1. If you do not want much down time and your server is always busy, set up a warm standby solution for each of these DBs in SQL 2005. (Make sure you have created the logins) Then when it is time to pull the plug, make sure you have restored the last transaction log after stopping all connections to the SQL 2000 DBs. Then restore all the DBs in the SQL 2005 with recovery mode.

    2. Close all connections to SQL 2000 and take a back up and restore to SQL 2005. Make sure you have all the logins pre created.

    3. The method you mentioned, Detach and attach.

    There are probably more ways, and simpler ways but these come to my mind.

    -Roy

  • Hi Roy

    Thanks for your answer

    I was more looking for a script that could detach the databases, as we have 100+ databases we want to move. Manual work would take forever 🙂

    My script for detach/attach in SQL2005, doesnt work for SQL2000, so I thought there might be some with the knowledge to multi detach/attach the databases

  • My script for detach/attach in SQL2005, doesn't work for SQL2000, so I thought there might be some with the knowledge to multi detach/attach the databases

    what error do you get? the script should still work, very possibly just need to change sys.databases to sysdatabases.

    else reverse engineer the detach staements

    select 'exec sp_detach_db ',name from sysdatabases where dbid > 4

    (warning code above not parsed)

    Be sure detach\attach is the way you want to go, rather than backup\restore.

    The following script reverse engineers the attach statements for dbs with up to 16 files, it presumes on the new server directory structure will be the same (which is a good idea if possible). I know it should be 'create database for attach' but hey it works, preserves correct database owner too. It will also allow you to quickly re-attach to the sql2000 server if you have to.

    set quoted_identifier off

    go

    create proc usp_create_attach_db

    as

    --set quoted_identifier off

    set nocount on

    declare

    @dbid smallint,

    @name sysname,

    @statement nvarchar(40),

    @filename nvarchar(80),

    @filename1 nvarchar(80),

    @filename2 nvarchar(80),

    @fileid tinyint,

    @count tinyint

    declare attach_cursor cursor for

    select dbid, name from master..sysdatabases

    open attach_cursor

    fetch next from attach_cursor

    into @dbid, @name

    while @@fetch_status = 0

    begin

    select @filename1 = rtrim(filename) from master..sysaltfiles where fileid = 1 and dbid = @dbid

    select @filename2 = rtrim(filename) from master..sysaltfiles where fileid = 2 and dbid = @dbid

    print 'exec sp_attach_db ' +"'" +@name +"'," +"'" +@filename1 +"',"

    print "'" +@filename2 +"'"

    select @fileid = max(fileid) from master..sysaltfiles where dbid = @dbid

    if @fileid > 2

    begin

    set @count = 3

    while @count !> @fileid

    begin

    select @filename = rtrim(filename) from master..sysaltfiles where fileid = @count and dbid = @dbid

    print ",'" +@filename +"'"

    set @count = @count+1

    end

    end

    select 'sp_changedbowner '+"'"+suser_sname(sid)+"'" from master..sysdatabases where dbid = @dbid

    print ''

    print '-------------------------next database-----------------------------'

    print ''

    fetch next from attach_cursor

    into @dbid, @name

    end

    close attach_cursor

    deallocate attach_cursor

    ---------------------------------------------------------------------

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply