SlashGeek » Blog Archive » How to duplicate a SQL database

How to duplicate a SQL database

November3

How to copy a SQL Server database to the same server instance

You would think creating a copy of a database on the same SQL Server server instance would be a point-and-click management task but it isn’t. Here’s a link to the original article. I’ve reposted the code as part of the extended entry in case the original link doesn’t work.

USE master
GO

-- the original database (use 'SET @DB = NULL' to disable backup)
DECLARE @DB varchar(200)
SET @DB = 'PcTopp'

-- the backup filename
DECLARE @BackupFile varchar(2000)
SET @BackupFile = 'c:\pctopp\sqlserver\backup.dat'

-- the new database name
DECLARE @TestDB varchar(200)
SET @TestDB = 'TestDB'

-- the new database files without .mdf/.ldf
DECLARE @RestoreFile varchar(2000)
SET @RestoreFile = 'c:\pctopp\sqlserver\backup'

-- ****************************************************************
-- no change below this line
-- ****************************************************************
DECLARE @query varchar(2000)

DECLARE @DataFile varchar(2000)
SET @DataFile = @RestoreFile + '.mdf'

DECLARE @LogFile varchar(2000)
SET @LogFile = @RestoreFile + '.ldf'

IF @DB IS NOT NULL
BEGIN

SET @query = 'BACKUP DATABASE ' + @DB + ' TO DISK = ' + QUOTENAME(@BackupFile, '''')
EXEC (@query)

END

-- RESTORE FILELISTONLY FROM DISK = 'C:\temp\backup.dat'
-- RESTORE HEADERONLY FROM DISK = 'C:\temp\backup.dat'
-- RESTORE LABELONLY FROM DISK = 'C:\temp\backup.dat'
-- RESTORE VERIFYONLY FROM DISK = 'C:\temp\backup.dat'

IF EXISTS(SELECT * FROM sysdatabases WHERE name = @TestDB)
BEGIN

SET @query = 'DROP DATABASE ' + @TestDB
EXEC (@query)

END

RESTORE HEADERONLY FROM DISK = @BackupFile
DECLARE @File int
SET @File = @@ROWCOUNT

DECLARE @Data varchar(500)
DECLARE @Log varchar(500)

SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BackupFile , '''')

CREATE TABLE #restoretemp
(
LogicalName varchar(500),
PhysicalName varchar(500),
type varchar(10),
FilegroupName varchar(200),
size int,
maxsize bigint
)

INSERT #restoretemp EXEC (@query)
SELECT @Data = LogicalName FROM #restoretemp WHERE type = 'D'
SELECT @Log = LogicalName FROM #restoretemp WHERE type = 'L'

PRINT @Data
PRINT @Log

TRUNCATE TABLE #restoretemp
DROP TABLE #restoretemp

IF @File > 0
BEGIN
SET @query = 'RESTORE DATABASE ' + @TestDB + ' FROM DISK = ' + QUOTENAME(@BackupFile, '''') +
' WITH MOVE ' + QUOTENAME(@Data, '''') + ' TO ' + QUOTENAME(@DataFile, '''') + ', MOVE ' +
QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogFile, '''') + ', FILE = ' + CONVERT(varchar, @File)

EXEC (@query)

END

GO

posted under programming

12 Comments to

“How to duplicate a SQL database”

On December 21st, 2007 at 4:56 pm Rob Rietow Says:
I get an error

Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.

Any thoughts?
On April 1st, 2008 at 2:50 am Jens Vestergaard Says:
Change datatypes in #restoretemp to correspond those listed in RESTORE FILELIST statement, see http://msdn2.microsoft.com/en-us/library/ms173778.aspx

CREATE TABLE #restoretemp
(
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
type char(1),
FilegroupName nvarchar(128),
size numeric(20),
maxsize numeric(20)
)
On May 13th, 2008 at 3:37 pm Al Johnson Says:
This didn’t work with our SQL2005 env. I’ve modified the script to make it work:

USE master
GO

– the original database (use ‘SET @DB = NULL’ to disable backup)
DECLARE @DB varchar(200)
SET @DB = ‘temp’

– the backup filename
DECLARE @BackupFile varchar(2000)
SET @BackupFile = ‘e:\temp\backup.dat’

– the new database name
DECLARE @TestDB varchar(200)
SET @TestDB = ‘TestDB’

– the new database files without .mdf/.ldf
DECLARE @RestoreFile varchar(2000)
SET @RestoreFile = ‘e:\temp\backup’

– ****************************************************************
– no change below this line
– ****************************************************************

DECLARE @query varchar(2000)

DECLARE @DataFile varchar(2000)
SET @DataFile = @RestoreFile + ‘.mdf’

DECLARE @LogFile varchar(2000)
SET @LogFile = @RestoreFile + ‘.ldf’

IF @DB IS NOT NULL
BEGIN
SET @query = ‘BACKUP DATABASE ‘ + @DB + ‘ TO DISK = ‘ + QUOTENAME(@BackupFile, ””)
EXEC (@query)
END

– RESTORE FILELISTONLY FROM DISK = ‘C:\temp\backup.dat’
– RESTORE HEADERONLY FROM DISK = ‘C:\temp\backup.dat’
– RESTORE LABELONLY FROM DISK = ‘C:\temp\backup.dat’
– RESTORE VERIFYONLY FROM DISK = ‘C:\temp\backup.dat’

IF EXISTS(SELECT * FROM sysdatabases WHERE name = @TestDB)
BEGIN
SET @query = ‘DROP DATABASE ‘ + @TestDB
EXEC (@query)
END

create table #restoreheader(
BackupName nvarchar(128)
, BackupDescription nvarchar(255)
, BackupType smallint
, ExpirationDate datetime
, Compressed tinyint
, Position smallint
, DeviceType tinyint
, UserName nvarchar(128)
, ServerName nvarchar(128)
, DatabaseName nvarchar(128)
, DatabaseVersion int
, DatabaseCreationDate datetime
, BackupSize numeric(20,0)
, FirstLSN numeric(25,0)
, LastLSN numeric(25,0)
, CheckpointLSN numeric(25,0)
, DatabaseBackupLSN numeric(25,0)
, BackupStartDate datetime
, BackupFinishDate datetime
, SortOrder smallint
, CodePage smallint
, UnicodeLocaleId int
, UnicodeComparisonStyle int
, CompatibilityLevel tinyint
, SoftwareVendorId int
, SoftwareVersionMajor int
, SoftwareVersionMinor int
, SoftwareVersionBuild int
, MachineName nvarchar(128)
, Flags int
, BindingID uniqueidentifier
, RecoveryForkID uniqueidentifier
, Collation nvarchar(128)
, FamilyGUID uniqueidentifier
, HasBulkLoggedData bit
, IsSnapshot bit
, IsReadOnly bit
, IsSingleUser bit
, HasBackupChecksums bit
, IsDamaged bit
, BeginsLogChain bit
, HasIncompleteMetaData bit
, IsForceOffline bit
, IsCopyOnly bit
, FirstRecoveryForkID uniqueidentifier
, ForkPointLSN numeric(25,0) NULL
, RecoveryModel nvarchar(60)
, DifferentialBaseLSN numeric(25,0) NULL
, DifferentialBaseGUID uniqueidentifier
, BackupTypeDescription nvarchar(60)
, BackupSetGUID uniqueidentifier NULL
)

DECLARE @File int
SET @query = ‘RESTORE HEADERONLY FROM DISK =’ + QUOTENAME(@BackupFile , ””)
insert #restoreheader exec(@query)
SET @File = @@ROWCOUNT

DECLARE @Data varchar(500)
DECLARE @Log varchar(500)

SET @query = ‘RESTORE FILELISTONLY FROM DISK = ‘ + QUOTENAME(@BackupFile , ””)

CREATE TABLE #restoretemp
(
LogicalName nvarchar(128)
,Old_PhysicalName nvarchar(128)
,[Type] char(1)
,FileGroupName nvarchar(128)
,[Size] numeric(20,0)
,[MaxSize] numeric(20,0)
,FileID bigint
,CreateLSN numeric(25,0)
,DropLSN numeric(25,0) NULL
,UniqueID uniqueidentifier
,ReadOnlyLSN numeric(25,0)
,ReadWriteLSN numeric(25,0)
,BackupSizeInByte bigint
,SourceBlockSize int
,FilegroupID int
,LogGroupGUID uniqueidentifier NULL
,DifferentialBaseLSN numeric(25,0)
,DifferentialbaseGUID uniqueidentifier
,IsReadOnly bit
,IsPresent bit
)

INSERT #restoretemp EXEC (@query)

SELECT @Data = LogicalName FROM #restoretemp WHERE [type] = ‘D’
SELECT @Log = LogicalName FROM #restoretemp WHERE [type] = ‘L’

PRINT @Data
PRINT @Log

TRUNCATE TABLE #restoretemp
TRUNCATE TABLE #restoreheader
DROP TABLE #restoretemp
DROP TABLE #restoreheader

select @File as [File]

IF @File > 0
BEGIN
SET @query = ‘RESTORE DATABASE ‘ + @TestDB + ‘ FROM DISK = ‘ + QUOTENAME(@BackupFile, ””) +
‘ WITH MOVE ‘ + QUOTENAME(@Data, ””) + ‘ TO ‘ + QUOTENAME(@DataFile, ””) + ‘, MOVE ‘ +
QUOTENAME(@Log, ””) + ‘ TO ‘ + QUOTENAME(@LogFile, ””) + ‘, FILE = ‘ + CONVERT(varchar, @File)
EXEC (@query)
END
GO
On April 29th, 2009 at 10:39 am Bipin Karmacharya Says:
Changed to create store proc and work with SQL 2008.

USE master
GO

PRINT ‘Checking for the existence of this procedure’
IF (SELECT OBJECT_ID(‘usp_CopyDBByBackupAndRestore’,'P’)) IS NOT NULL –means, the procedure already exists
BEGIN
PRINT ‘Procedure already exists. So, dropping it’
DROP PROC usp_CopyDBByBackupAndRestore
END
GO

CREATE PROCEDURE usp_CopyDBByBackupAndRestore
(
@SourceDB varchar(200), – the original database (use ‘SET @SourceDB = NULL’ to disable backup)
@BackupFile varchar(2000), – the backup file path
@DestinationDB varchar(200), – the new database name
@RestoreFileNoExtension varchar(2000) – the new database files without .mdf/.ldf
)
AS
BEGIN
DECLARE @query varchar(2000)

DECLARE @DataFile varchar(2000)
SET @DataFile = @RestoreFileNoExtension + ‘.mdf’

DECLARE @LogFile varchar(2000)
SET @LogFile = @RestoreFileNoExtension + ‘.ldf’

IF @SourceDB IS NOT NULL
BEGIN
– “With Format” means create a new file.
– Without this statement, if the backup file already existed, our new backup would append to it.
SET @query = ‘BACKUP DATABASE ‘ + @SourceDB + ‘ TO DISK = ‘ + QUOTENAME(@BackupFile, ””) + ‘ WITH FORMAT’
PRINT @query
EXEC (@query)
END

– RESTORE FILELISTONLY FROM DISK = ‘C:\temp\backup.dat’
– RESTORE HEADERONLY FROM DISK = ‘C:\temp\backup.dat’
– RESTORE LABELONLY FROM DISK = ‘C:\temp\backup.dat’
– RESTORE VERIFYONLY FROM DISK = ‘C:\temp\backup.dat’

IF EXISTS(SELECT * FROM sysdatabases WHERE name = @DestinationDB)
BEGIN
SET @query = ‘DROP DATABASE ‘ + @DestinationDB
EXEC (@query)
END

CREATE TABLE #restoreheader(
BackupName nvarchar(128)
, BackupDescription nvarchar(255)
, BackupType smallint
, ExpirationDate datetime
, Compressed tinyint
, Position smallint
, DeviceType tinyint
, UserName nvarchar(128)
, ServerName nvarchar(128)
, DatabaseName nvarchar(128)
, DatabaseVersion int
, DatabaseCreationDate datetime
, BackupSize numeric(20,0)
, FirstLSN numeric(25,0)
, LastLSN numeric(25,0)
, CheckpointLSN numeric(25,0)
, DatabaseBackupLSN numeric(25,0)
, BackupStartDate datetime
, BackupFinishDate datetime
, SortOrder smallint
, [CodePage] smallint
, UnicodeLocaleId int
, UnicodeComparisonStyle int
, CompatibilityLevel tinyint
, SoftwareVendorId int
, SoftwareVersionMajor int
, SoftwareVersionMinor int
, SoftwareVersionBuild int
, MachineName nvarchar(128)
, Flags int
, BindingID uniqueidentifier
, RecoveryForkID uniqueidentifier
, Collation nvarchar(128)
, FamilyGUID uniqueidentifier
, HasBulkLoggedData bit
, IsSnapshot bit
, IsReadOnly bit
, IsSingleUser bit
, HasBackupChecksums bit
, IsDamaged bit
, BeginsLogChain bit
, HasIncompleteMetaData bit
, IsForceOffline bit
, IsCopyOnly bit
, FirstRecoveryForkID uniqueidentifier
, ForkPointLSN numeric(25,0) NULL
, RecoveryModel nvarchar(60)
, DifferentialBaseLSN numeric(25,0) NULL
, DifferentialBaseGUID uniqueidentifier
, BackupTypeDescription nvarchar(60)
, BackupSetGUID uniqueidentifier NULL
, CompressedBackupSize int NULL
)

DECLARE @File int
SET @query = ‘RESTORE HEADERONLY FROM DISK =’ + QUOTENAME(@BackupFile , ””)
INSERT #restoreheader EXEC (@query)
SET @File = @@ROWCOUNT

CREATE TABLE #restoretemp
(
LogicalName nvarchar(128)
,PhysicalName nvarchar(128)
,[Type] char(1)
,FileGroupName nvarchar(128)
,[Size] numeric(20,0)
,[MaxSize] numeric(20,0)
,FileID bigint
,CreateLSN numeric(25,0)
,DropLSN numeric(25,0) NULL
,UniqueID uniqueidentifier
,ReadOnlyLSN numeric(25,0)
,ReadWriteLSN numeric(25,0)
,BackupSizeInByte bigint
,SourceBlockSize int
,FilegroupID int
,LogGroupGUID uniqueidentifier NULL
,DifferentialBaseLSN numeric(25,0)
,DifferentialbaseGUID uniqueidentifier
,IsReadOnly bit
,IsPresent bit
,TDEThumbprint varbinary(32)
)

DECLARE @Data varchar(500)
DECLARE @Log varchar(500)

SET @query = ‘RESTORE FILELISTONLY FROM DISK = ‘ + QUOTENAME(@BackupFile , ””)
INSERT #restoretemp EXEC (@query)

SELECT @Data = LogicalName FROM #restoretemp WHERE [type] = ‘D’
SELECT @Log = LogicalName FROM #restoretemp WHERE [type] = ‘L’

PRINT @Data
PRINT @Log

TRUNCATE TABLE #restoretemp
TRUNCATE TABLE #restoreheader
DROP TABLE #restoretemp
DROP TABLE #restoreheader

SELECT @File AS [File]

IF @File > 0
BEGIN
SET @query = ‘RESTORE DATABASE ‘ + @DestinationDB + ‘ FROM DISK = ‘ + QUOTENAME(@BackupFile, ””) +
‘ WITH MOVE ‘ + QUOTENAME(@Data, ””) + ‘ TO ‘ + QUOTENAME(@DataFile, ””) + ‘, MOVE ‘ +
QUOTENAME(@Log, ””) + ‘ TO ‘ + QUOTENAME(@LogFile, ””) + ‘, FILE = ‘ + CONVERT(varchar, @File)
EXEC (@query)
END

END
GO

PRINT ‘Created the procedure’
GO

PRINT ‘Granting EXECUTE permission on sp_generate_inserts to all users’
GRANT EXEC ON sp_generate_inserts TO public
GO

PRINT ‘Done’
On April 29th, 2009 at 10:44 am Bipin Karmacharya Says:
Changed to create store procedure and work with SQL 2008

– ***********************************************************************************
– This copies sql 2008 database using backup and restore method.
–
– Example 1: To copy database with backup and restore
– EXEC usp_CopyDBByBackupAndRestore ‘source’, ‘C:\source.bak’, ‘dest’, ‘C:\dest’
–
– Example 2: To copy database with restore only
– EXEC usp_CopyDBByBackupAndRestore NULL, ‘C:\source.bak’, ‘dest’, ‘C:\dest’
–
– ************************************************************************************

USE master
GO

PRINT ‘Checking for the existence of this procedure’
IF (SELECT OBJECT_ID(‘usp_CopyDBByBackupAndRestore’,'P’)) IS NOT NULL –means, the procedure already exists
BEGIN
PRINT ‘Procedure already exists. So, dropping it’
DROP PROC usp_CopyDBByBackupAndRestore
END
GO

CREATE PROCEDURE usp_CopyDBByBackupAndRestore
(
@SourceDB varchar(200), – the original database (use ‘SET @SourceDB = NULL’ to disable backup)
@BackupFile varchar(2000), – the backup file path
@DestinationDB varchar(200), – the new database name
@RestoreFileNoExtension varchar(2000) – the new database files without .mdf/.ldf
)
AS
BEGIN
DECLARE @query varchar(2000)

DECLARE @DataFile varchar(2000)
SET @DataFile = @RestoreFileNoExtension + ‘.mdf’

DECLARE @LogFile varchar(2000)
SET @LogFile = @RestoreFileNoExtension + ‘.ldf’

IF @SourceDB IS NOT NULL
BEGIN
– “With Format” means create a new file.
– Without this statement, if the backup file already existed, our new backup would append to it.
SET @query = ‘BACKUP DATABASE ‘ + @SourceDB + ‘ TO DISK = ‘ + QUOTENAME(@BackupFile, ””) + ‘ WITH FORMAT’
PRINT @query
EXEC (@query)
END

– RESTORE FILELISTONLY FROM DISK = ‘C:\temp\backup.dat’
– RESTORE HEADERONLY FROM DISK = ‘C:\temp\backup.dat’
– RESTORE LABELONLY FROM DISK = ‘C:\temp\backup.dat’
– RESTORE VERIFYONLY FROM DISK = ‘C:\temp\backup.dat’

IF EXISTS(SELECT * FROM sysdatabases WHERE name = @DestinationDB)
BEGIN
SET @query = ‘DROP DATABASE ‘ + @DestinationDB
EXEC (@query)
END

CREATE TABLE #restoreheader(
BackupName nvarchar(128)
, BackupDescription nvarchar(255)
, BackupType smallint
, ExpirationDate datetime
, Compressed tinyint
, Position smallint
, DeviceType tinyint
, UserName nvarchar(128)
, ServerName nvarchar(128)
, DatabaseName nvarchar(128)
, DatabaseVersion int
, DatabaseCreationDate datetime
, BackupSize numeric(20,0)
, FirstLSN numeric(25,0)
, LastLSN numeric(25,0)
, CheckpointLSN numeric(25,0)
, DatabaseBackupLSN numeric(25,0)
, BackupStartDate datetime
, BackupFinishDate datetime
, SortOrder smallint
, [CodePage] smallint
, UnicodeLocaleId int
, UnicodeComparisonStyle int
, CompatibilityLevel tinyint
, SoftwareVendorId int
, SoftwareVersionMajor int
, SoftwareVersionMinor int
, SoftwareVersionBuild int
, MachineName nvarchar(128)
, Flags int
, BindingID uniqueidentifier
, RecoveryForkID uniqueidentifier
, Collation nvarchar(128)
, FamilyGUID uniqueidentifier
, HasBulkLoggedData bit
, IsSnapshot bit
, IsReadOnly bit
, IsSingleUser bit
, HasBackupChecksums bit
, IsDamaged bit
, BeginsLogChain bit
, HasIncompleteMetaData bit
, IsForceOffline bit
, IsCopyOnly bit
, FirstRecoveryForkID uniqueidentifier
, ForkPointLSN numeric(25,0) NULL
, RecoveryModel nvarchar(60)
, DifferentialBaseLSN numeric(25,0) NULL
, DifferentialBaseGUID uniqueidentifier
, BackupTypeDescription nvarchar(60)
, BackupSetGUID uniqueidentifier NULL
, CompressedBackupSize int NULL
)

DECLARE @File int
SET @query = ‘RESTORE HEADERONLY FROM DISK =’ + QUOTENAME(@BackupFile , ””)
INSERT #restoreheader EXEC (@query)
SET @File = @@ROWCOUNT

CREATE TABLE #restoretemp
(
LogicalName nvarchar(128)
,PhysicalName nvarchar(128)
,[Type] char(1)
,FileGroupName nvarchar(128)
,[Size] numeric(20,0)
,[MaxSize] numeric(20,0)
,FileID bigint
,CreateLSN numeric(25,0)
,DropLSN numeric(25,0) NULL
,UniqueID uniqueidentifier
,ReadOnlyLSN numeric(25,0)
,ReadWriteLSN numeric(25,0)
,BackupSizeInByte bigint
,SourceBlockSize int
,FilegroupID int
,LogGroupGUID uniqueidentifier NULL
,DifferentialBaseLSN numeric(25,0)
,DifferentialbaseGUID uniqueidentifier
,IsReadOnly bit
,IsPresent bit
,TDEThumbprint varbinary(32)
)

DECLARE @Data varchar(500)
DECLARE @Log varchar(500)

SET @query = ‘RESTORE FILELISTONLY FROM DISK = ‘ + QUOTENAME(@BackupFile , ””)
INSERT #restoretemp EXEC (@query)

SELECT @Data = LogicalName FROM #restoretemp WHERE [type] = ‘D’
SELECT @Log = LogicalName FROM #restoretemp WHERE [type] = ‘L’

PRINT @Data
PRINT @Log

TRUNCATE TABLE #restoretemp
TRUNCATE TABLE #restoreheader
DROP TABLE #restoretemp
DROP TABLE #restoreheader

SELECT @File AS [File]

IF @File > 0
BEGIN
SET @query = ‘RESTORE DATABASE ‘ + @DestinationDB + ‘ FROM DISK = ‘ + QUOTENAME(@BackupFile, ””) +
‘ WITH MOVE ‘ + QUOTENAME(@Data, ””) + ‘ TO ‘ + QUOTENAME(@DataFile, ””) + ‘, MOVE ‘ +
QUOTENAME(@Log, ””) + ‘ TO ‘ + QUOTENAME(@LogFile, ””) + ‘, FILE = ‘ + CONVERT(varchar, @File)
EXEC (@query)
END

END
GO

PRINT ‘Created the procedure’
GO

PRINT ‘Granting EXECUTE permission on usp_CopyDBByBackupAndRestore to all users’
GRANT EXEC ON usp_CopyDBByBackupAndRestore TO public
GO

PRINT ‘Done’
On August 6th, 2009 at 3:22 am Excite Template Says:
The info is very much worth for my work thanks…n all the best
On September 1st, 2009 at 12:16 am Neil Hoskins Says:
What about copying a database from one server to another without using Linked Servers? When I go on-site I always need to take a copy of the client’s database, then upload it to our secure office SQL Server when I get back to the office. However, I may need to work on it between locations.
On October 14th, 2009 at 10:09 pm war Says:
Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
On October 14th, 2009 at 10:09 pm war Says:
How to solve that error.. Pls help me.. :(
On October 27th, 2009 at 1:28 am MT Says:
Neil Hoskins > You can use smo with C# .NET…
On November 24th, 2009 at 7:05 am Pablo Alonso Says:
Hi, I have to duplicate a database on the same server as the procedure proposes, but I cannot even get saved the procedure. it gives me an error:

“Error 21001: [SQL-DMO]Stored procedure definition must include name and text (for Standard StoredProcedure) or libraryname (for Extended StoredProcedure).”

I’m using SQL 2000 with SP4
Can anyone help me with this, please???
I’m a newbie so explain well in case you know how to solve this issue.

Thanks in advance.
Regards,
Pablo Alonso
On April 13th, 2010 at 5:51 am RSA Now Says:
Thank you for posting this, and thanks also to Bipin Karmacharya for doing the 2008 conversion – which was exactly what I was looking for.

But why oh why doesn’t Microsoft just ship a built in sp_copydatabase() proc? It would make our lives so much easier!