May 28, 2009 at 10:07 am
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
May 28, 2009 at 11:47 am
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
May 29, 2009 at 1:30 am
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
May 29, 2009 at 5:23 pm
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