What is the best way to copy a database?

Backup and Restore is the most straight-forward way I know. You have to be careful between servers as security credentials don't come with the restored database.

"Here's a dynamic sql script I've used in the past. It can be further modified but it will give you the basics. I prefer scripting it to avoid the mistakes you can make using the Management Studio:

Declare @OldDB varchar(100)
Declare @NewDB varchar(100)
Declare @vchBackupPath varchar(255)
Declare @query varchar(8000)

/*Test code to implement
Select @OldDB = 'Pubs'
Select @NewDB = 'Pubs2'
Select @vchBackupPath = '\\dbserver\C$\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\pubs.bak'
*/

SET NOCOUNT ON;

Select @query = 'Create Database ' + @NewDB
exec(@query)

Select @query = '
Declare @vBAKPath varchar(256)
declare @oldMDFName varchar(100)
declare @oldLDFName varchar(100)
declare @newMDFPath varchar(100)
declare @newLDFPath varchar(100)
declare @restQuery varchar(800)

select @vBAKPath = ''' + @vchBackupPath + '''
select @oldLDFName = name from ' + @OldDB +'.dbo.sysfiles where filename like ''%.ldf%''
select @oldMDFName = name from ' + @OldDB +'.dbo.sysfiles where filename like ''%.mdf%''
select @newMDFPath = physical_name from ' + @NewDB +'.sys.database_files where type_desc = ''ROWS''
select @newLDFPath = physical_name from ' + @NewDB +'.sys.database_files where type_desc = ''LOG''

select @restQuery = ''RESTORE DATABASE ' + @NewDB +
' FROM DISK = N'' + '''''''' + @vBAKpath + '''''''' +
'' WITH MOVE N'' + '''''''' + @oldMDFName + '''''''' +
'' TO N'' + '''''''' + @newMDFPath + '''''''' +
'', MOVE N'' + '''''''' + @oldLDFName + '''''''' +
'' TO N'' + '''''''' + @newLDFPath + '''''''' +
'', NOUNLOAD, REPLACE, STATS = 10''

exec(@restQuery)
--print @restQuery'

exec(@query)"

Backup and Restore is the most straight-forward way I know. You have to be careful between servers as security credentials don't come with the restored database.

"The Publish to Provider functionality has worked great for me. See Scott Gu's Blog Entry.

If you need something really robust look at redgate software's tools here...if you are doing much SQL at all, these are worth the $$."