Simple Procedure to Make a Copy of a SQL Server Database

By: Eli Leiba   |   Updated: 2016-12-05   |   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

• Check out these other scripts
• Learn about the new database cloning option that only copies schema and statistics
• Review these Backup and Restore resources:
  • SQL Server Backup Tutorial
  • SQL Server Backup Tips
  • SQL Server Restore Tutorial
  • SQL Server Restore Tips

About the author

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