Simple Procedure to Make a Copy of a SQL Server Database

By:   |   Updated: 2016-12-05   |   Comments (5)   |   Related: More > Restore


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?


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;

CREATE PROCEDURE dbo.CloneDBUsingBackupRestore (
 @SourceDatabaseName VARCHAR(200)
 ,@SourceDatafileLogicalName VARCHAR(200)
 ,@SourceLogfileLogicalName VARCHAR(200)
 ,@BackupFile VARCHAR(2000)
 ,@TargetDatabaseName VARCHAR(200)
 ,@TargetDatabaseFolder VARCHAR(2000)

 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
  SET @tsql = CONCAT (
    ,' TO DISK = '
    ,QUOTENAME(@BackupFile, '''')

  PRINT 'Executing query : ' + @tsql;

  EXEC (@tsql)

 -- Restore database from the Backup File into target Data File and target Log File
 SET @tsql = CONCAT (
   ,' FROM DISK = '
   ,QUOTENAME(@BackupFile, '''')
   ,' WITH MOVE '
   ,QUOTENAME(@SourceDatafileLogicalName, '''')
   ,' TO '
   ,QUOTENAME(@TargetDataFile, '''')
   ,' , MOVE '
   ,QUOTENAME(@SourceLogfileLogicalName, '''')
   ,' TO '
   ,QUOTENAME(@TargetLogFile, '''')

 PRINT 'Executing query : ' + @tsql

 EXEC (@tsql)


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
EXEC dbo.CloneDBUsingBackupRestore
   @SourceDatabaseName        = 'Northwind',
   @SourceDatafileLogicalName = 'Northwind',
   @SourceLogfileLogicalName  = 'Northwind_Log',
   @BackupFile                = 'C:\SQL\nw.bak',
   @TargetDatabaseName        = 'NorthwindClone',
   @TargetDatabaseFolder      = 'C:\SQL\'

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

Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights

get scripts

next tip button

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.

View all my tips

Article Last Updated: 2016-12-05

Comments For This Article

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


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


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

Just what I needed.



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

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