detach / attach database to move to new server

Question

I want to move three large databases form old servers to new servers. I an concerned the restore will take a long time, and hoping that detach and attache would be faster. please let me know if my assumption is correct. Also let me know if that will create all the users and roles on the new server as  they were on the old server.

I am aware that I will have to migrate SSIS and SQL agent jobs manually, but, Is there anything else I need to be concerned about if I use detach / attach method.

Thanks.

I am aware that I will have to migrate other components like SSIS, ans QL agent jobs manually.

I am aware that I will have to migrate other components like SSIS, ans QL agent jobs manually.

Answer 1

By far, the best and the recommended way to migrate a database is backup and restore. The drawback to using detach/attach is longer downtime i.e. the database will be down until you copy the database files and attach it on the destination server. 

By backup/restore, you can perform the backup online and restore with 'no recovery' on the destination and when you're ready to do the cutover, switch the source DB to read-only, perform a final Diff/Log backup and restore onto the destination and recover the database. Thus minimizing the downtime. Also, you can tune the backup/restore so it's done quicker. 

If you still want to go the detach/attach route, I'd suggest reading the following article where I mentioned the feature in detail. 

https://social.technet.microsoft.com/wiki/contents/articles/51626.sql-server-all-about-detach-and-attach-operations-and-common-scenarios-where-they-may-or-may-not-work.aspx

and yes, both these methods carry over everything that resides within the DB including roles, users, permissions etc.

---

Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

Answer 2

you can configure log shipping. it will save lot time.

take downtime for cut over time only.

---

https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

Answer 3

Hi kvd,

>> I am aware that I will have to migrate other components like SSIS, ans QL agent jobs manually.
In order to transfer SQL Server agent jobs, you can use the Transfer Jobs task in SSIS to do it. Please refer to Transfer Jobs Task. 

Best regards,
Cathy Ji

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact [e-mail]