database 复制数据库 SQL Server 2012 中的“作业失败”

" The job failed" in copying database SQL Server 2012

提问by mohammad_hasan

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

我正在尝试复制数据库。通过复制数据库向导时，我收到执行 SQL Server 代理作业错误。错误状态

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)

• 为包添加日志（成功）

• 添加传输数据库对象的任务（成功）

• 创建包（成功）

• 启动 SQL Server 代理作业（成功）

• 执行 SQL Server 代理作业（错误）

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.

我似乎无法找到导致此问题的原因。我是否使用了正确的方法？我只需要复制这个数据库。提前致谢。

回答by safi

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 Databasesand select Restore Database
3. Select the database you want to copy from the From Databasedrop-down list in the Source for restoresection
4. Enter the name of the new database in the To databasefield in the Destination for Restoresection - this cannot be the name of an existing database.
5. Click OK

1. 创建要复制的数据库的备份
2. 右键单击Databases并选择Restore Database
3. 从From Database该Source for restore部分的下拉列表中选择要复制的数据库
4. To database在该Destination for Restore部分的字段中 输入新数据库的名称- 这不能是现有数据库的名称。
5. 单击确定

回答by Shaun Luttin

Check the Windows Event Log.

检查 Windows 事件日志。

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.

1. 事件查看器
2. Windows 日志
3. 应用
4. 查找与以下任何一项相关的警告/错误消息：
   • SQL代理
   • SQLI包
5. 阅读错误。

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)).

无法确定作业的所有者 (...) 是否具有服务器访问权限（原因：无法获取有关 Windows NT 组/用户“...”的信息，错误代码 0x54b。[SQLSTATE 42000]（错误 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 saaccount.) That resolved the above warning.

我们需要确保在复制数据库向导期间，我们登录到目标服务器的帐户具有适当的权限，并且这些权限是可获取的（我们最终使用了该sa帐户）。这解决了上述警告。

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.

我们需要确保本地 SQL Server 代理在本地服务器上具有适当的权限。因此，我们将 SQL Server 代理登录为本地系统。这是有效的，因为本地系统是我们 SQL Server 实例中的系统管理员。

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.

• 来自 SSMS
• 右键单击服务器名称，然后单击属性
• 单击权限选项卡
• 单击用户本地系统
• 在几乎在底部的显式权限上有“查看任何定义”授予。看看这是否有效。

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

xp_regread() 返回错误 5，“访问被拒绝。”

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

这阻碍了我们，我们问了另一个问题：xp_regread() 返回错误 5，“访问被拒绝”。

回答by Grey Wolf

It usually because your account (NT Service\SQLSERVERAGENT) don't have permission on data folder (..Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA). Set it to full controll is ok

这通常是因为您的帐户（NT Service\SQLSERVERAGENT）对数据文件夹（..Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA）没有权限。将其设置为完全控制就可以了

Account above is default, if you want to check which account running agent, go to services.msc . check account logon

上面的账户是默认的，如果你想查看哪个账户运行了代理，去 services.msc 。检查帐户登录

回答by Chip

For My issue with Copy Database Wizard: Error: Value cannot be null. Parameter name: database StackTrace: at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.CheckLocalandDestinationStatus

对于我的复制数据库向导问题：错误：值不能为空。参数名称：数据库 StackTrace：在 Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.CheckLocalandDestinationStatus

I had tried everything in trying to get my Copy Database wizard to work through a schedule from SQL 2012 to SQL2017 1. I had set the SQL Agent account to an administrator, even though I had already set up a proxy that was a sysadmin 2. I had gone into data tools, and tried to change the max errors to more than one.

我已经尝试了所有尝试让我的复制数据库向导按照从 SQL 2012 到 SQL2017 1 的计划工作。我已将 SQL 代理帐户设置为管理员，即使我已经设置了一个代理，即 sysadmin 2。我进入了数据工具，并试图将最大错误数更改为多个。

The final steps I took that worked were as follows: A. I installed data tools on the 2017 server (just in case I needed them) B. I added a dummy database on the 2017 server just so I could get to Copy Database wizard from inside SSMS. C. I started the opy database wizard from my NEWER system from the dummy database, but changed my source to the source server(instead of the default of the local SQL instance), and respectively changed my target to my local machine SQL instance.

我采取的最终步骤如下： A. 我在 2017 服务器上安装了数据工具（以防万一我需要它们） B. 我在 2017 服务器上添加了一个虚拟数据库，以便我可以从SSMS 内。C、我从我的NEWER系统从虚拟数据库启动了opy数据库向导，但是将我的源更改为源服务器（而不是本地SQL实例的默认值），并将我的目标分别更改为我的本地机器SQL实例。

D. I went through the wizard for each item (for my case, it was a copy and replace option, using SSMS (not detach and re-attach as I couldn't bring down the source on production), and I clicked REFRESH on each database copy wizard screen AFTER changing the destination directory.

D. 我浏览了每个项目的向导（就我而言，它是一个复制和替换选项，使用 SSMS（不是分离和重新附加，因为我无法关闭生产源），然后我点击了 REFRESH更改目标目录后的每个数据库复制向导屏幕。

(ONE CAVIAT IF MIGRATING DATABASES ON SSMS 2017 for SQL 2017. Make sure you have the latest cumulative update for the SQL version: https://support.microsoft.com/en-us/help/4342123vs. select @@version in a query

（如果在 SSMS 2017 上为 SQL 2017 迁移数据库，请确保您拥有 SQL 版本的最新累积更新：https: //support.microsoft.com/en-us/help/4342123vs. select @@version in一个问题

Also make sure youre SQL agent and SQL server accounts have permission to the target directory)

还要确保您的 SQL 代理和 SQL 服务器帐户对目标目录具有权限）

Following that, my script finally worked running from the SQL2017 server, having been added to SQL Agent Jobs

之后，我的脚本终于可以从 SQL2017 服务器运行，并已添加到 SQL 代理作业

回答by James Burnett

For me, I was copying a db to my localhost.

对我来说，我正在将一个数据库复制到我的本地主机。

1. I changed the agent service to run as myself.
2. Added a missing role on my localhost since the eventlog said "Cannot alter the server role 'Developer', because it does not exist or you do not have permission"
3. saw this "InnerException --> The procedure 'sys.sp_procoption' cannot be executed within a transaction.". So, I changed the wizard setting to not copy stored procedures.
4. Next error was "Property HasMemoryOptimizedObjects is not available for Database '[TCCPortal_UAT]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights" which I couldn't fix quickly & easily by myself.

1. 我将代理服务更改为以我自己的身份运行。
2. 在我的本地主机上添加了一个缺失的角色，因为事件日志显示“无法更改服务器角色‘开发者’，因为它不存在或您没有权限”
3. 看到这个“InnerException --> 过程'sys.sp_procoption'不能在事务中执行。”。因此，我将向导设置更改为不复制存储过程。
4. 下一个错误是“属性 HasMemoryOptimizedObjects 不适用于数据库‘[TCCPortal_UAT]’。此对象可能不存在此属性，或者由于访问权限不足而无法检索”，我自己无法快速轻松地修复该错误。

At that point, I gave up & used the SQL Server Import and Export wizard which seemed to work ok for what I needed. https://docs.microsoft.com/en-us/sql/integration-services/import-export-data/import-and-export-data-with-the-sql-server-import-and-export-wizard?view=sql-server-2017

那时，我放弃了并使用了 SQL Server 导入和导出向导，它似乎可以满足我的需要。 https://docs.microsoft.com/en-us/sql/integration-services/import-export-data/import-and-export-data-with-the-sql-server-import-and-export-wizard？视图=sql-server-2017

Note that using the import and export wizard works great with the tables. I did have to copy the views, stored procedures and functions separately by rightclick database->Tasks->generate scripts and then the wizard will walk you through which objects you can select.

请注意，使用导入和导出向导可以很好地处理表格。我确实必须通过右键单击数据库-> 任务-> 生成脚本来分别复制视图、存储过程和函数，然后向导将引导您完成可以选择的对象。

Good luck, I hope this helps someone.

祝你好运，我希望这对某人有所帮助。