0

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.

7
  • 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
    – Shanky
    Jan 24, 2017 at 13:51
  • 1
    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.
    – alroc
    Jan 24, 2017 at 13:59
  • 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/… Jan 24, 2017 at 14:18
  • I'd strongly recommend you reconsider using backups. Are these databases not backed up at all? Jan 24, 2017 at 14:38
  • 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
    – Bob Klimes
    Jan 24, 2017 at 14:38

1 Answer 1

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.