Copy a database With Script (SQL Server 2008)

Run this on production:

USE MASTER;

BACKUP DATABASE [MyDatabase]
TO DISK = 'C:tempMyDatabase1.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:tempMyDatabase1.bak'
WITH
MOVE 'MyDatabase'   TO 'C:SqlMyDatabase.mdf', -- or wherever these live on target
MOVE 'MyDatabase_log'   TO 'C:SqlMyDatabase_log.ldf',
REPLACE, RECOVERY

Then save these scripts on production, test and dev. One-click convenience.

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:tempMyDatabaseName1.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';

Ref: http://stackoverflow.com/questions/2043726/best-way-to-copy-a-database-sql-server-2008#_=_