Sql-server – Attach/detach vs. backup/restore

backuprestoresql serversql-server-2008

I need to transfer the database (as a whole) to another server, to make a duplicate database to setup another test environment.

I have two choices:

  1. Make a full backup at source server/restore at destination server;
  2. Detach at source server/attach at destination server.

What are the pros and cons of the two solutions according to my requirements?

I am using SQL Server 2008 Enterprise.

Best Answer

Backup/restore should normally be your method of choice. It will be quicker in most situations.

You can use it consistently, also for production to test too.

See this related question as well, where the backup/restore vs detach/attach is mentioned:

SQL Server Migration restore backup vs copy data and log files

Make sure you add the WITH COPY_ONLY option to the backup so it does not break the existing maintenance plan backup chain.

Related Question