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?

Answer 1

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

Answer 2

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.

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.

Answer 4

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.

Answer 5

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

Answer 6

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! :)