I am using SQL Server 2008 Enterprise. I need to transfer the database (as a whole) to another server (to make a duplicate database to set up another test environment).
I have two choices, (1) making a full backup at source server/restore at destination server; (2) making detach at source server/attach at destination server.
Any pros and cons compared of the two solutions according to my requirements?
thanks in advance,
George
Best Answer
I shudder at the thought of detaching a database, copying its files to a different location, and then reattaching it on the production server (and attaching the copy on the test server). Backup is designed to provide a complete copy of the database without any downtime. It also doesn't introduce opportunity for the source database's files to be accidentally moved/deleted/damaged, which I've seen happen several times (due to user error -- typo or mouse slip) during detach/attach operations. Also, transferring the database+logs could potentially result in a much larger transfer file size than just a backup.
Just make a backup, and then copy/move the backup file to the destination server. It's much safer to do that, from the production standpoint.