Copy One Database to Another

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

-- 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