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
