CODEWITHSUNDEEP
sqlsql-server
Craig
Craig

Reputation: 36816

Moving a SQL Server database to a new server

Whenever I move a SQL Server database to a new server I always to a database backup and restore. I have seen a lot of people, especially DBA's who will to a detach / re-attach of the MDF file instead. What is the preferred method and why? I find a backup/restore to be safer, less likely hood of corruption.

Upvotes: 2

Views: 327

Answers (1)

Wayne Brantley
Wayne Brantley

Reputation: 694

I just did this. For our small databases, I did a backup/restore - just because I felt like it was 'safer'. However, when moving LARGE databases, it is so much faster to just detach, copy and attach. This beats having to do a (usually slow) backup, followed by a copy and a (usually slow) restore.

Microsoft recommends using alter database 'planned relocation procedure'.

Note, you can use attach/detach to upgrade between SQL versions as shown in the link.

Upvotes: 7

Related Questions