How can I clone an SQL Server database on the same server in SQL Server 2008 Express?

Question

I have an MS SQL Server 2008 Express system which contains a database that I would like to 'copy and rename' (for testing purposes) but I am unaware of a simple way to achieve this.

I notice that in the R2 version of SQL Server there is a copy database wizard, but sadly I can't upgrade.

The database in question is around a gig. I attempted to restore a backup of the database I want to copy into a new database, but with no luck.

Answer 1

1. Install Microsoft SQL Management Studio, which you can download for free from Microsoft's website:

   Version 2008

   Microsoft SQL Management Studio 2008 is part of SQL Server 2008 Express with Advanced Services

   Version 2012

   Click download button and check ENU\x64\SQLManagementStudio_x64_ENU.exe

   Version 2014

   Click download button and check MgmtStudio 64BIT\SQLManagementStudio_x64_ENU.exe

2. Open Microsoft SQL Management Studio.

3. Backup original database to .BAK file (db -> Task -> Backup).
4. Create empty database with new name (clone). Note comments below as this is optional.
5. Click to clone database and open restore dialog (see image)
6. Select Device and add the backup file from step 3.
7. Change destination to test database
8. Change location of database files, it must be different from the original. You can type directly into text box, just add postfix. (NOTE: Order is important. Select checkbox, then change the filenames.)
9. Check WITH REPLACE and WITH KEEP_REPLICATION

Answer 2

Right-click the database to clone, click Tasks, click Copy Database.... Follow the wizard and you're done.

Answer 3

You could try to detach the database, copy the files to new names at a command prompt, then attach both DBs.

In SQL:

USE master;
GO 
EXEC sp_detach_db
    @dbname = N'OriginalDB';
GO

At Command prompt (I've simplified the file paths for the sake of this example):

copy c:\OriginalDB.mdf c:\NewDB.mdf
copy c:\OriginalDB.ldf c:\NewDB.ldf

In SQL again:

USE master;
GO
CREATE DATABASE OriginalDB
    ON (FILENAME = 'C:\OriginalDB.mdf'),
       (FILENAME = 'C:\OriginalDB.ldf')
    FOR ATTACH;
GO
CREATE DATABASE NewDB
    ON (FILENAME = 'C:\NewDB.mdf'),
       (FILENAME = 'C:\NewDB.ldf')
    FOR ATTACH;
GO

Answer 4

It turns out that I had attempted to restore from a backup incorrectly.

Initially I created a new database and then attempted to restore the backup here. What I should have done, and what worked in the end, was to bring up the restore dialog and type the name of the new database in the destination field.

So, in short, restoring from a backup did the trick.

Thanks for all the feedback and suggestions guys

Answer 5

This is the script I use. A bit tricky but it works. Tested on SQL Server 2012.

DECLARE @backupPath nvarchar(400);
DECLARE @sourceDb nvarchar(50);
DECLARE @sourceDb_log nvarchar(50);
DECLARE @destDb nvarchar(50);
DECLARE @destMdf nvarchar(100);
DECLARE @destLdf nvarchar(100);
DECLARE @sqlServerDbFolder nvarchar(100);

SET @sourceDb = 'db1'
SET @sourceDb_log = @sourceDb + '_log'
SET @backupPath = 'E:\DB SQL\MSSQL11.MSSQLSERVER\MSSQL\Backup\' + @sourceDb + '.bak'    --ATTENTION: file must already exist and SQL Server must have access to it
SET @sqlServerDbFolder = 'E:\DB SQL\MSSQL11.MSSQLSERVER\MSSQL\DATA\'
SET @destDb = 'db2'
SET @destMdf = @sqlServerDbFolder + @destDb + '.mdf'
SET @destLdf = @sqlServerDbFolder + @destDb + '_log' + '.ldf'

BACKUP DATABASE @sourceDb TO DISK = @backupPath

RESTORE DATABASE @destDb FROM DISK = @backupPath
WITH REPLACE,
   MOVE @sourceDb     TO @destMdf,
   MOVE @sourceDb_log TO @destLdf

Answer 6

None of the solutions mentioned here worked for me - I am using SQL Server Management Studio 2014.

Instead I had to uncheck the "Take tail-log backup before restore" checkbox in the "Options" screen: in my version it is checked by default and prevents the Restore operation to be completed. After unchecking it, the Restore operation proceeded without issues.

Answer 7

Using MS SQL Server 2012, you need to perform 3 basic steps:

1. First, generate .sql file containing only the structure of the source DB

   • right click on the source DB and then Tasks then Generate Scripts
   • follow the wizard and save the .sql file locally

2. Second, replace the source DB with the destination one in the .sql file

   • Right click on the destination file, select New Query and Ctrl-H or (Edit - Find and replace - Quick replace)

3. Finally, populate with data

   • Right click on the destination DB, then select Tasks and Import Data
   • Data source drop down set to ".net framework data provider for SQL server" + set the connection string text field under DATA ex: Data Source=Mehdi\SQLEXPRESS;Initial Catalog=db_test;User ID=sa;Password=sqlrpwrd15
   • do the same with the destination
   • check the table you want to transfer or check box besides "source: ..." to check all of them

You are done.

Answer 8

If the database is not very large, you might look at the 'Script Database' commands in SQL Server Management Studio Express, which are in a context menu off the database item itself in the explorer.

You can choose what all to script; you want the objects and the data, of course. You will then save the entire script to a single file. Then you can use that file to re-create the database; just make sure the USE command at the top is set to the proper database.

Answer 9

In SQL Server 2008 R2, back-up the database as a file into a folder. Then chose the restore option that appears in the "Database" folder. In the wizard enter the new name that you want in the target database. And choose restore frrom file and use the file you just created. I jsut did it and it was very fast (my DB was small, but still) Pablo.

Answer 10

The solution, based on this comment: https://stackoverflow.com/a/22409447/2399045 . Just set settings: DB name, temp folder, db files folder. And after run you will have the copy of DB with Name in "sourceDBName_yyyy-mm-dd" format.

-- Settings --
-- New DB name will have name = sourceDB_yyyy-mm-dd
declare @sourceDbName nvarchar(50) = 'MyDbName';
declare @tmpFolder nvarchar(50) = 'C:\Temp\'
declare @sqlServerDbFolder nvarchar(100) = 'C:\Databases\'

--  Execution --
declare @sourceDbFile nvarchar(50);
declare @sourceDbFileLog nvarchar(50);
declare @destinationDbName nvarchar(50) = @sourceDbName + '_' + (select convert(varchar(10),getdate(), 121))
declare @backupPath nvarchar(400) = @tmpFolder + @destinationDbName + '.bak'
declare @destMdf nvarchar(100) = @sqlServerDbFolder + @destinationDbName + '.mdf'
declare @destLdf nvarchar(100) = @sqlServerDbFolder + @destinationDbName + '_log' + '.ldf'

SET @sourceDbFile = (SELECT top 1 files.name 
                    FROM sys.databases dbs 
                    INNER JOIN sys.master_files files 
                        ON dbs.database_id = files.database_id 
                    WHERE dbs.name = @sourceDbName
                        AND files.[type] = 0)

SET @sourceDbFileLog = (SELECT top 1 files.name 
                    FROM sys.databases dbs 
                    INNER JOIN sys.master_files files 
                        ON dbs.database_id = files.database_id 
                    WHERE dbs.name = @sourceDbName
                        AND files.[type] = 1)

BACKUP DATABASE @sourceDbName TO DISK = @backupPath

RESTORE DATABASE @destinationDbName FROM DISK = @backupPath
WITH REPLACE,
   MOVE @sourceDbFile     TO @destMdf,
   MOVE @sourceDbFileLog  TO @destLdf

Answer 11

Script based on Joe answer (detach, copy files, attach both).

1. Run Managment Studio as Administrator account.

It's not necessary, but maybe access denied error on executing.

2. Configure sql server for execute xp_cmdshel

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

3. Run script, but type your db names in @dbName and @copyDBName variables before.

USE master;
GO 

DECLARE @dbName NVARCHAR(255) = 'Products'
DECLARE @copyDBName NVARCHAR(255) = 'Products_branch'

-- get DB files
CREATE TABLE ##DBFileNames([FileName] NVARCHAR(255))
EXEC('
    INSERT INTO ##DBFileNames([FileName])
    SELECT [filename] FROM ' + @dbName + '.sys.sysfiles')

-- drop connections
EXEC('ALTER DATABASE ' + @dbName + ' SET OFFLINE WITH ROLLBACK IMMEDIATE')

EXEC('ALTER DATABASE ' + @dbName + ' SET SINGLE_USER')

-- detach
EXEC('EXEC sp_detach_db @dbname = ''' + @dbName + '''')

-- copy files
DECLARE @filename NVARCHAR(255), @path NVARCHAR(255), @ext NVARCHAR(255), @copyFileName NVARCHAR(255), @command NVARCHAR(MAX) = ''
DECLARE 
    @oldAttachCommand NVARCHAR(MAX) = 
        'CREATE DATABASE ' + @dbName + ' ON ', 
    @newAttachCommand NVARCHAR(MAX) = 
        'CREATE DATABASE ' + @copyDBName + ' ON '

DECLARE curs CURSOR FOR 
SELECT [filename] FROM ##DBFileNames
OPEN curs  
FETCH NEXT FROM curs INTO @filename
WHILE @@FETCH_STATUS = 0  
BEGIN
    SET @path = REVERSE(RIGHT(REVERSE(@filename),(LEN(@filename)-CHARINDEX('\', REVERSE(@filename),1))+1))
    SET @ext = RIGHT(@filename,4)
    SET @copyFileName = @path + @copyDBName + @ext

    SET @command = 'EXEC master..xp_cmdshell ''COPY "' + @filename + '" "' + @copyFileName + '"'''
    PRINT @command
    EXEC(@command);

    SET @oldAttachCommand = @oldAttachCommand + '(FILENAME = "' + @filename + '"),'
    SET @newAttachCommand = @newAttachCommand + '(FILENAME = "' + @copyFileName + '"),'

    FETCH NEXT FROM curs INTO @filename
END
CLOSE curs 
DEALLOCATE curs

-- attach
SET @oldAttachCommand = LEFT(@oldAttachCommand, LEN(@oldAttachCommand) - 1) + ' FOR ATTACH'
SET @newAttachCommand = LEFT(@newAttachCommand, LEN(@newAttachCommand) - 1) + ' FOR ATTACH'

-- attach old db
PRINT @oldAttachCommand
EXEC(@oldAttachCommand)

-- attach copy db
PRINT @newAttachCommand
EXEC(@newAttachCommand)

DROP TABLE ##DBFileNames

Answer 12

You could just create a new database and then go to tasks, import data, and import all the data from the database you want to duplicate to the database you just created.

Answer 13

From SSMS :

1 - Backup original database to .BAK file (your_source_db -> Task -> Backup).

2 - Right clicking the "Databases" and 'Restore Database'

3 - Device > ... (button) > Add > select the your_source_db.bak

4 - In 'General' tab, in 'Destination' section, rename in 'Database' your_source_db to new_name_db

5 - In 'Files' tab, tick 'Relocate all files to folder',

• Rename in 'Restore As' column the two lignes to keep consistency with new_name_db (.mdf, _log.ldf)

6 - In 'Options' tab, in 'Restore options' section, tick two fist options ('Overwrite...', 'Preserve...') and for 'Recovery state' : 'RESTORE WITH RECOVERY'

• Make also sure that in 'Tail-Log backup' section options are unticked to avoid keeping source db in 'restoring state' !

Answer 14

Another way that does the trick by using import/export wizard, first create an empty database, then choose the source which is your server with the source database, and then in the destination choose the same server with the destination database (using the empty database you created at first), then hit finish

It will create all tables and transfer all the data into the new database,