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)

TrackbackPrint

Location: BlogsRamblings, Scriptlets, and more!MSSQL Scriptlets

Your name:
Title:
Comment:
Security Code Enter the code shown above in the box below
Add Comment   Cancel