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

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

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.