copy a database within SQL Server Express?

Question

I would like to make a copy of a database I have but keep it on the same server as a test database. However, everything I have found is to use the copy database wizard (I am using MS SQL Server Express).

The instructions always say: In SQL Server Management Studio, in Object Explorer, expand Databases, right-click a database, point to Tasks, and then click Copy Database.

I don't have the Copy Database option. I am running as an admin, so no clue why it is missing for me - is it something I have to install separately? I can't do the Detach/Attach since it is copying to the same server. I tried detaching, copying the MDF/LDF, renaming, attaching but as you can imagine that messed a ton up :) I am not great with SQL to do it all programatically. Is there a tool out there I could use?

Volplane

possible duplicate of How can I clone an SQL Server database on the same server in SQL Server 2008 Express? – Underfeed

Answer 1

In SSMS 2008 you can do this:

1. Create a backup of the database you want to copy

2. In SSMS, right-click 'Databases' and select 'Restore Database'

3. Select the database you wish to copy from the 'From database' drop-down list in the 'Source for restore' section

4. Enter the name of the new database in the 'To database' field in the 'Destination for Restore' section - this cannot be the name of an existing database.

5. Click OK

You're done! :)

Heloise

For me it causes a lot of problems, as it still tries to use the same MDF and LDF file as the copied database. You can change MDF and LDF files to use, but still, somehow it often tries to use the ones from the original database. – Pigheaded

@Pigheaded I'm experiencing a similar issue. Have you managed to work around it? – Accidie

I did - after few tries, changing MDF and LDF paths while restoring database worked. Somehow. I don't remember if I have done anything special there to make it work though. – Pigheaded

Set new .mdf and .ldf files did the trick, in the "restore database" popup, "Files" tab, parameters "restore as" ; thanks for the tip @Pigheaded – Arcane

Seems to have been fixed by SSMS 17.4 (2017). I at least got new file names based on the new database name automatically suggested. – Ochone

Answer 2

In SQL Server Express 2012 you can do following steps:

1. Create a backup of the database you want to copy
2. right-click "Databases" and select "Restore Files and Filegroups"
3. Enter the name of the new database in the "To database" field.
4. Select "From device" and then select the file that you backuped in the first step
5. click "OK"

this will "clone" the Database with the correct table settings such as the "default value" and "auto increase" etc.

Nairn

This also works great with SQL Server Express 2008 R2. Thanks! – Quetzalcoatl

To backup and restore database, do I need login as admin? – Unpack

I used "Restore Database" before I noticed the answer suggested "Restore Files and Filegroups". I received what I wanted, but I wondered what the difference is. – Jug

NB: This only works if you haven't already created the destination database (i.e., in step 3 it has to be a non-existing new DB name). – Leopoldeen

Answer 3

SQL Express database has an export button, I just exported the database to a new database on the same server, it is copying the database. Just right-click on the database name.

Agnosticism

Slower than backup/restore but the most painless and foolproof answer provided especially when migrating a lot of small databases. – Benefield

Actually I think it's faster, as half the reading and writing is needed. User accounts got copied across as well. – Benefield

It doesn't copy stored procedures. Not sure about indexes. – Pigheaded

It also copied my views as tables. – Aisne

I had the same issue as @Aisne -- i.e. Views were copied as Tables. I ended up resolving this by backing up the existing database and restoring it to a new database. There's a catch, however, wherein you're unable to restore a database onto an existing database, as it throws an "The backup set holds a backup of a database other than the existing <DB_Name> database." error. Removing the new database and re-executing the restore process, whilst manually typing the new database name into the Destination database field, worked perfectly for me! :) – Wimberly

It doesn't copy indexes, PK, FKs etc. However, with visual studio SQL Server data comparison, you can synchronize 2 databases schemas. After this, use SSMS export to insert the data into the destination database. – Pentachlorophenol

Answer 4

Take these steps to make a copy of the database in SQL Express

1. Stop SQL
2. Copy the mdf, ldf and any other file for the db to a NEW location (make sure you get the log file)
3. Change the name of each copied file
4. Start SQL
5. Right-click Database in SSMQ and select attach
6. Make sure you change the name in the column "Attach As"
7. Update the file location in the lower pane of "Database Details" to the location of your copied files (especially that log file)

I was able to copy a database on my SQL Express system with this method

Lamothe

I did this but couldn't get it to work. It broke my original database when I attached the new one with a new name. So all of the code I hit against this new database with a new name worked great but my old code hitting my old database name was broken. I need to be able to use both databases. – Volplane

FYI - it showed up as two distinct databases. But somehow didn't work the way I thought it would (and the way you are saying it does). I will try again, just in case, following your instructions exactly.... – Volplane

Answer 5

I had a problem creating a copy of my database as well using SQL Express 2012. I have solved it by the backup and restore method. After making the backup I used: restore -> files and file groups

Next step was to write a new name for the new database and set the source:

Pointing the source file

and finally, a overwrite the existing database with replace must be selected and set names for new files with extension mdf and ldf that are different from the existing where is: Restore as

This method worked for me

Narvaez

Answer 6

Just be aware if you are using SQL Server Express 2012 of going to the option Files and make sure that the destination files (Restore As column) are different from the original files *.mdf and *.log

( I tried to put an image but need 10 reputation :p)

Diastasis

Answer 7

The solution is definitely to create a backup and restore it, but ensure that you're restored copy is pointing to different .mdf and .ldf files.

Here's how to check that using SSMS 2014 and a SQL Server 12 installation: assuming you're creating and restoring backups on your local disk.

• Create a backup of your existing database
  • Right click the database, and choose "back up..." under "tasks."
  • (If you leave the location as the default, you don't have to hunt for the back up in the next step when you restore.)
• Restore your backup to a NEW database:
  • Right click on databases, choose "restore database"
  • Select "device"
  • Click the ellipsis button ("...") to open the "Selct Backup devices" dialog.
  • Choose "File" as the backup media type and click the "add" button
  • Select the backup you just made, click ok (twice)
  • Now, back in the "restore database" dialog, type a new name for your destination database
  • Click "files" under "select a page" and make sure that "restore as" is pointing to .mdf and .ldf file names that do not already exist
  • Click ok!

Anurous

Answer 8

I do not believe the Express version of the manager will have the copy database feature. Have you considered copying via the backup and restore method?

http://msdn.microsoft.com/en-us/library/ms190436.aspx

Karakul

Did that but doesn't work since want to restore ont he same server where this database already exists :( – Volplane

If you restore to a database with a different name, you should also include the option to rename the data files (LDF/NDF/MDF). This prevents Sql from trying to overwrite the data files from the original database. – Nena

@DanPuzey can you elaborate on that? How exactly should the procedure be? – Accidie

Answer 9

I found the problem! Click on Databases, restore, then do the following: After choosing from where to restore, and writing destination db name, go to files [annotation 1 on picture] and change the very right column files names to different than original [annotation 2 on picture] then it works :)

Monoploid

Answer 10

I think you could try import data to a new database.

1. Create an empty database in your local sql server
2. Right click on the new database -> tasks -> import data
3. In the SQL Server Import and Export Wizard, select product env's servername as data source. And select your new database as the destination data.

Seventeenth

Answer 11

I just thought of a really nifty way to get around this :) So I thought I should post my idea. Note that this is 'untested' but I think it will work.

1. Do a "Back Up..." database (theoretically this is on your production server, but it doesn't have to be)
2. Copy the backup file (from your prod server) onto your development machine
3. Assuming you're using SSMS Developer Edition on your development machine, you can then do a "Restore" onto your development machine, then do a "Copy Database" afterwards also on your development machine (to create a new copy of the DB)
4. Now do a "Back Up..." on the new DB you just created, copy the backup file (to your production server) and do a "Restore" on the sql server express server :)

Hope this helps out a few people :)

Cheers,

Jeff

Malarkey

Answer 12

As for the first part of your question (why do you not see this Copy Database Wizard option under Tasks for your dbs), the answer is indeed in the fact that you are running SQL Server Express. SQL Server Express doesn't support the SQL Server Agent feature, which this Copy DB feature relies upon, so the Copy DB feature is not shown (but many online resources fail to make that observation).

Fortunately, most everyone else has addressed the second part of your question (how to achieve the task otherwise), and nearly all point out using the existing backup and restore options (which DO exist in Express), or the export/import (which also exists in Express, I can confirm).

Walburga

Answer 13

Try making a backup of your database, and restoring it into a brand new database.

• Create new DB.
• Make a full backup of your original.
• Right click on your new DB, hit restore.
• Navigate to your .BAK, and ensure the .mdf and .ldf match the new.

Naseberry