SQL 2000 to 2005 Migration Script for databases

This needs to be run from SQL 2005 instance with a linked server named link2000
linking to the 2000 instance. The 'RPC Out' option for the linked server needs to be set
to TRUE to allow stored procedures to be executed remotely.

For the 2005 instance, the script requires -
( 1 ) USP_KillAllConnections to be present in master database
( 2 ) USP_FixUsers to be present in the master database
( 3 ) Full control over the path specified in @backupdirectory parameter

Usage :
( 1 ) Change the value of @chvDataBaseName on line 10 to the database to be migrated and the directory locations in lines 11, 12 & 13
( 2 ) Execute script from the 2005 instance the restores are occuring to.

declare @chvDataBaseName varchar(100)
declare @sql nvarchar(2000)
declare @output nvarchar(2000)
declare @backupdirectory nvarchar(200)
declare @newdatadirectory nvarchar(200)
declare @newlogdirectory nvarchar(200)
declare @logicaldataname nvarchar(200)
declare @logicallogname nvarchar(200)

set @chvDataBaseName = 'TestDataBaseToRestore'
set @backupdirectory = '\\server\share$\TEMP2005MIGRATIONSTORE\'
set @newdatadirectory = 'H:\Penfold\'
set @newlogdirectory = 'H:\Penfold\'

-- backup on remote server
print '------------'
print 'start backup'
print '------------'
 set @sql =       N' BACKUP DATABASE ' + @chvDataBaseName  
 set @sql = @sql + ' TO DISK = N''' + @backupdirectory
 set @sql = @sql + @chvDataBaseName + '.BAK'' WITH NOFORMAT, INIT,'
 set @sql = @sql + ' NAME = N''Full Database Backup'',' 
 set @sql = @sql + ' SKIP, NOREWIND, NOUNLOAD, STATS = 10'
 print @sql
execute link2000.master.dbo.sp_executesql @sql
print '----------'
print 'end backup'
print '----------'

-- kill connections on remote (if db exists)
print '----------------'
print 'kill connections'
print '----------------'
 set @sql = N'dbo.master.USP_KillAllConnections ' + @chvDataBaseName + ''
execute master.dbo.sp_executesql @sql


-- restore on local server
print '-------------'
print 'start restore'
print '-------------'

-- fetch filenames for restore

-- use temp table to hold output of restore file list
if object_id('tempdb..#filelist') is not null 
 begin
 drop table #filelist
 end

create table #filelist
(LogicalName nvarchar(128) 
,PhysicalName nvarchar(260) 
,Type char(1) 
,FileGroupName nvarchar(128) 
,Size numeric(20,0) 
,MaxSize numeric(20,0)
,Fileid tinyint
,CreateLSN numeric(25,0)
,DropLSN numeric(25, 0)
,UniqueID uniqueidentifier
,ReadOnlyLSN numeric(25,0)
,ReadWriteLSN numeric(25,0)
,BackupSizeInBytes bigint
,SourceBlocSize int
,FileGroupId int
,LogGroupGUID uniqueidentifier
,DifferentialBaseLSN numeric(25,0)
,DifferentialBaseGUID uniqueidentifier
,IsReadOnly bit
,IsPresent bit 
)

set @sql = 'RESTORE FILELISTONLY FROM DISK = N'''+ @backupdirectory + @chvDataBaseName + '.BAK'''
insert into #filelist
exec (@sql)

select @logicaldataname = logicalname
  from #filelist
 where Type = 'D'

select @logicallogname = logicalname
  from #filelist
 where Type = 'L'

set @sql =        ' RESTORE DATABASE ' + @chvDataBaseName  
set @sql = @sql + ' FROM DISK = N'''+ @backupdirectory + @chvDataBaseName + '.BAK'''
set @sql = @sql + ' WITH REPLACE ,'
set @sql = @sql + ' MOVE ''' + @logicaldataname + ''' TO ''' + @newdatadirectory + @chvDataBaseName + '.mdf' + ''','
set @sql = @sql + ' MOVE ''' + @logicallogname + ''' TO ''' + @newlogdirectory + @chvDataBaseName + '.ldf' + ''''
print @sql
EXEC(@sql)
print '-----------'
print 'end restore'
print '-----------'

print '------------------'
print 'change owner to sa'
print '------------------'
set @sql = 'exec '+ @chvDataBaseName + '.dbo.sp_changedbowner ''sa'' '
print @sql
execute master.dbo.sp_executesql @sql

print '--------------'
print 'add datareader'
print '--------------'
set @sql = 'exec '+ @chvDataBaseName + '.dbo.sp_addrolemember ''db_datareader'',''applicationuser'' '
print @sql
execute master.dbo.sp_executesql @sql

print '--------------'
print 'add datawriter'
print '--------------'
set @sql = 'exec '+ @chvDataBaseName + '.dbo.sp_addrolemember ''db_datawriter'',''applicationuser'' '
print @sql
execute master.dbo.sp_executesql @sql

print '--------------'
print 'reattach users'
print '--------------'
set @sql = 'exec '+ @chvDataBaseName + '.dbo.USP_FixUsers'
print @sql
execute master.dbo.sp_executesql @sql

print '-------------------'
print 'alter schema to dbo'
print '-------------------'
set @sql = 'exec '+ @chvDataBaseName + '.dbo.sp_MSforeachtable "PRINT ''? modify''; ALTER SCHEMA dbo TRANSFER ?; IF @@ERROR = 0 PRINT ''? modified''; PRINT ''''" '
print @sql
execute master.dbo.sp_executesql @sql

print '--------------------------------------'
print 'report unmapped users - should be none'
print '--------------------------------------'
set @sql = 'exec '+ @chvDataBaseName + '.dbo.sp_change_users_login ''report'''
print @sql
execute master.dbo.sp_executesql @sql