This Is a Striped down version of the script I use to restore a great many databases at the same time. Hopefully I took out all of the parts that make it work for my scenario and left them with appropriate hints as to what to place in your version.
-- Remove old DBs:
use [master]
go
DROP Database [YourDBName]
DROP Database [YourNextDBName]
DROP Database [AndSoOn]
DROP Database [AndSoForth]
GO
-- Create Restore List
Insert into [dbo].[BackupList] (DBName) Values ('YourDBName')
Insert into [dbo].[BackupList] (DBName) Values ('YourNextDBName')
Insert into [dbo].[BackupList] (DBName) Values ('AndSoOn')
Insert into [dbo].[BackupList] (DBName) Values ('AndSoForth')
GO
declare @counter [int]
declare @maxcounter [int]
declare @DBName [varchar](50)
declare @SQL1 [varchar](4000)
declare @SQL2 [varchar](4000)
declare @SQL3 [varchar](4000)
declare @SQL4 [varchar](4000)
set @counter = 0
select @maxcounter = max(id) from [dbo].[BackupList]
while @counter <= @maxcounter
BEGIN
select @DBname = DBname from .[dbo].[BackupList] where ID = @Counter
IF @DBname LIKE '%WhateverYoursIsLike'
BEGIN
SET @SQL1 = 'RESTORE DATABASE '+ QUOTENAME(@DBName) + ' FROM DISK = ''YourBack-UpLocation' + @DBName + '.bak'' WITH FILE = 1,
MOVE N''YourFileName'' TO ''YourRestoreLocation' + @DBName + '.mdf'',
MOVE N''YourFileName_log'' TO ''YourRestoreLocation' + @DBName + '.ldf'''
exec(@sql1)
print(@sql1)
END
set @counter = @counter + 1
END
GO
Use [em_support]
GO
truncate table [em_support].[dbo].[BackupList]
GO
DBCC CHECKIDENT (BackupList, RESEED, 0)
GO
--> Modify DB properties
DECLARE @DBName SYSNAME
DECLARE @Login SYSNAME
DECLARE @SQL NVARCHAR(4000)
DECLARE curModifyProperties CURSOR FOR
SELECT [name]
FROM sys.databases
WHERE [name] LIKE '%WhateverYoursIsLike'
OPEN curModifyProperties
FETCH NEXT FROM curModifyProperties
INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
--> BEG - Set trustworthy property
SET @SQL = 'ALTER DATABASE '+QUOTENAME(@DBName)+' SET TRUSTWORTHY ON'
EXEC (@SQL)
--> END - Set trustworthy property
--> BEG - Set database owner
SET @SQL = QUOTENAME(@DBName)+'.[dbo].[sp_changedbowner] ''sa'''
EXEC (@SQL)
--> END - Set database owner
FETCH NEXT FROM curModifyProperties
INTO @DBName
END
CLOSE curModifyProperties
DEALLOCATE curModifyProperties
GO
I hope that helps or at least points you in the right direction.
Since you're staying with 2008R2 (both source and destination) there is nothing stopping you from just backing up and restoring all of your databases including the system databases. As Shanky pointed out, this would save a great deal of time:
1) Backup and Restore Databases - Is this the best option?
It's an option. If you need the downtime to be less, you can use mirroring or log shipping (or do it by hand) to keep the databases in sync and then migrate during a fast downtime or cutover.
Please note that system databases cannot be log shipped or mirrored and would need to either be frozen or copied right before the cutover.
2) Migrate Logins - use Microsoft KB? kb/918992
If you restore the master database to the new instance, no migration should be needed. All server level logins are stored in the master database.
3) Migrate Credentials/Certificates - what is required for this?
If this is inside of SQL Server, these would be held in their respective databases (and possibly master as well). The one difference would be the service master key (SMK) that would change. You make want to back that up and restore it on the new server if you're relying on automatic key decryption.
4) Migrate SQL Server Agent Jobs - (Object Explorer Details > Select All Jobs > Script Job; Is this process the best option?)
If you restore the msdb system database, all agent jobs will be held in it. There would be no need to script->restore.
5) Migrate SSIS Packages - (How to do this?)
If the SSIS packages are on the filesystem (not default) it would be trivial to create the same location on the new server and copy. By default the SSIS packages are held in the msdb system database and restoring it would get you to the same place, just like #4 and previous.
6) Migrate Database Mail Accounts/Profiles - (Create a script or recreate in SSMS?)
This is also stored in msdb. See #4, #5.
7) Recreate assemblies
These live in their respective databases. If backup and restore is used, this should not be a problem. Any assemblies outside of SQL Server would need copied to the new server.
8) Recreate Linked Servers
These also live in the master database, see #2.
Best Answer
I write this query and this work.