" The job failed" in copying database SQL Server 2012

Question

I'm trying to copy a database. When going through the Copy Database Wizard, I'm getting a Execute SQL Server Agent Job error. The error states

The job failed. Check the event log on the destination server for details

Performing operation

• Add log for package (Success)

• Add task for transferring database objects (Success)

• Create package (Success)

• Start SQL Server Agent Job (Success)

• Execute SQL Server Agent Job (Error)

Error:

The job failed. Check the event log on the destination server for details. (Copy Database Wizard)

I can't seem to find what causing this problem. Am I using the correct 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:

1. Create a backup of the database you want to copy
2. right-click on Databases and select Restore Database
3. Select the database you want to copy from the From Database drop-down list in the Source for restore section
4. Enter the name of the new database in the To database field in the Destination for Restore section - this cannot be the name of an existing database.
5. Click OK

Answer 2

Check the Windows Event Log.

1. Event Viewer
2. Windows Logs
3. Application
4. Look for Warning/Error messages associated with any of the following:
   • SQLAgent
   • SQLISPackage
5. Read the error.

Here is an example.

Here are some of the errors and resolutions we encountered.

Unable to determine if the owner (...) of job ... has server access (reason: Could not obtain information about Windows NT group/user '...', error code 0x54b. [SQLSTATE 42000] (Error 15404)).

We needed to make sure that during the Copy Database Wizard, the account with which we signed in to the destination server had the appropriate privileges and that those privilege were obtainable (we eventually used the sa account.) That resolved the above warning.

Access is denied

We needed to make sure that the local SQL Server Agent has appropriate privileges on the local server. So, we made the SQL Server Agent logon as Local System. That worked because Local System is a sysadmin in our SQL Server instance.

Could not read metadata, possibly due to insufficient access rights.

We needed to give the Local System account further privileges.

• From SSMS
• Right click on the server name and click properties
• Click on the Permissions tab
• Click the user Local System
• On the Explicit Permissions almost at the bottom there is "View Any Definition" grant that. See if that will work.

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

This stymied us and we asked another question: xp_regread() returned error 5, 'Access is denied.'