Exporting/Importing (Migrating) All Non System MSSQL Databases

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:

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:

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