Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Simple Procedure to Make a Copy of a SQL Server Database


By:   |   Updated: 2016-12-05   |   Comments (5)   |   Related: More > 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


Last Updated: 2016-12-05


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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

 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 09, 2018 - 8:11:30 AM - JElster Back To Top

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

Just what I needed.

 

Thanks!


Monday, December 05, 2016 - 10:19:32 PM - Bill Back To Top

 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 05, 2016 - 2:02:23 AM - EBV Back To Top

 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.


Learn more about SQL Server tools