Data base migration 1

[bertrandkis]

Hi all, I have a SQL 2000 box with 60 databases and I need to move all the databases to a new sql 2005 box. What is the quickest way to do that?

 

[ptheriault]

Do the databases need to be running while you are migrating them? The quickest way would be to detach them, copy and attach. But if they are production databases and need to accessed by the users till the 2005 server is ready I would suggest restoring from backups.
Here is a procedure I found that can detach and copy your databases.

CREATE PROCEDURE sp_detach_and_copy_all_dbs
	(
		@targetdir varchar(260)
	)
AS

declare @filename varchar(260)
declare @dbname sysname
declare @shellcmd varchar(260)
declare @beforecount integer
declare @aftercount integer
declare @tmpvarchar varchar(260)


/*
** Count existing databases, make sure we end up with what
** we started.
*/

set @beforecount= (select COUNT([name])  from sysdatabases)

print @beforecount


/*
** Create a cursor, iterate through all records.
*/

declare dbc cursor for
select [name], [filename]  from sysdatabases

open dbc
fetch dbc into @dbname, @filename
while @@fetch_status >= 0
begin

 /*
 **  Skip over system db's since we wouldn't want to copy them,
 **  even if we could.
 */

 if @dbname in ('master',  'msdb', 'tempdb',  'pubs', 'model')
 begin
  print '(skipping system db)'
 end
 else
 begin
  print @dbname

  /*
  ** Prepare a shell command string to copy the file, after
  ** it's detached.
  */

  set @shellcmd = 'copy ' + @filename + ' ' + @targetdir


  /*
  **  Make sure we are able to detach the database.
  */

  exec sp_certify_removable @dbname, 'auto'
  if @@error = 0
  begin
   exec sp_detach_db @dbname
  end
  else
  begin
   set @tmpvarchar = 'FAILED to  detach ' + @dbname
   raiserror (@tmpvarchar, 11, 11)
  end

  if @@error = 0
  begin
   exec xp_cmdshell @shellcmd


   /*
   ** This could be more elegant, it would be better to
   ** enumerate the contents of @dbname.dbo.sysfiles, create
   ** an argument list, and call sp_attach_db (which accepts
   ** a variable number of args, up to 16 file names.)
   **
   ** In practice you could usually get away with expecting
   ** two files (a db and a transaction log), but that has a
   ** chance of failing, and you'd be better off using
   ** sp_attach_single_file_db, rather than trying to attach
   ** with an incomplete list.
   */

   exec sp_attach_single_file_db @dbname, @filename

   if @@error <> 0
   begin
    set @tmpvarchar = 'Attach FAILED:'
    + CAST(@dbname AS varchar)
    + ' (' + @filename + ')'
    raiserror (@tmpvarchar, 11, 11)
   end
  end
 end

 fetch dbc into @dbname, @filename
end
deallocate dbc

set @aftercount= (select count([name]) from sysdatabases)

if @aftercount <> @beforecount
begin
 set @tmpvarchar = 'database count different, was: '
 + CAST(@beforecount AS varchar) + ' is: '
 + CAST(@aftercount AS varchar)

 raiserror (@tmpvarchar, 11, 11)
end

return 0

- Paul
- Database performance looks fine, it must be the Network!

 

[Catadmin]

Quickest isn't always best, BTW. You want to back up all your DBs or only move copies of the data/log files to SQL 2k5 to make sure you don't accidently corrupt or ruin anything.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"