Best way to copy a database (SQL Server 2008)

Question

Dumb question - what's the best way to copy instances in an environment where I want to refresh a development server with instances from a production server?

I've done backup-restore, but I've heard detach-copy-attach and one guy even told me he would just copy the datafiles between the filesystems....

Are these the three (or two, the last one sounds kind of suspect) accepted methods?

My understanding is that the second method is faster but requires downtime on the source because of the detach aspect.

Also, in this situation (wanting an exact copy of production on a dev server) what's the accepted practice for transferring logins,etc.? Should I just backup and restore the user databases + master + msdb?

Answer 1

Easiest way is actually a script.

Run this on production:

USE MASTER;  BACKUP DATABASE [MyDatabase] TO DISK = 'C:\temp\MyDatabase1.bak' -- some writeable folder.  WITH COPY_ONLY 

This one command makes a complete backup copy of the database onto a single file, without interfering with production availability or backup schedule, etc.

To restore, just run this on your dev or test SQL Server:

USE MASTER;  RESTORE DATABASE [MyDatabase] FROM DISK = 'C:\temp\MyDatabase1.bak' WITH MOVE 'MyDatabase'   TO 'C:\Sql\MyDatabase.mdf', -- or wherever these live on target MOVE 'MyDatabase_log'   TO 'C:\Sql\MyDatabase_log.ldf', REPLACE, RECOVERY 

Then save these scripts on each server. One-click convenience.

Edit:
if you get an error when restoring that the logical names don't match, you can get them like this:

RESTORE FILELISTONLY FROM disk = 'C:\temp\MyDatabaseName1.bak' 

If you use SQL Server logins (not windows authentication) you can run this after restoring each time (on the dev/test machine):

use MyDatabaseName; sp_change_users_login 'Auto_Fix', 'userloginname', null, 'userpassword';