May 11, 2011 at 4:30 pm
I wish to migrate the databases from MS SQL Server 2008 R2 on server1 to MS SQL Server 2008 R2 server 2 ( both are identical with respect to OS). Can anyone suggest me the best way to do it.
I have roughly figured out the backup/restore to/from disk option, however I am not a practicing DBA so need to know the simplest method.
Thanks
May 11, 2011 at 6:14 pm
I think Backup & Restore would be the simplest method. You would need to resync the logins afterwards.
May 11, 2011 at 10:44 pm
As Homebrew mentioned, you can do Backup\Restore Method which reduced the amount of down time but the easiest approach would be the Detach\Approach.
Check these Links below.
http://www.sqlservercentral.com/articles/Upgrade/65872/
http://www.sql-server-performance.com/articles/dba/migrating_databases_checklist_part1_p1.aspx
http://pollus.blogspot.com/2008/06/easiest-sql-server-instance-migration.html
Thank You,
Best Regards,
SQLBuddy
May 12, 2011 at 3:21 am
i would use backup and restore especially if you dont want much downtime then make sure orphaned logins are fixed.
May 12, 2011 at 4:06 am
And, if existing, remember the SQL Server jobs referencing the moved database.
May 12, 2011 at 7:27 am
IMO the most efficient method (least down time but more work) would be to setup logshipping of the dbs to the new server.
Followed by:
Create the logins on the destination server using sp_rev_logins.
Script and apply SQL agent jobs (powershell script to help below)
Verify there are no external datasources used by ssis and linked servers
At point of migration:
Restore final database logs with RECOVERY
Enable SQL agent jobs as required on new server
Disable jobs on old server
Take databases offline on old server
function Script-AllSQLJobs ([string]$Instance, [string]$Folder)
{
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $Instance
$srv.JobServer.Jobs | foreach {
$filename = $Folder + $_.ToString().replace(":","").replace("/","").replace("""","").replace("\","").replace("*","").replace("?","").replace("<","").replace(">","").replace("|","") + ".sql";
$_.Script() | out-file -FilePath $filename -force
}
}
May 12, 2011 at 9:12 am
Backup/restore is my preferred method, making sure to also grab the MSDB database as it holds all of the maintenance plans and jobs.
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply