Sql-server – migrating DBs from SQL server 2005 to SQL server 2012

sql serversql-server-2005sql-server-2012upgrade

We are planning on migrating from SQL server 2005 to SQL server 2012,the DB's are huge and cannot be backed up so we are doing a detach and attach method. I read online SQLServer 2005 to 2012 cannot be updated straight away. I think we need to upgrade to 2008r2 and then upgraded to 2012 not sure if it is the best practice . I got the users & permission scripted out . What are the best practices to follow to have a smooth upgrade without any issues.

Best Answer

Depending on the edition since the database size is big, you can look into

  • Logshipping - set up with full backup and subsequent log backups. During the cutover, just take the final tail log backup and restore the 2012 server databases with RECOVERY.
  • Mirroring - Set up as async and during cutover, change the mode to SYNC and failover.

Once the failover is done, remove logshipping or mirroring.

I agree with alroc that the dbatools.io is extremely useful, but in your situation you are better off going with above and separately use the cmdlets for migrating jobs, logins.

For more detailed steps, refer to my answer here.

Related Question