migrating DBs from SQL server 2005 to SQL server 2012

Question

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.

• Shanky: You can attach database from SQL Server 2005 directly to SQL Server 2012. What you are referring to is for SQL Server 2000. However make sure you run upgrade advisor and see that it does not points to ant breaking changes
• alroc: Rather than scripting everything out in SQL, you should take a look at the dbatools.io Powershell module and in particular the start-sqlmigration cmdlet.
• Thomas Rushton: Be advised, though, that even this quick-sounding method could take a while if you're upgrading through multiple versions, as the upgrade process can take a while. There's not a lot of documentation around this that I've been able to find, unfortunately, but here's a list of versions: sqlserverbuilds.blogspot.co.uk/2014/01/…
• Gareth Lyons: I'd strongly recommend you reconsider using backups. Are these databases not backed up at all?
• Bob Klimes: You can upgrade from 2005 to 2012 but you need to be on SP4 technet.microsoft.com/en-us/library/ms143393(v=sql.110).aspx

Answer 1

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.