locked
attach/detach? or to backup/restore? RRS feed

  • Question

  • Hi experts,

     

     

    2 questions…

     

    I am migrating from sql server 2012 to sql server 2016. (side by side windows server 2012 datacenter to windows server 2016 standard)

     

    1-      What is better to attach/detach? or to backup/restore? (what are pros and cons?)

    2-      I have found this really really useful article http://www.sqlservercentral.com/scripts/Database+Migration/121942/   but it’s a bit outdated, doesn’t talk about sql server 2016, does it apply as well? or can anyone provide a detailed explanation like that one?

    Thanks!

     

     

     

    Friday, June 16, 2017 10:51 AM

Answers

  • Backup and restore is preferred because a detach and reattach may not close the database files completely correctly.  A backup with verification is guaranteed.

    When doing this you have to keep fail back in mind. If you detach your files and re-attach them from the same location you will not be able to go back as the database files will be upgraded.

    It is considered to be a best practice to copy the files. If they are damaged during or after a move operation you will have nothing to fail back to.

    A detach and reattach will likely be faster than a backup and restore.

    Friday, June 16, 2017 11:58 AM

All replies

  • Backup and restore is preferred because a detach and reattach may not close the database files completely correctly.  A backup with verification is guaranteed.

    When doing this you have to keep fail back in mind. If you detach your files and re-attach them from the same location you will not be able to go back as the database files will be upgraded.

    It is considered to be a best practice to copy the files. If they are damaged during or after a move operation you will have nothing to fail back to.

    A detach and reattach will likely be faster than a backup and restore.

    Friday, June 16, 2017 11:58 AM
  • I will recommend, backup and restore only. the reasone is attach/detach may break few this, I dont have KB article saved with me. Here is the info in my note

    So why exactly is this important, and what difference does it really make? Well, there are a number of things that can be affected by the use of attach/detach that are not affected when using ALTER DATABASE. For example if your database uses Service Broker, by using detach/attach, Service Broker is disabled on the database, whereas when using ALTER DATABASE MODIFY FILE, Service Broker remains enabled. To re-enable Service Broker for the database requires exclusive access to the database, which means that you will have to kick any active connection out of the database to use ALTER DATABASE ENABLE BROKER, once you realize that there is a problem. In addition, if you have enable TRUSTWORTHY for the database for SQLCLR or cross database ownership chaining, this is disabled using attach/detach where it is not using ALTER DATABASE MODIFY FILE. The reason for this is security. When you attach a database, it may not be from a trusted source, and for this reason, TRUSTWORTHY is always disabled upon attaching the database making it necessary for a DBA to reset this flag marking the database as trusted.

    While it is possible to still move a database to a different file system location using detach/attach, there are potential unplanned consequences to doing so. For expedience and stability of your application/database, ALTER DATABASE should be the preferred method of moving the database inside of the same SQL Instance.



    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    Friday, June 16, 2017 12:30 PM
  • I prefer backup and restore because it is the safer method for most situations involving user databases migrations between servers, in my experience.

    How to move databases between computers that are running SQL Server

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    Friday, June 16, 2017 1:32 PM