SSMS 2012 : Copy Database fails

Question

Hi everyone,

I successfully installed SQL Server Standard 2012 (standalone, non-clustered) as a new instance alongside my existing Standard 2008 R2 installation. Both 2008 R2 & 2012 installations & user database files are on the same physical machine. I'm having trouble in SSMS with (DatabaseName)\Tasks\Copy Database. I've tried many permutations of the wizard options. All efforts fail with the same message in Windows Event Log:

"Failed to connect to server COMPUTER2010\SQLSTANDARD08R2."

Here's what I've checked so far in trying to resolve this:

• In SSMS (2012), connected to instance 2008 R2, I verified that the number of connections is unlimited.
• In SS Configuration Manager (2008 R2) I verified that the 2008 R2 Engine & Agent are running.
• In SS Configuration Manager (2008 R2) I verified that all three Client Protocols are enabled.
• I verified that the TCP/IP  port number in SS Configuration Manager (2008 R2) for SS 2008 R2 in different than the port number in SS Configuration Manager (2012) for SS 2012.

Questions :

1. My goal is to upgrade my user databases from 2008 R2 to 2012.  Am I correct in using SSMS 2012, connecting to instance 2008 R2 & then manually running the Copy Database wizard on each user database? (This is the process I have been following, which generated the above errors.)
2. Should I consider (within SSMS 2012) manually detaching each user database, moving the files to the appropriate 2012 folder, & reattaching..? If I go this route, what "upgrades" do I miss versus the wizard-driven Copy process?

Thanks for your assistance,

Brad

Answer 1

Hello,

Can you connect to the new SQL 2012 instance using SSMS 2012?

I strongly suggest you to use SSMS 2012 to detach the databases from SQL 2008 R2 instance and attached them back to SQL 2012. This is the right way to perform an side-by-side upgrade. Do not forget to backup the databases before starting. I've done this approach several times (including the one you have mentioned). 

Is the original SQL 2008 R2 Standard Edition as well? if it is 2008 R2 EE consider that 2012 SE does not have all the Enterprise Features

---

Javier Villegas | @javier_vill | http://sql-javier-villegas.blogspot.com/

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 2

Hi Javier,

Thanks for your reply & suggestion.

>Can you connect to the new SQL 2012 instance using SSMS 2012?

Yes, I should have mentioned this : I can connect to SQL 2012 using SSMS 2012, but of course there are only the system databases & no user databases.

> Is the original SQL 2008 R2 Standard Edition as well? 

Yes, both my 2008 R2 & 2012 installations are Standard edition. 

> I strongly suggest you to use SSMS 2012 to detach the databases from SQL 2008 R2 instance and attached them back to SQL 2012.

So you're suggesting to forget about the "Copy Database" wizard?  I guess I'm OK with that but since it's the MS-recommended method I think it would be good to understand why the wizard fails..?

I'll try the detach-move-reattach method & report back.

Thanks..

---

Brad

Answer 3

DCW creates Agent job on the destnation server which connects to the source server and does its stuff. I consider this to be a fragile process whcih doesn't really do anything (much) for you compared to backup/restore (my preference) or detach/attach.

If you insist on using CDW: I'd try connecting from the destination machine to the service machine. Using both the SQL Server Service account as well as the Agent account. And then chase the error history from the agent job from there. But, again, I wouldn't bother. Here's my take on moving a database: http://www.karaszi.com/SQLServer/info_moving_database.asp

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 4

Hi Tibor,

Thanks very much for your comments & the referral to your blog posting. 

>If you insist on using CDW: I'd try connecting from the destination machine to the service machine...

Just to clarify, both SQL server installations & the user databases are on the same physical machine. But I get your point : don't bother trying to use the Copy Database Wizard.

Thanks..

---

Brad

Answer 5

Hi Tibor,

Thanks very much for your comments & the referral to your blog posting. 

>If you insist on using CDW: I'd try connecting from the destination machine to the service machine...

Just to clarify, both SQL server installations & the user databases are on the same physical machine. But I get your point : don't bother trying to use the Copy Database Wizard.

---

Hi BradStiritz,

Regarding to your description, seems you want to move SQL Server 2008 R2 user database to SQL Server 2012. You can tired to use backup/restore or detach/attach methods.

If you want to use CDW, you can refer to this article to follow the steps.  Use the Copy Database Wizard http://msdn.microsoft.com/en-us/library/ms188664.aspx

---

Regards, Amber zhang

Answer 6

Hi Amber,

Thanks for your posting & reference to the MSDN article. Unfortunately, the article does not discuss troubleshooting errors that may occur & how to resolve them. As I mentioned in my original posting :

 >I've tried many permutations of the wizard options. All efforts fail with the same message in Windows Event Log:

>"Failed to connect to server COMPUTER2010\SQLSTANDARD08R2."

Do you possibly have some advice please or a reference which discusses this particular problem..?

Thanks,

Brad

---

Brad

Answer 7

Hi BradStiritz,

>> Failed to connect to server COMPUTER2010\SQLSTANDARD08R2

Thank you for your update.  The error message you provided, which is a general error might cause by many issues.  Could you try to copy a backup file to the server and restore.

Move a database from one server to another server, there a many ways to move/copy database. You can try one of the following as you want.
1. Detach the database from the old server and attach it in the new server.
2. Back up the database in the old server and restore it in the destination server.
3. The last type is to generate the create script using Generate Script Wizard (SSMS) and execute it in the destination server.
4. Using copy database wizard in SQL Server Management Studio.

For more information, please refer to this blog.

---

Regards, Amber zhang