Simple Procedure to Make a Copy of a SQL Server Database

7 Comments

1. Also :

   …’ WITH COMPRESSION, INIT, SKIP’ — to override olds backups

2. And compression :

   — Backup the Source Database
   IF @SourceDatabaseName IS NOT NULL
   BEGIN
   SET @tsql = CONCAT (
   ‘BACKUP DATABASE ‘
   ,@SourceDatabaseName
   ,’ TO DISK = ‘
   ,QUOTENAME(@BackupFile, ””)
   , ‘ WITH COMPRESSION’
   )

   …

3. Perfect, thx a lot !

   I just added at the beginning :

   BEGIN

   IF EXISTS(select * from sys.databases where name = @TargetDatabaseName)
   exec(‘ALTER DATABASE ‘ + @TargetDatabaseName + ‘ SET SINGLE_USER WITH ROLLBACK IMMEDIATE’);
   IF EXISTS(select * from sys.databases where name = @TargetDatabaseName)
   exec(‘DROP DATABASE ‘ + @TargetDatabaseName);

   SET NOCOUNT ON […]

4. I made some modifications to the SP – they were minor but when passing the database name, with localDB it is the full file name, not the logical database name. So if the database logical name is ‘myDatabase’, you would pass that as the @SourceDatafileLogicalName but ‘C:\Databases\myDatabase.mdf’ (for instance) as the @SourceDatabaseName. You also have to put the database name in square brackets.in the SP (or in the passed data).both to back up the source and restore the target. I added a parameter @dbType Integer for the conditional IIF, with value 0 being localDB.

   Here is the modified procedure:

   USE [master]
   GO
   /****** Object: StoredProcedure [dbo].[CloneDBUsingBackupRestore] Script Date: 9/30/2022 6:20:57 PM ******/
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   ALTER PROCEDURE [dbo].[CloneDBUsingBackupRestore]
   @SourceDatabaseName VARCHAR(200)
   ,@SourceDatafileLogicalName VARCHAR(200)
   ,@SourceLogfileLogicalName VARCHAR(200)
   ,@BackupFile VARCHAR(2000)
   ,@TargetDatabaseName VARCHAR(200)
   ,@TargetDatabaseFolder VARCHAR(2000)
   ,@dbType Integer

   AS
   BEGIN
   SET NOCOUNT ON

   DECLARE @tsql VARCHAR(2000)
   DECLARE @TargetDataFile VARCHAR(2000)
   DECLARE @TargetLogFile VARCHAR(2000)

   SET @TargetDataFile = @TargetDatabaseFolder + @TargetDatabaseName + ‘.mdf’;
   SET @TargetLogFile = @TargetDatabaseFolder + @TargetDatabaseName + ‘_log.ldf’;

   — Backup the Source Database
   IF @SourceDatabaseName IS NOT NULL
   BEGIN
   SET @tsql = CONCAT (
   ‘BACKUP DATABASE ‘
   IIF(@dbType = 0,'[‘ + @SourceDatabaseName _ ‘]’, QuoteName(@SourceDatabaseName, ””))
   ,’ TO DISK = ‘
   ,QUOTENAME(@BackupFile, ””)
   )

   PRINT ‘Executing query : ‘ + @tsql;

   EXEC (@tsql)
   END

   — Restore database from the Backup File into target Data File and target Log File

   SET @tsql = CONCAT (
   ‘RESTORE DATABASE ‘
   ,IIf(@dbType = 0, ‘[‘ + @TargetDataFile + ‘]’, @TargetDatabaseName)
   ,’ FROM DISK = ‘
   ,QUOTENAME(@BackupFile, ””)
   ,’ WITH MOVE ‘
   ,QUOTENAME(@SourceDatafileLogicalName, ””)
   ,’ TO ‘
   ,QUOTENAME(@TargetDataFile, ””)
   ,’ , MOVE ‘
   ,QUOTENAME(@SourceLogfileLogicalName, ””)
   ,’ TO ‘
   ,QUOTENAME(@TargetLogFile, ””)
   );

   PRINT ‘Executing query : ‘ + @tsql;

   EXEC (@tsql);

   SET NOCOUNT OFF
   END

5. Hi Steve,

   Take a look at this article to see if this helps: https://alteridem.net/2016/03/24/restore-a-sql-server-backup-to-localdb/

   Also, what does the PRINT statement show for the command it generates? I would look at that and compare how to you get the restore to work and then adjust the script to meet your needs.

   -Greg

6. Hi, Eli
   For your stored procedure to run correctly the logical file names have to match the physical file names, so after the sp runs, if you want to copy the copy, it will fail because it has the logical file names of the original database. I had asked about changing the names but found a solution after that which works great:

   USE [master];
   GO
   ALTER DATABASE [Manvendra] MODIFY FILE ( NAME = Manvendra_Data, NEWNAME = Manvendra );
   GO

   My remaining problem is that the stored procedure does not work on localDB instances, where the database name is an attached file with a full file name – ie C:\SQL\Data\myData.mdf. The script throws an error and the backup fails. however, if I pass the logical name (ie, myData) I get “Database ‘myData’ does not exist”. I suspect the same thing would occur on a SQL Express instance.

   How can I modify your script to copy such databases?

   Thanks!
   Steve

7. Thank you for this script – a great tool for a common task that otherwise takes several steps in SSMS!

   How would you modify it if you wanted the logical database/log names to reflect the new file names (ie, if mySQLdb is copied to mySQLdbClone, the logical name of the destination database would be mySQLdbClone)?