The following T-SQL sequence will make a copy of a database using the BACKUP and RESTORE commands:
backup database mydb to disk = 'c:\myfile.dat'
restore database mydbcopy from disk = 'c:\myfile.dat'
with move 'mydb_dat' to 'c:\my_dbcopy.mdf'
, move 'mydb_log' to 'c:\my_dbcopy.ldf'
The magic names referenced in the MOVE...TO clauses are logical filenames. You can determine these by running sp_helpfiles in the source database (or querying the sysfiles table). The following commands will do the same thing using file copies instead:
exec sp_detach_db @dbname='mydb', @skipchecks='true'
exec master..xp_cmdshell 'copy c:\mydb.mdf c:\mydbcopy.mdf'
exec master..xp_cmdshell 'copy c:\mydb_log.ldf c:\mydbcopy_log.ldf'
exec sp_attach_db @dbname='mydb', @filename1='c:\mydb.mdf', @filename2='c:\mydb_log.ldf'
exec sp_attach_db @dbname='mydbcopy', @filename1='c:\mydbcopy.mdf', @filename2='c:\mydbcopy_log.ldf'
This is slightly messier, but runs a bit faster (say, 40%).
