-- The two lines below here display a script to do automatic one-time backups of ALL databases and tran logs of every db on the server for a migration
-- Uncomment these two lines, and comment out the two below if you just want to make the script to do this work
--exec sp_MSforEachDb 'IF DB_NAME((SELECT dbid FROM sysdatabases WHERE [Name] =''?'')) <> ''tempdb'' PRINT ''BACKUP DATABASE [?] TO DISK =N''''D:\Migration\?.bak'''' WITH NOFORMAT, NOINIT,NAME = N''''?-Full Database Backup for Migration'''', SKIP, NOREWIND, NOUNLOAD'''
--exec sp_MSforeachDB 'IF DATABASEPROPERTYEX(''?'', ''RECOVERY'') <> ''SIMPLE''PRINT ''BACKUP LOG [?] TO DISK = N''''D:\Migration\?.trn'''' WITH NOFORMAT, INIT, NAME = N''''?-Transaction Log Backup for Migration'''', SKIP, NOREWIND, NOUNLOAD'' ELSE PRINT ''BACKUP LOG [?] WITH TRUNCATE_ONLY'''
go
-- The two lines below here run automatic one-time backups of ALL databases and tran logs of every db on the server for a migration
-- This runs the script immediately, if you just want the script, comment out these two and run the two above.
exec
sp_MSforEachDb 'IF DB_NAME((SELECT dbid FROM sysdatabases WHERE [Name] = ''?'')) <> ''tempdb'' BACKUP DATABASE [?] TO DISK =N''D:\Migration\?.bak'' WITH NOFORMAT, NOINIT, NAME = N''?-Full Database Backup for Migration'', SKIP, NOREWIND, NOUNLOAD'exec
sp_MSforeachDB 'IF DATABASEPROPERTYEX(''?'', ''RECOVERY'') <> ''SIMPLE'' BACKUP LOG [?] TO DISK = N''D:\Migration\?.trn'' WITH NOFORMAT, INIT, NAME = N''?-Transaction Log Backup for Migration'', SKIP, NOREWIND, NOUNLOAD ELSE BACKUP LOG [?] WITH TRUNCATE_ONLY'go
Source: http://www.lazydba.com/sql/1__44929.html