Job Failure When Copying SQL Server 2012 Database

Question

I am trying to copy a database. When you go through the Copy Database Wizard, I get an SQL Execution SQL query execution error. The error contains

Failed to complete the task. For more information, see the event log on the target server.

Operation

• Add log for package (Success)

• Add a task to transfer database objects (Success)

• Create Package (Success)

• Start SQL Server Agent (success)

• SQL Server Agent Job Execution (Error)

Error:

Failed to complete the task. See the event log on the destination server for more information. (Database Copy Wizard)

I can not find the cause of this problem. Am I taking the right approach? I just need to copy this database. Thanks in advance.

Answer 1

if you are trying to clone your database on the same server, try this:

• Back up the database you want to copy.
• right click on Databases and select Restore Database
• Select the database you want to copy from From Database drop-down list in the Source for restore section
• Enter the name of the new database in the To database field in the Destination for Restore field - this cannot be the name of an existing database.
• Click OK

Answer 2

Check the Windows event log.

• Event Viewer
• Windows Logs
• Application
• Look for warning / error messages related to any of the following:
  • SQLAgent
  • SQLISPackage
• Read the error.

Here is an example.

Here are some of the errors and permissions we encountered.

It is impossible to determine if the owner (...) of the job ... has access to the server (reason: could not get information about the Windows NT group / user "...", error code 0x54b. [SQLSTATE 42000] (Error 15404)) .

We had to make sure that during the copy database wizard, the account with which we were logged in to the target server had the appropriate rights and that these privileges were available (in the end we used the sa account). This resolved the above warning.

Access is denied

We needed to make sure that the local SQL Server agent had the appropriate rights on the local server. Thus, we made the registration of SQL Server Agent as local. This worked because the Local System is sysadmin in our instance of SQL Server.

Failed to read metadata, possibly due to insufficient permissions.

We needed to grant additional rights to the Local System account.

• From SSMS
• Right-click the server name and click Properties
• Click on the Permissions tab.
• Click "Local User System"
• In explicit permissions, the permission "View any definition" is available almost below. See if this will work.

xp_regread () returned error 5, 'Access denied.'

This stopped us and we asked another question: xp_regread () returned error 5, "Access denied."

Answer 3

Usually, since your account (NT Service \ SQLSERVERAGENT ) does not have permission to the data folder (..Microsoft SQL Server \ MSSQL13.MSSQLSERVER \ MSSQL \ DATA). Set it to full control in order

The account is higher by default, if you want to check which running agent is running, go to services.msc. check login