Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

I am unable to see the Copy Database option in SQL Server Management Studio 2008 R2.

Can anyone tell me whats going on please? Copy Database not found Image

share|improve this question
the source database is completely empty without any rows. I want to copy only the structure with tables and relationships among them to the empty database hosted on server. please see the link blogs.msdn.com/b/jorgepc/archive/2009/01/19/… – windson Jun 22 '12 at 12:38
1  
@windson Then just script out the database. Also a much easier operation. – Thomas Stringer Jun 22 '12 at 12:43
Yeah if it's empty it's just as easy to use the generate scripts option. I'm not sure why the broken wizard option would be missing though, I have 2008 and 2012 installed and it's there in both places. – Aaron Bertrand Jun 22 '12 at 12:48

migrated from stackoverflow.com Jun 22 '12 at 12:43

3 Answers

up vote 2 down vote accepted

Make sure it's not marked for replication, or recovering/suspect/loading/inaccessible.

From the msdn page on this wizard:

The Copy Database Wizard cannot be used to copy or move the following databases.

  • System databases

  • Databases marked for replication.

  • Databases marked Inaccessible, Loading, Offline, Recovering, Suspect, or in Emergency Mode.

share|improve this answer
Hi, I ensured all the above limitations in order to get the copy database option. But I could not find the option yet. – windson Jun 22 '12 at 14:31
up vote 11 down vote

That's because you are clicking on the wrong thing. Don't right-click Databases, but you need to actually right-click the database that you want to copy, and under Tasks there is an option for Copy Database.... In other words, right-click on Database1 and then you'll see this option under Tasks.

As per @AaronBertrand's comment, though, it is highly recommended not to use the Copy Database Wizard. Just go with a backup and restore. It'll be less of a headache.

Edit: It seems as though this option is not available for SQL Server Express edition. When connecting to a SQL Server Express instance, you cannot run the Copy Database wizard. This is due to the fact that the two primary means of executing this task are unavailable in SQL Server Express Edition. First, the Detach/Reattach method relies on SQL Agent (presumably to copy the data and log files from source to destination), and SMO has been removed from 2008 R2, so that method is also unavailable in Express 2008 R2.

share|improve this answer
I'm clicking on the database that I want to copy only. See the symbol golden cylinder in the image which indicates database. – windson Jun 22 '12 at 12:36
@windson Oh, I see. Ok I'd reference JNK's answer, maybe it falls into one of those categories. Either way, my overarching recommendation would be to just "Generate Scripts..." for the database. It'll be much easier and less prone to errors. – Thomas Stringer Jun 22 '12 at 12:50
See my edit. It seems as though this wizard isn't available pertaining to SQL Server Express instances. – Thomas Stringer Jun 22 '12 at 14:07
SMO was introduced with Sql Server 2005, DMO is the deprecated feature. SMO is available for express edition of Sql Server 2008R2. – Justicator Oct 10 '12 at 8:53
up vote 0 down vote

This is odd ... but it looks like that this option is removed in SSMS 2008 R2. I can see it in SSMS 2005 (btw, the link you posted it is using same) And so I think your only option is just to script out the database or restore backup with different name.

share|improve this answer
It's in my local SSMS 2008 R2. – Thomas Stringer Jun 22 '12 at 13:41
Now this is getting really strange. I can see this option if I am connecting to SQL Server 2008 R2 dev instance ... But if I am connecting to Express edition instance on remote server then I don't see that option (I dont have local express instance so I am not sure if it is the same case) .. I wonder if this option is based on edition of instance (Dev, Express, Enterprise etc ) – JackLock Jun 22 '12 at 13:57
2  
I'm seeing the same thing. It looks like this isn't available with a SQL Server Express instance. – Thomas Stringer Jun 22 '12 at 14:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.