Copy Database Wizard truly GARBAGE! Always Errors

Question

With that off of my chest....

I've tried using CDW from one db to another, on the same server, and from local (Standard) to cloud ( Web),  and even db to db on the cloud server.   I've tried both the detach and object methods.

IN ALL CASES,  "THE JOB FAILED. CHECK THE EVENT LOG ON THE DESTINATION SERVER FOR DETAILS."

The sql server logs ARE CLEAN.

The sql server agent logs ARE CLEAN.

The NT/ Application log is not beginning with...

OnError

NT AUTHORITY\NETWORK SERVICE

GRANDRAPIDS_GRANDRAPIDS_Transfer Objects Task

{D8493497-A0C5-4279-9A5D-430FF3351679}

{086A789D-C3BD-40E9-A6F3-9A939F719DF8}

4/22/2012 5:37:04 PM

4/22/2012 5:37:04 PM

0

Access to the path 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TMM12_new.mdf' is denied. StackTrace: at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.File.InternalCopy(String sourceFileName, String destFileName, Boolean overwrite) at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.CopyFile(String sourceFileName, String destinationFileName, Boolean overwriteOnExist)

This appears to be a "rights issue" *But* a search cannot even find TMM12_new.mdf! 

---

Myron Oakley

Answer 1

PS,  Does EXACTLY the same thing, on a totally different SQL server.

---

Myron Oakley

Answer 2

5:37:04 PM

4/22/2012 5:37:04 PM

0

Access to the path 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TMM12_new.mdf' is denied. StackTrace: at

This appears to be a "rights issue" *But* a search cannot even find TMM12_new.mdf! 

Hello Myron,

Of course you can't find the file TMM12_new.mdf, because as the error message says you don't have the permission to create the file.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 3

Hi Myron,

If you are using Copy Database Wizard, please make sure that the SQL Server Agent account or Integration Services Proxy account is a sysadmin fixed server role, and has access permission on both of source and target file system.

TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Stephanie Lv

TechNet Community Support

Answer 4

Ok, I got it to work locally on each SQL server. I added "SQLServerSQLAgentUser"  to the sql DATA folder, "Full Control".

I still am not able to copy between SQL servers.  I suspect it's an inter domain issue.  A while back had to scrap MS CRM installation due to a problem like this, which basically nobody had an answer for.

The serious bit about all of this is *why* SQL server would have this tool installed when it won't even run in a default installation.  Add to the fact the error messages, from the tool itself, are worthless.   

I will probably just go with an aftermarket sync tool that works. 

 

 

---

Myron Oakley

Answer 5

Ok, I got it to work locally on each SQL server. I added "SQLServerSQLAgentUser"  to the sql DATA folder, "Full Control".

I still am not able to copy between SQL servers.  I suspect it's an inter domain issue.  A while back had to scrap MS CRM installation due to a problem like this, which basically nobody had an answer for.

The serious bit about all of this is *why* SQL server would have this tool installed when it won't even run in a default installation.  Add to the fact the error messages, from the tool itself, are worthless.   

I will probably just go with an aftermarket sync tool that works. 

 

---

Myron Oakley

SQL security is set up in a restrictive way by default and you need to adapt it to your situation. If you want to copy from one machine to another then you need to set up your SQL Agent to use a domain account that has permissions on both servers or create a proxy account for your job to use.

---

Chuck