attach/detach vs. backup/restore

Solution 1:

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.

Solution 2:

Hopefully a DBA can chime in here as I am not a DB guy. But I do know that I have ran into schema related issues doing detatch/attach rather then backup/restore. I always think the safer route is to backup and restore.

Solution 3:

Detach/attach is potentially faster, depending on your recovery model and whether or not you're using compression in your backup. The detach and attach are nearly instant whereas it takes a while to write the backup file on the source server and then to write the db files on the destination server.

Solution 4:

Backup and restore will lower the downtime on your source server while detach and attach will be faster overall since you won't need first to copy the backup to the target system and restore it (unless you're using a physical media to transfer the backup file from one server to the other).

I'd still go with backup/restore: it's simple to use and understand and uptime is always a desirable property on a production system. Additionally, you can use that to validate your backup strategy by pulling a regular backup for the restore instead or creating a new one.

Once the DB has been mounted, you will still need to go through permissions and schema to adjust them to the new location. In both case, you might need to transfert any and all keys used for encryption before attempting to restore the data.