SQL: Copy/Paste a database...

...could be a handy function.

Sometimes your test data gets corrupted or outdated i.e. useless in a quality context. If so make sure to have a set of backups with good valid test data. If the backup size is huge (deidentified production copies for example) it could be hours in performing backup/restore...so in order to skip the waiting part of the backup to finish you can do the backup/restore using one SQL script like this:

/* Copy a database to another on the same db server */

declare @databaseFrom varchar(100)
declare @databaseTo varchar(100)
declare @databaseCopyName varchar(500)
declare @randomNumber varchar(50)
declare @date varchar(50)
set @date = Replace(Replace(getDate(),' ','_'),':','_')

-- *** User input ************************
-- Database to copy
set @databaseFrom =    'MyFavoriteDataBase'
-- Target database 
set @databaseTo =    'OtherDataBase'
-- ***************************************

--Perform backup and restore (Do not forget to remove all connections on target database prior restore!)
set @randomNumber = Round((RAND()*1000000),0)
set @databaseCopyName = '\\myServer\myDrive$\myFolder\'+@databaseFrom+'_'+@date+'_'+@randomNumber

Print 'Database ' + @databaseFrom + ' will be saved as: ' + @databaseCopyName

declare @databaseCopyBackupName varchar(500)
Set @databaseCopyBackupName = @databaseFrom+'-Full Database Backup'

BACKUP DATABASE 
@databaseFrom
TO  DISK = 
@databaseCopyName
WITH NOFORMAT, NOINIT,  NAME = 
@databaseCopyBackupName,
SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Print 'Database ' + @databaseFrom + ' were saved as: ' + @databaseCopyName

Print 'Starting restore to ' + @databaseTo

RESTORE DATABASE @databaseTo FROM  
DISK = @databaseCopyName
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10

Print 'Restore of ' + @databaseCopyName + ' to ' + @databaseTo + ' is done!'

GO