Create Duplicate SQL Database for Testing

Question

I have created a database on SQL server and a front end user application in winforms c#. It's up and running and working fine, but I've now been asked to set up a test version by the client for training new employees, so they can put in dummy data whilst they are trained without effecting the 'real' live database.

When I installed the database I had it all scripted, but things have changed since, and I don't particularly want to script all the tables again.

Is there a fast, convenient way of duplicating the database (and its data) on the same server, but under a different name?

EDIT (subsequent to Tony Hopkinson post)

I've got as far as this

exec sp_addumpdevice 'Disk','MyDatabaseBackup',N'D:\MyDatabaseBackup' 
Restore Database MyDatabase From MyDatabaseBackup With File = 1, 
Move 'MyDatabase' To N'C:\Program Files\Microsoft SQL 
Server\MSSQL11.SQLE\MSSQL\DATA\MyDatabaseTEST.mdf', 
Move 'MyDatabase_Log' To N'C:\Program Files\Microsoft SQL 
Server\MSSQL11.SQLE\MSSQL\DATA\MyDatabaseTEST_log.ldf', 
NORECOVERY,  NOUNLOAD,  STATS = 10 
RESTORE LOG [MyDatabaseTEST] FROM  [MyDatabaseBackup] WITH  FILE = 2,  NOUNLOAD,  STATS = 10 
exec sp_dropdevice MyDatabaseBackup 

But I'm getting the following error message

Msg 3234, Level 16, State 2, Line 2 Logical file 'MyDatabase' is not part of database 'MyDatabase'. Use RESTORE FILELISTONLY to list the logical file names. Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally. Msg 3154, Level 16, State 4, Line 6 The backup set holds a backup of a database other than the existing 'MyDatabaseTEST' database. Msg 3013, Level 16, State 1, Line 6 RESTORE LOG is terminating abnormally. Device dropped.

Answer 1

use copy database option in SQL server management studio

Answer 2

I found this method to be most effective on SQL Server 2005 and 2008, Express Editions:

From the Microsoft Docs:

1. Right click on the database you want to duplicate and choose Tasks->"Back Up..."
2. Save the back up to a .bak file
3. Right click on the "Databases" folder in the Object Explorer in SQL Server Management Studio
4. Choose "Restore Database"
5. As the source, select "File" and point to the .bak file you created earlier.
6. Change the name of the database to restore to (this was the key step for me - you are not constrained to the options in the dropdown.)

SSMS will restore your .bak file to a new database, according to the name that you give it.

Answer 3

First do a full backup your current database, which of course you have :)

The you restore it to another one

e.g. something like

exec sp_addumpdevice 'Disk','LiveDataBackup',N'Insert backup file name here including path'
Restore Database TestData From LiveDataBackup With File = 1,
Move 'LiveData' To N'Path to where sqlserver expects the mdfs to be\TestData.mdf',
Move 'LiveData_Log' To N'Path to where sqlserver expects the ldf to be\TaxData1.ldf',
NORECOVERY,  NOUNLOAD,  STATS = 10
RESTORE LOG [TestData] FROM  [LiveDataBackup] WITH  FILE = 2,  NOUNLOAD,  STATS = 10
exec sp_dropdevice LiveDataBackup

Above assume your live database is cunningly named LiveData and test, TestData.

The path to where the mdf and ldf will be depends on the version of sql server and the instance name

It should be something like C:\Program Files\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\DATA\

MSSQL11 because it's sql 2012, and DENALI is my instance name and it was installed by default in C: \Program Files

Also there's no with replace, so if you wanted to run it again, you'd need to Drop your test database.

There's probably some way to do this from the GUI, but I found it a massive PIA trying to relate the UI, to what I wanted to do.

Answer 4

using MS SQLServer 2012, you need to perform 3 basic steps

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 u can save the .sql file locally

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

=> right click on the destination file, open the .sql file and press New Query and Ctrl-H or (edit - find and replace - Quack replace)

finally, populate with data

=> right click on the detination DB, then Tasks and then Import Data => Data source drop dow set to ".net framework data procider for sql server" + set connection string text field under DATA ex: Data Source=Mehdi\SQLEXPRESS;Initial Catalog=db_test;User ID=sa;Password=sqlrpwrd15
=> Same thing to do with the destination => check the table you want to transfer or check box besides "source :....." to check all of them

you are done.

Answer 5

• Dump your database into a backup file
• Re-create your database from your dump - that is a script which you can run - with different name (that you have to change into the script)

You can follow (this)

Answer 6

You want to copy one Database_Production to Database_Testing in the same server. I would take database_production database as an example. I tested it in my server successfully.

Firstly, backup the database Database_Production.

BACKUP DATABASE Database_Production TO DISK ='H:\test\Database_Production.bark';

Secondly, restore Database_Production and this could rename the database name to Database_Testing.

RESTORE DATABASE Database_Testing 
    FROM DISK='H:\test\Database_Production.bark' 
    WITH 
        MOVE 'Database_Production_Data' TO 'H:\test\Database_Testing_Data.mdf',
        MOVE 'Database_Production_log' to  'H:\test\Database_Testing_Data.ldf';
GO

Then the database Database_Production is copied to database Database_Testing. The MOVE statement causes the data and log file to be restored to the specified locations. You do not need to create database Database_Testing and the script would create it.

Answer 7

If you need to create a database on the same server just create the empty database. Right Click on it and Select Restore-> Choose the database you want to make a copy of and click okay.