I want all DB’s moved, from SQL Server/Instance A to SQL Server/Instance B. The easiest way to do this of course, is scripted :D. Oh how I love the idea of automation.
Generate export script:
|
1 2 3 4 5 6 7 8 |
use master GO select ' BACKUP DATABASE [' + [name] + '] TO DISK = N''C:\RESTORE\PATH\' + [name] + '.bak'' WITH NOFORMAT, NOINIT, NAME = N''' + [name] + '-Full Database Backup'' , SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO' from master..sysdatabases where dbid > 4 |
Use the output to backup the databases.
Then run the following command to get the import commands you would run on the destination instance:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
declare @cmd1 varchar(500) declare @cmd2 varchar(500) declare @cmd3 varchar(500) declare @pre varchar(500) set @cmd1 = 'insert into #filesinfo (db, dn, dfn ) select ''?'', [name], [filename] from [?]..sysfiles where groupid=1' set @cmd2 = 'update #filesinfo set ln = [name], lfn =[filename] from [?]..sysfiles sf where sf.groupid=0 and #filesinfo.db=''?''' set @cmd3 = 'update #filesinfo set nf = (select count(*) from [?]..sysfiles) where #filesinfo.db=''?''' CREATE TABLE #filesinfo (db sysname, dn sysname, ln sysname null, dfn nvarchar(500), lfn nvarchar(500) null, nf int, sa sysname null) EXEC sp_MSforeachdb @command1=@cmd1,@command2=@cmd2, @command3=@cmd3 set @cmd1 = 'update #filesinfo set sa = (select sl.[name] from master..syslogins sl inner join [?]..sysusers su on sl.sid=su.sid where su.name=''dbo'') where #filesinfo.db=''?''' EXEC sp_MSforeachdb @command1=@cmd1 --EXEC sp_MSforeachdb 'insert into #filesinfo (db) select ''?''' DELETE FROM #filesinfo WHERE db in ('model','master','tempdb','pubs','Northwind','msdb') --SELECT 'backup database ' + db + ' to disk=''' + db + '.bak''' FROM #filesinfo declare @restorepath nvarchar(1024) declare @datapath nvarchar(1024) declare @logpath nvarchar(1024) set @datapath = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\' set @logpath = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\' set @restorepath = N'C:\EdgeWeb\migrate\' select ' RESTORE DATABASE [' + db + '] FROM DISK = ''' + @restorepath + db + '.bak'' WITH FILE = 1 , MOVE N''' + dn + ''' TO N''' + @datapath + db + '.mdf'' , MOVE N''' + ln + ''' TO N''' + @logpath + db + '_log.ldf'' , NOUNLOAD, REPLACE, STATS = 10 GO ' from #filesinfo drop table #filesinfo |
Leave a Reply