0

Hello I have database on which user work. I want to create copy of this database for testing purpose.

Can anyone tell how can this be done? I tried everything from copy a database within SQL Server Express?

But Copying files and attaching it destroys first database and trying to restore backup into new database throws error that database is not the same.

Can anyone suggest me solution?

as @Nadeem_MK suggested I created backup and now I'm trying to restore with this script:

RESTORE DATABASE [Equipment Test]  -- use your new db name
FROM  DISK = N'C:\Backup\ExistingDb.bak'  --Path of backup location
WITH  REPLACE,RECOVERY,  
MOVE N'Equipment Test' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\ExistingDb.mdf',  --logical name and physical name 
MOVE N'Equipment Test_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\ExistingDb.ldf' --logical name and physical name

and this throws error:

Msg 1834, Level 16, State 1, Line 1
The file 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Equipment.mdf' cannot be overwritten.  It is being used by database 'Equipment'.
Msg 3156, Level 16, State 4, Line 1
File 'Equipment' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Equipment.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 1

The file 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Equipment_log.ldf' cannot be overwritten.  It is being used by database 'Equipment'.
Msg 3156, Level 16, State 4, Line 1
File 'Equipment_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Equipment_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

As you can see restores is trying to alter files from backup-ed database instead of new database

@Update: BackUp Script:

--To backup Database (Delete the previous backup first)
BACKUP DATABASE Equipment --Database Name
TO DISK = 'C:\Backup\Equipment.bak' WITH INIT --Path of backup location

file locations:

C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Equipment.mdf
C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Equipment_log.ldf
C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Equipment Test.mdf
C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Equipment Test_log.mdf

Restore function:

RESTORE DATABASE [Equipment Test]  -- use your new db name
FROM  DISK = N'C:\Backup\Equipment.bak'  --Path of backup location
WITH  REPLACE,RECOVERY,  
MOVE N'Equipment Test' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Equipment2.mdf',  --logical name and physical name 
MOVE N'Equipment Test_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Equipment_log2.ldf' --logical name and physical name
Community
  • 1
  • 1
szpic
  • 4,346
  • 15
  • 54
  • 85
  • Easiest is properly just to get a backup and then restore under different name. – Allan S. Hansen Mar 19 '14 at 09:53
  • yeah, sorry, the reporting file name should bear the new db name.. answer amended! should work now! – Nadeem_MK Mar 19 '14 at 12:50
  • Possible duplicate of [How can I clone an SQL Server database on the same server in SQL Server 2008 Express?](http://stackoverflow.com/questions/3829271/how-can-i-clone-an-sql-server-database-on-the-same-server-in-sql-server-2008-exp) – Tomas Kubes Mar 01 '16 at 07:43

5 Answers5

2

You can create a backup and restore it using the following script;

--To backup Database (Delete the previous backup first)
BACKUP DATABASE ExistingDb --Database Name
TO DISK = 'C:\DBBackup\ExistingDb.bak' WITH INIT --Path of backup location

Then restore it;

RESTORE DATABASE NewDbName  -- use your new db name
FROM  DISK = N'C:\DBBackup\ExistingDb.bak'  --Path of backup location
WITH  REPLACE,RECOVERY,  
MOVE N'Reporting' TO N'C:\Databases\MDF\NewDbName.mdf',  --logical name and physical name 
MOVE N'Reporting_log' TO N'C:\Databases\LDF\NewDbName.ldf' --logical name and physical name
Nadeem_MK
  • 7,533
  • 7
  • 50
  • 61
  • OUps, yeah my fault, the reporting file name should bear the new db name.. answer amended! – Nadeem_MK Mar 19 '14 at 12:48
  • Modified my script according to the your changes and still same error. You can see my paths and Db names in the question. Could you show me code with correct names from my post? I'll be gratefull – szpic Mar 19 '14 at 13:02
2

Assuming this are the paths of the original database:

C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Equipment.mdf
C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Equipment_log.ldf

and the filename of your backup is called

Equipment.bak

Make sure that the filenames for the new database aren't the same as the old one. That is what the errors you are getting means:

The file 'C:\...' cannot be overwritten.  It is being used by database 'Equipment'.

Explanation: Don't use filenames of files you already have!

Instead, give these a new name. Like this:

RESTORE DATABASE [Equipment Test]  -- use your new db name
FROM  DISK = N'C:\Backup\Equipment.bak'  --Path of backup location
WITH  REPLACE,RECOVERY,  
MOVE N'Equipment Test' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Equipment2.mdf',  --logical name and physical name 
MOVE N'Equipment Test_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Equipment_log2.ldf' --logical name and physical name

This is basically Nadeem_MK's answer, but instead of your original names Equipment.mdf and Equipment_log.ldf, now you should have Equipment2.mdf and Equipment_log.ldf

If this works, please also opvote Nadeem_MK's answer, as this is basically what he was saying. I just don't have enough reputation yet to comment on his answer.

user1261104
  • 315
  • 4
  • 14
  • Don't worry your answer give completely same error :) – szpic Mar 19 '14 at 13:27
  • That just makes no sense. I tested everything and for me it works. Please provide: 1. The exact script you use to backup, 2.the exact script you use to restore and 3. The paths of of the ldf and mdf in the original database. – user1261104 Mar 19 '14 at 13:46
  • Updated question With exact scripts and file paths. Thanks! – szpic Mar 19 '14 at 13:59
  • And you still get the "... cannot be overwritten" error with the scripts you posted in the last update? If so, you could check if the Equipment2 and Equipment_log2 indeed don't exist, if they exist, delete and try again. Else I have no clue. At first I was thinking it could be that you have no write access, but then I would expect another error message. In that case, I have no clue, I'm sorry... – user1261104 Mar 19 '14 at 14:09
  • I can assure You that there is no files like `Equipment2`. And `SSMS` is running under `Admin` rights. So the shouldn't be any permissions problems – szpic Mar 19 '14 at 14:12
0

Simple, Do these steps:

  1. Take backup of your primary database.

  2. Create new database with whatever name you want to give it for testing.

  3. Restore backup of your primary database to your newly created testing database.

Done!!

Vaibhav
  • 303
  • 1
  • 3
  • 9
0

Note: The functionality below is not available in SQL Express I have left the answer here for completeness for future readers only.

The simplest method I can think of is to let Management Studio do all the work!

Right-click the database you wish to copy and look for the Copy Database... option under the Tasks menu.

Copy database task

gvee
  • 16,732
  • 35
  • 50
0

Why don't you script the database and run the script?

user2063329
  • 443
  • 2
  • 5
  • 15