Simple Procedure to Make a Copy of a SQL Server Database

By:   |   Comments (12)   |   Related: > Restore


Problem

You want need to create a copy (clone) of a database on the same SQL Server instance. This can be done in several steps, but is there a simpler way of doing this with T-SQL so it can be done in one step?

Solution

My solution is to create a stored procedure (dbo.CloneDBUsingBackupRestore) that does the cloning process. The method used for the cloning is to backup the database and restore to a new database. This will create an exact copy of the database.

The Method

  1. The source database is backed up to a file using dynamic T-SQL execution
  2. The backup is then restored to a new database, using the RESTORE DATABASE WITH MOVE OPTION again using dynamic T-SQL.

The procedure takes 6 parameters (all parameters should be supplied):

  • Source database name
  • Source database logical data file name
  • Source database logical log file name
  • Backup file name and path
  • Destination database name
  • Destination database folder location for database files

Here is the T-SQL procedure source code:

USE master;
GO

CREATE PROCEDURE dbo.CloneDBUsingBackupRestore (
 @SourceDatabaseName VARCHAR(200)
 ,@SourceDatafileLogicalName VARCHAR(200)
 ,@SourceLogfileLogicalName VARCHAR(200)
 ,@BackupFile VARCHAR(2000)
 ,@TargetDatabaseName VARCHAR(200)
 ,@TargetDatabaseFolder VARCHAR(2000)
 )
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 + '.ldf';

 -- Backup the  Source Database 
 IF @SourceDatabaseName IS NOT NULL
 BEGIN
  SET @tsql = CONCAT (
    'BACKUP DATABASE '
    ,@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 '
   ,@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
GO

Example use of procedure

You want to clone the Northwind database to a new database called NorthwindClone.  To get the logical data file name and log file name you can use the sp_helpdb stored procedure as follows:

sp_helpdb 'Northwind'

Then specify the values for each parameter.

use master
go
EXEC dbo.CloneDBUsingBackupRestore
   @SourceDatabaseName        = 'Northwind',
   @SourceDatafileLogicalName = 'Northwind',
   @SourceLogfileLogicalName  = 'Northwind_Log',
   @BackupFile                = 'C:\SQL\nw.bak',
   @TargetDatabaseName        = 'NorthwindClone',
   @TargetDatabaseFolder      = 'C:\SQL\'
GO

The result for the execution is printed in the Messages tab:

Executing query : BACKUP DATABASE Northwind TO DISK = 'C:\SQL\nw.bak'
Processed 544 pages for database 'Northwind', file 'Northwind' on file 2.
Processed 2 pages for database 'Northwind', file 'Northwind_log' on file 2.
BACKUP DATABASE successfully processed 546 pages in 0.317 seconds (13.433 MB/sec).
Executing query : RESTORE DATABASE NorthwindClone FROM DISK = 'C:\SQL\nw.bak' 
      WITH MOVE 'Northwind' TO 'C:\SQL\NorthwindClone.mdf' ,
      MOVE 'Northwind_Log' TO 'C:\SQL\NorthwindClone.ldf'
Processed 544 pages for database 'NorthwindClone', file 'Northwind' on file 1.
Processed 2 pages for database 'NorthwindClone', file 'Northwind_log' on file 1.
RESTORE DATABASE successfully processed 546 pages in 0.315 seconds (13.518 MB/sec).

Now we have an exact copy of the Northwind database named NorthwindClone.

Notes on Use

  1. The procedure was tested with SQL Server 2012 Developer edition on my personal server. My version is: Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86) Feb 10 2012 19:13:17 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
  2. The procedure clones databases with a single data file (mdf) data file and a single log file (ldf)
  3. The logical names for source database data and log file should be supplied as parameters to the procedure.
  4. The target database should not exist prior to the procedure execution.
  5. The target database name should be different from source database name.
  6. The backup file should not exist prior to the procedure execution.
  7. When supplying the target database folder location, it should end with the backslash \ character (see in the example).
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Thursday, September 14, 2023 - 7:55:50 AM - Flou Back To Top (91557)
Also :

...' WITH COMPRESSION, INIT, SKIP' -- to override olds backups

Friday, July 21, 2023 - 9:11:33 AM - Flou Back To Top (91418)
And compression :

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

...

Friday, July 21, 2023 - 8:45:05 AM - Flou Back To Top (91417)
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 [...]

Saturday, October 1, 2022 - 12:27:03 AM - Steve Jensen Back To Top (90540)
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

Friday, September 30, 2022 - 11:53:57 AM - Greg Robidoux Back To Top (90538)
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

Friday, September 30, 2022 - 10:56:16 AM - Steve Jensen Back To Top (90536)
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

Saturday, September 17, 2022 - 1:42:16 PM - Steve Jensen Back To Top (90492)
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)?

Monday, January 14, 2019 - 2:25:14 AM - Syed Back To Top (78761)

 Hi Eli,

I am trying to use suggest sql procedure but i found thing when i am executing in restore database if you run below query

select * from sys.database_files

in Restorte Databse  You can see Name of Database of same as main database which has been backedup...do you thing this will create a issue 

name physical_name

DBNaina C:\SQL\NorthwindClone.mdf

DBNaina_log C:\SQL\NorthwindClone.ldf

Below code i have executed

use master
go
EXEC dbo.CloneDBUsingBackupRestore
   @SourceDatabaseName        = 'DbNaina',
   @SourceDatafileLogicalName = 'DbNaina',
   @SourceLogfileLogicalName  = 'DbNaina_Log',
   @BackupFile                = 'C:\SQL\nw.bak',
   @TargetDatabaseName        = 'NorthwindClone',
   @TargetDatabaseFolder      = 'C:\SQL\'
GO

Tuesday, October 9, 2018 - 8:11:30 AM - JElster Back To Top (77903)

Great!..

How can this be used to copy DBs between 2 differant servers?

thx

Monday, December 12, 2016 - 10:23:36 AM - Matt Sharkey Back To Top (44951)

Just what I needed.

 

Thanks!

Monday, December 5, 2016 - 10:19:32 PM - Bill Back To Top (44903)

 If a reader is thinking about adding EXECUTE AS to CloneDBUsingBackupRestore (for impersonation needs), keep in mind that the impersonated account may be granted more permissions than a security policy allows (in conjunction with the presence of dynamic SQL).  Such security concerns might be addressed by constraining allowable string parameter values (which would need to be checked in CloneDBUsingBackupRestore) and signing CloneDBUsingBackupRestore with a certificate, as suggested by Erlan Sommarskog in "Giving Permissions through Stored Procedures" (http://sommarskog.se/grantperm.html)

Monday, December 5, 2016 - 2:02:23 AM - EBV Back To Top (44899)

 Nice all-in-one script. Your script is a good starting point. 

I would adjust it to make a Copy Only backup so you don't mess the backup chain. Also add a option to place log file on separate location.














get free sql tips
agree to terms