CODEWITHSUNDEEP
sql-servert-sql
Hovestar
Hovestar

Reputation: 1627

Clone a SQL Server database programmatically

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?

Upvotes: 1

Views: 1275

Answers (3)

paul stanton
paul stanton

Reputation: 964

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

Upvotes: 0

Hovestar
Hovestar

Reputation: 1627

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.

Upvotes: 1

Sean Pearce
Sean Pearce

Reputation: 1169

RESTORE FILELISTONLY   
FROM 'path'

Upvotes: 1

Related Questions