it's a common thing to need to re-flash a database with a copy from another; taking a fresh copy of a production database and putting it into a dev/test environment for example. As long as both databases are connected to the same instance then going from one to another is a quick and easy exercise, in fairness even if the databases are connected to different instances it's not particularly difficult, it's just a case of copying out the bak file.
Here's some code to do the job:
As mentioned in the comments there are a few bits of information to ensure are set before running and obviously this could be adapted easily into a stored procedure so that you don't have to lug the code around
-- Restore From Backup declare @BackUp as integer = 0 declare @SourceDBName as varchar(50) = '[PPM-Migration]' -- Name for backup file declare @FileName as varchar(500) = convert(varchar, getdate(), 12) + '_Migration_Backup.bak' -- This is relative to the instance server (i.e. ppm-profund2) and sets where the backupfile will be saved --declare @FilePath as varchar(500) = 'F:\Program Files\Microsoft SQL Server\MSSQL10_50.DBCOPIES\MSSQL\Backup\' declare @FilePath as varchar(500) = '\\ppm-app01\Backups\' declare @Restore as integer = 1 declare @DestDBName as varchar(50) = '[PPM-Calcs]' -- data directory to restore the backup file to, the destination database data should be kept here declare @SSMSDataDir as varchar(500) = 'F:\Program Files\Microsoft SQL Server\MSSQL10_50.PROFUND2\MSSQL\DATA\' Declare @SetDB as varchar(500) = 'Use ' + @SourceDBName Declare @BackupSQL as varchar(500) = 'BACKUP DATABASE ' + @SourceDBName + ' TO DISK = ''' + @FilePath + @FileName + ''' WITH FORMAT, MEDIANAME = ''SQLServerBackups'', NAME = ''Full Backup of ' + @SourceDBName + ''';' if @BackUp = 1 begin exec (@SetDB) exec (@BackupSQL) end declare @RestoreToSQL as varchar(max) = ' ALTER DATABASE ' + @DestDBName + ' SET SINGLE_USER WITH ROLLBACK AFTER 10 --give current connections some time to complete --Do Restore RESTORE DATABASE ' + @DestDBName + ' --Path to backup, change as required FROM DISK = ''' + @FilePath + @FileName + ''' WITH REPLACE, MOVE ''Standard_Data'' TO '''+ @SSMSDataDir + @DestDBName + '.mdf'', MOVE ''Standard_Log'' TO '''+ @SSMSDataDir + @DestDBName + '.ldf'' /*If there is no error in statement before database will be in multiuser mode. If error occurs please execute following command it will convert database in multi user.*/ ALTER DATABASE ' + @DestDBName + ' SET MULTI_USER ALTER DATABASE ' + @DestDBName + ' SET NEW_BROKER WITH ROLLBACK IMMEDIATE; ' if @Restore = 1 begin exec ('use master') exec (@RestoreToSQL) end