SQL Script to clone database leaving original untouched

Question

we currently have a base installation of our CMS, in this CMS it contains a complete working dataset for users, products, content etc. We are looking to increase our installation time because right now we currently have to go into SQL Server 2012, create a new DB and then restore the DB from an existing base installation db.

This can take up to 10 - 15 minutes each installation we do.

We also make sure our base database has all the requirements for sites we build.

Our issue is, we would like to do the following.

1. have a script create a fresh new empty database
2. make a clone of this database into a new .bak file
3. Take this .bak file then reproduce this into a fresh new database with its own MDF and LDF files respectively.

the databases are on the same server so we dont need to migrate this across to any other machine or instance.

Our code is below

CREATE database my_test

BACKUP DATABASE test_db TO DISK = 'C:\my_test\my_test.bak' WITH INIT;
EXEC internal_lab_test.dbo.sp_helpfile;

RESTORE FILELISTONLY
  FROM DISK = 'C:\my_test\my_test.bak'

RESTORE DATABASE my_test
  FROM DISK = 'C:\my_test\my_test.bak'
  WITH MOVE 'my_test' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\test_db.mdf',
  MOVE 'my_test_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\test_db_log.ldf'

we want to make sure that everything is fresh and clean, but also still contain all the data, however everytime we run this code, we get the following error messages

we also want to make sure the original database mdf and ldf files are left in tact and arent used in the new database

Msg 3154, Level 16, State 4, Line 10
The backup set holds a backup of a database other than the existing 'my_test' database.
Msg 3013, Level 16, State 1, Line 10
RESTORE DATABASE is terminating abnormally.

Answer 1

Compleate script wroted by me that is enough flexible for common development task (clone db from 'template', apply incremental scripts, then drop cloned db). Note: you should make your DB 'read_only, single_user' before procedure adv.cloneDatabase will be executed, and return original states after, also you need rights to execute xp_cmdshell.

CREATE PROCEDURE adv.alterateFileNames(
        @mdfFileName nvarchar(256), 
        @ldfFileName nvarchar(256), 
        @newMdfFileName nvarchar(256) OUTPUT, 
        @newLdfFileName nvarchar(256) OUTPUT
)
AS
BEGIN
    DECLARE @path_data nvarchar(256)
    DECLARE @ext_data nvarchar(4)
    DECLARE @path_log nvarchar(256)
    DECLARE @ext_log nvarchar(4)

    -- respect file extensions
    if (RIGHT(@mdfFileName , 4)='.mdf')
    BEGIN
        SET @path_data = SUBSTRING(@mdfFileName,0,LEN(@mdfFileName)-3)
        SET @ext_data = '.mdf'
    END
    ELSE
    BEGIN
        SET @path_data = @mdfFileName
        SET @ext_data = ''
    END


    if (RIGHT(@ldfFileName , 4)='.ldf')
    BEGIN
        SET @path_log = SUBSTRING(@ldfFileName,0,LEN(@ldfFileName)-3)
        SET @ext_log = '.ldf'
    END
    ELSE
    BEGIN
        SET @path_log = @ldfFileName
        SET @ext_log = ''
    END

    -- respect suffix counters like dbname_2 (that means add value to them)
    DECLARE @iData int

    DECLARE @data_suffix_index int = len(@path_data) - charindex('_', reverse(@path_data))
    IF (@data_suffix_index < len(@path_data)-1 AND @data_suffix_index > 0 )
    BEGIN
        DECLARE @data_suffix nvarchar(128) = substring(@path_data, @data_suffix_index+2, len(@path_data)-@data_suffix_index-1 ) 
        IF @data_suffix NOT LIKE '%[^0-9]%'
        BEGIN
            SET @path_data = SUBSTRING(@path_data,0,@data_suffix_index+1)
            SET @iData = CAST(@data_suffix as int);
        END
    END
    IF (@iData is null)
    BEGIN
            SET @path_data = @path_data
            SET @iData = 0
    END 

    DECLARE @iLog int

    DECLARE @log_suffix_index int = len(@path_log) - charindex('_', reverse(@path_log))
    IF (@log_suffix_index < len(@path_log)-1 AND @log_suffix_index > 0 )
    BEGIN
        DECLARE @log_suffix nvarchar(128) = substring(@path_log, @log_suffix_index+2, len(@path_log) - @log_suffix_index-1 ) 
        IF @log_suffix NOT LIKE '%[^0-9]%'
        BEGIN
            SET @path_log = SUBSTRING(@path_log,0,@log_suffix_index+1)
            SET @iLog = CAST(@log_suffix as int);
        END
    END
    IF (@iLog is null)
    BEGIN
            SET @path_log = @path_log
            SET @iLog = 0
    END 

    WHILE 1=1
    BEGIN
        IF EXISTS(SELECT * FROM sys.master_files WHERE physical_name=@path_data+'_'+CAST(@iData AS varchar(6))+@ext_data)
            SET @iData=@iData+1
        ELSE
        BEGIN
            SET @path_data= @path_data+'_'+CAST(@iData AS varchar(6))+@ext_data
            BREAK
        END
    END

    WHILE 1=1
    BEGIN
        IF EXISTS(SELECT * FROM sys.master_files WHERE physical_name=@path_log+'_'+CAST(@iLog AS varchar(6))+@ext_log)
            SET @iLog=@iLog+1
        ELSE
        BEGIN
            SET @path_log= @path_log+'_'+CAST(@iLog AS varchar(6))+@ext_log
            BREAK
        END
    END
    SET @newMdfFileName = @path_data
    SET @newLdfFileName = @path_log
END
GO
CREATE PROCEDURE adv.cloneDatabase
( 
    @databaseName sysname,
    @newDatabaseName sysname
)
AS
BEGIN
    SET NOCOUNT ON

    IF NOT EXISTS ( SELECT * FROM sys.databases WHERE name = @databaseName)
        THROW 50000, 'Database doesn''t exist', 1;
    IF NOT EXISTS ( SELECT * FROM sys.databases WHERE name = @databaseName AND owner_sid<>0x01)
        THROW 50000, 'Clonning of system database is not supported', 1;
    IF NOT EXISTS ( SELECT * FROM sys.databases WHERE name = @databaseName AND is_read_only=1)
        THROW 50000, 'Clonning of not readonly database is not supported', 1;
    IF NOT EXISTS ( SELECT * FROM sys.databases WHERE name = @databaseName AND user_access=1 /*single user*/)
        THROW 50000, 'Clonning of nor single_user database is not supported', 1;

    -- collect file info
    DECLARE @Files TABLE 
    (
        [type] int, /*0,1,2,3,4*/
        type_desc nvarchar(60), /*ROWS,LOG,FILESTREAM,FULLTEXT*/
        name sysname,
        physical_name nvarchar(260)
    )

    INSERT INTO @Files ([type], type_desc, name, physical_name)
    SELECT [type], type_desc, f.name, physical_name
    FROM sys.master_files f INNER JOIN sys.databases d ON f.database_id=d.database_id
    WHERE d.name=@databaseName

    -- test files
    DECLARE @filesCount int
    SELECT @filesCount =  count(*) from @Files
    IF (@filesCount<>2)
        THROW 50000, 'The procedure doesn''t support complex file structures', 1;

    DECLARE @mdfFileName nvarchar(260), @ldfFileName nvarchar(260)
    SELECT @mdfFileName = physical_name FROM @Files WHERE type_desc='ROWS'
    SELECT @ldfFileName = physical_name FROM @Files WHERE type_desc='LOG'

    DECLARE @newMdfFileName nvarchar(260), @newLdfFileName nvarchar(260)
    exec adv.alterateFileNames @mdfFileName, @ldfFileName, @newMdfFileName=@newMdfFileName OUTPUT, @newLdfFileName=@newLdfFileName OUTPUT

    DECLARE @cmd1 nvarchar(4000)= 'copy /Y "@mdfFileName" "@newMdfFileName"'
    DECLARE @cmd2 nvarchar(4000)= 'copy "@ldfFileName" "@newLdfFileName"'
    SET @cmd1=replace(@cmd1,'@mdfFileName',@mdfFileName)
    SET @cmd1=replace(@cmd1,'@newMdfFileName',@newMdfFileName)
    SET @cmd2=replace(@cmd2,'@ldfFileName',@ldfFileName)
    SET @cmd2=replace(@cmd2,'@newLdfFileName',@newLdfFileName)

    DECLARE @OUTPUT TABLE (line text)
    DECLARE @result INT

    BEGIN TRY
        INSERT INTO @OUTPUT (line) VALUES ('> '+@cmd1)
        INSERT INTO @OUTPUT
        exec @result =xp_cmdshell @cmd1
        INSERT INTO @OUTPUT (line) VALUES ('> '+@cmd2)
        IF (@result <> 0)
           THROW 50000, 'Error copying mdf file',1

        INSERT INTO @OUTPUT
        exec @result =xp_cmdshell @cmd2
        IF (@result <> 0)
           THROW 50000, 'Error copying ldf file',1
    END TRY
    BEGIN CATCH
        SELECT * FROM @OUTPUT WHERE line is not null;
        THROW
    END CATCH
    DECLARE @createDatabaseSql nvarchar(max)
    SET @createDatabaseSql = '
    CREATE DATABASE '+@newDatabaseName+'
      ON (FILENAME = '''+@newMdfFileName+'''),
         (FILENAME = '''+@newLdfFileName+''')
   FOR ATTACH;'
   exec sp_executesql  @stmt = @createDatabaseSql

END
GO

Answer 2

I know this is old, but it was the 2nd entry in google so for completeness.

It is because the database already exists. so either drop the database or add with Replace.

Also the my_test and my_test_log names need to be the logical names from the restore filelistonly command.

RESTORE DATABASE my_test
  FROM DISK = 'C:\my_test\my_test.bak'
  WITH Replace,
  MOVE 'my_test' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\test_db.mdf',
  MOVE 'my_test_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\test_db_log.ldf'