Jun27 Written by:R Teachout
6/27/2008 4:54 PM
Here is a script I wrote to create a script on the "old" SQL server (with all the proper logical file names, etc) so I could then export the databases, and just run a few scripts on the new server, completing a migration.
Be sure to read my other blog entry regarding Migrating your SQL users.. this prevents the orphans!
create table #RES
(db varchar(255),
type_desc varchar(10),
logfilenam nvarchar(1000),
filenam nvarchar(1000),
script nvarchar(1000))
go
Create table #s
(
db varchar(255),
ROWSS varchar(255),
LOGSS varchar(255),
ResultScript varchar(255)
)
GO
EXEC sp_msForEachDb "insert into #RES SELECT '?' as DB, type_desc, name, physical_name,'' as A
FROM sys.master_files WHERE database_id = DB_ID(N'?')"
GO
EXEC sp_msForEachDb "insert into #s SELECT '?' as DB,''as a,'' as b,'' as c
FROM sys.master_files WHERE database_id=DB_ID(N'?') and type_desc=N'LOG'"
GO
DECLARE @SOURCEFOLDER VARCHAR(1000)
-- Source where the .bak files will be when you run the restore on the new server
-- All DB files should be in this folder, named like: databasename.bak
SET @SOURCEFOLDER = 'D:\Migration'
update #RES set script = 'RESTORE DATABASE ['+[db]+'] FROM DISK = N"'+@SOURCEFOLDER+'\'+[db]+'.bak" WITH FILE = 1, MOVE N"'+[logfilenam]+'" TO N"'+[filenam]+'"'
where type_desc='ROWS'
GO
update #RES set script = ', MOVE N"'+[logfilenam]+'" TO N"'+[logfilenam]+'", NOUNLOAD, REPLACE, STATS = 10'
where type_desc='LOG'
GO
update #s set ROWSS=#RES.script
from #s join #RES on #s.db=#RES.db
where type_desc='ROWS'
GO
update #s set LOGSS=#RES.script
from #s join #RES on #s.db=#RES.db
where type_desc='LOG'
GO
DECLARE @DEST VARCHAR(2000),@SOURCE VARCHAR(2000)
-- To replace paths in case they changed between one server and the next
SET @SOURCE = 'D:\MSSQL.1\MSSQL\DATA'
SET @DEST = 'D:\MSSQL.1\MSSQL\DATA'
update #s set ROWSS=replace(ROWSS,@SOURCE,@DEST)
update #s set LOGSS=replace(LOGSS,@SOURCE,@DEST)
GO
set quoted_identifier off
select replace(ROWSS+LOGSS,'"',CHaR(39)) as ResultScript from #s
where db not in ('Master','Model','msdb','tempdb')
go
Drop table #RES
drop table #s
Now, save the results to a txt file, you would run this on the new server when you are ready and all the databases will be re-created. (again, read the other blog about transferring the users, and do that first)
(See other blog entry for a similar script that works on SQL2000)
Tags: