Clone a SQL Server database programmatically

Question

I am attempting to write a SQL script that copies and renames a database. I've split it into two parts as features independently, so the restore is giving me a hard time. For example this is approximately what I have now.

BACKUP DATABASE [@dbName] 
TO DISK = 'path' WITH COPY_ONLY;

RESTORE DATABASE [@newDbName] 
FROM DISK = 'path';

How can I alter this to automatically copy?

Read this example in the relevant msdn page. You might also want to read this link as well. — Zohar Peled
– Zohar Peled, Commented Jun 30, 2016 at 13:22
@ZoharPeled Yeah I found that one earlier, but I don't necessarily have the names of the _Data and _log sections. Is there a way of getting these from the bak file? — Hovestar
– Hovestar, Commented Jun 30, 2016 at 13:29
But if you perform the back you should be able to get that information. — paparazzo
– paparazzo, Commented Jun 30, 2016 at 15:09
I get what you are asking but you should be more clear about what is not working in the code you posted. — paparazzo
– paparazzo, Commented Jun 30, 2016 at 15:29

Sean Pearce · Accepted Answer · 2016-06-30 13:46:38Z

1

RESTORE FILELISTONLY   
FROM 'path'

answered Jun 30, 2016 at 13:46

Sean Pearce

1,1705 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hovestar Over a year ago

Is there a way I can assign a name to this so it won't conflict with the original name?

Hovestar · Accepted Answer · 2016-06-30 21:47:18Z

So the comments and answers were useful to get me here, but this is what I settled on:

BACKUP DATABASE [OldDBName] TO DISK = 'path' WITH COPY_ONLY;
if (objectProperty(object_id('tmp_restore'), 'IsProcedure') is not null)
    drop procedure dbo.tmp_restore
GO
CREATE PROCEDURE dbo.tmp_restore
  @backup_path NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;
    RESTORE FILELISTONLY FROM DISK = @backup_path;
END
GO
CREATE TABLE #TEMP(
    LogicalName VARCHAR(64),            PhysicalName VARCHAR(130),
    [Type] VARCHAR(1),                  FileGroupName VARCHAR(64),
    Size DECIMAL(20, 0),                MaxSize DECIMAL(25,0), 
    FileID bigint,                      CreateLSN DECIMAL(25,0), 
    DropLSN DECIMAL(25,0),              UniqueID UNIQUEIDENTIFIER,  
    ReadOnlyLSN DECIMAL(25,0),          ReadWriteLSN DECIMAL(25,0),
    BackupSizeInBytes DECIMAL(25,0),    SourceBlockSize INT,
    filegroupid INT,                    loggroupguid UNIQUEIDENTIFIER,
    differentialbaseLSN DECIMAL(25,0),  differentialbaseGUID UNIQUEIDENTIFIER,
    isreadonly BIT,     ispresent BIT,  TDEThumbpr DECIMAL)

Insert into #TEMP exec dbo.resware_tmp_restore @backup_path = 'path';
GO
DECLARE @NameData VARCHAR(64)
DECLARE @NameLog VARCHAR(64)
Set @NameData = (Select LogicalName from #TEMP t where t.Type like 'D')
Set @NameLog = (Select LogicalName from #TEMP t where t.Type like 'L')
RESTORE DATABASE [newDBName] FROM DISK = 'path'
    WITH RECOVERY,
    MOVE @NameData TO 'path_Data.mdf',
    MOVE @NameLog TO 'path_Log.ldf'
GO
DROP TABLE #TEMP

Obviously Old/NewDBName and path have to be filled in with the proper values, but this should work.

Glad to see you solved the problem. Please accept your own answer so that other people will know that the problem is solved.

CalvT · Accepted Answer · 2018-01-24 11:53:13Z

0

Another approach to cloning a SQL Server database is to use SQL Server containers.

Windocks supports containers for all editions SQL Server 2008 onward, and you can simply copy mdf, ndf, ldf files to an image and use that to deliver identical but isolated instances.

Windocks has also released integrated support for full database cloning as well as support of large DB environments.

A free Community Edition is available at Windocks

Disclosure: I am the Co-Founder of WinDocks

edited Jan 24, 2018 at 11:53

CalvT

3,2336 gold badges40 silver badges55 bronze badges

answered Apr 13, 2017 at 15:11

paul stanton

9557 silver badges7 bronze badges

Collectives™ on Stack Overflow

Clone a SQL Server database programmatically

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related