I am unable to see the Copy Database option in SQL Server Management Studio 2008 R2.
Can anyone tell me whats going on please?
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 only takes a minute to sign up.Sign up to join this community
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.
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.
Databases marked for replication.
Databases marked Inaccessible, Loading, Offline, Recovering, Suspect, or in Emergency Mode.