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

  • 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, 2012 at 12:38
  • 2
    @windson Then just script out the database. Also a much easier operation. Jun 22, 2012 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. Jun 22, 2012 at 12:48

4 Answers 4


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.

  • 1
    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, 2012 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. Jun 22, 2012 at 12:50
  • See my edit. It seems as though this wizard isn't available pertaining to SQL Server Express instances. Jun 22, 2012 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.
    – Roi Gavish
    Oct 10, 2012 at 8:53
  • Curiously, the Export Data wizard in SSMS Express explicitly says "To move or copy databases and their objects from one server instance to another, cancel this wizard and use the Copy Database Wizard instead." Feb 7, 2020 at 8:58

I was able to make a copy of a database using SQL Express by choose Backup Database and then choose Restore from database.. quit simple

  • 6
    Why the downvotes? Thomas's answer indicates that SQL Server Express doesn't have the copy database wizard so backup and restore as a new name seems a straight forward alternative. Jun 15, 2013 at 9:56

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.

  • Hi, I ensured all the above limitations in order to get the copy database option. But I could not find the option yet.
    – navule
    Jun 22, 2012 at 14:31
  • 1
    @windson From the screenshot in your question you appear to be using SQL Server Express edition so I'm curious why you accepted this answer when the second half of Thomas's answer appears on the face of it more relevant. Jun 15, 2013 at 10:03

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.

  • It's in my local SSMS 2008 R2. Jun 22, 2012 at 13:41
  • 1
    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, 2012 at 13:57
  • 6
    I'm seeing the same thing. It looks like this isn't available with a SQL Server Express instance. Jun 22, 2012 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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