copy a database within SQL Server Express?
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
In SSMS 2008 you can do this:
Create a backup of the database you want to copy
In SSMS, right-click 'Databases' and select 'Restore Database'
Select the database you wish to copy from the 'From database' drop-down list in the 'Source for restore' section
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.
Click OK
You're done! :)