![SQL Server 2012 测试数据库复制数据库失败](https://linux22.com/image/684132/SQL%20Server%202012%20%E6%B5%8B%E8%AF%95%E6%95%B0%E6%8D%AE%E5%BA%93%E5%A4%8D%E5%88%B6%E6%95%B0%E6%8D%AE%E5%BA%93%E5%A4%B1%E8%B4%A5.png)
我正在尝试将数据库从我们的实时系统复制到测试系统。(同一服务器,不同的数据库)
测试数据库已在准备过程中被删除,因此我们有一个干净的记录。我尝试备份数据库,然后恢复它,但失败了,说它没有对数据库的独占访问权限。(我不明白这一点,因为备份文件只是一个 .bak 文件,为什么它没有访问权限?如果它指的是目标数据库。它已被删除,所以不应该在那里干扰?)
我已经在 Google 上搜索过这个问题,没有打开查询窗口。我是唯一一个连接到 SQL 服务器的人,默认数据库设置为 master。这些都是我可以检查的简单事项,但没有帮助。
有人建议使用复制数据库...所以...
因此我按照向导,选择要复制的数据库,选择测试的名称(同一台服务器),然后它运行并失败。
以下是日志:
#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message
OnPreValidate,SQLSERVER,NT Service\SQLSERVERAGENT,CDW_SQLSERVER_SQLSERVER_1,{A77E694E-5A0D-4244-A8E7-07B0F384B94},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:52,06/06/2016 12:31:52,0,0x,
OnPreValidate,SQLSERVER,NT Service\SQLSERVERAGENT,SQLSERVER_SQLSERVER_Transfer Objects Task,{6B0681DE-D895-4E61-82B0-57682BB61E50},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:52,06/06/2016 12:31:52,0,0x,
OnPostValidate,SQLSERVER,NT Service\SQLSERVERAGENT,SQLSERVER_SQLSERVER_Transfer Objects Task,{6B0681DE-D895-4E61-82B0-57682BB61E50},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:52,06/06/2016 12:31:52,0,0x,
OnPostValidate,SQLSERVER,NT Service\SQLSERVERAGENT,CDW_SQLSERVER_SQLSERVER_1,{A77E694E-5A0D-4244-A8E7-07B0DF384B94},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:52,06/06/2016 12:31:52,0,0x,
PackageStart,SQLSERVER,NT Service\SQLSERVERAGENT,CDW_SQLSERVER_SQLSERVER_1,{A77E694E-5A0D-4244-A8E7-07B0DF384B94},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:52,06/06/2016 12:31:52,0,0x,Beginning of package execution.
Diagnostic,SQLSERVER,NT Service\SQLSERVERAGENT,CDW_SQLSERVER_SQLSERVER_1,{A77E694E-5A0D-4244-A8E7-07B0DF384B94},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:52,06/06/2016 12:31:52,0,0x,Maximum concurrent executables are set to 1.
DiagnosticEx,SQLSERVER,NT Service\SQLSERVERAGENT,CDW_SQLSERVER_SQLSERVER_1,{A77E694E-5A0D-4244-A8E7-07B0DF384B94},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:52,06/06/2016 12:31:52,0,0x,<?xml version="1.0"?><DTS:ParameterValues xmlns:DTS="www.microsoft.com/SqlServer/Dts"/>
OnPreExecute,SQLSERVER,NT Service\SQLSERVERAGENT,CDW_SQLSERVER_SQLSERVER_1,{A77E694E-5A0D-4244-A8E7-07B0DF384B94},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:52,06/06/2016 12:31:52,0,0x,
OnPreExecute,SQLSERVER,NT Service\SQLSERVERAGENT,SQLSERVER_SQLSERVER_Transfer Objects Task,{6B0681DE-D895-4E61-82B0-57682BB61E50},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:52,06/06/2016 12:31:52,0,0x,
OnPreValidate,SQLSERVER,NT Service\SQLSERVERAGENT,SQLSERVER_SQLSERVER_Transfer Objects Task,{6B0681DE-D895-4E61-82B0-57682BB61E50},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:52,06/06/2016 12:31:52,0,0x,
OnPostValidate,SQLSERVER,NT Service\SQLSERVERAGENT,SQLSERVER_SQLSERVER_Transfer Objects Task,{6B0681DE-D895-4E61-82B0-57682BB61E50},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:52,06/06/2016 12:31:52,0,0x,
OnProgress,SQLSERVER,NT Service\SQLSERVERAGENT,SQLSERVER_SQLSERVER_Transfer Objects Task,{6B0681DE-D895-4E61-82B0-57682BB61E50},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:52,06/06/2016 12:31:52,0,0x,Task just started the execution.
OnInformation,SQLSERVER,NT Service\SQLSERVERAGENT,SQLSERVER_SQLSERVER_Transfer Objects Task,{6B0681DE-D895-4E61-82B0-57682BB61E50},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:52,06/06/2016 12:31:52,0,0x,Connecting to server SQLSERVER
OnInformation,SQLSERVER,NT Service\SQLSERVERAGENT,CDW_SQLSERVER_SQLSERVER_1,{A77E694E-5A0D-4244-A8E7-07B0DF384B94},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:52,06/06/2016 12:31:52,0,0x,Connecting to server SQLSERVER
OnInformation,SQLSERVER,NT Service\SQLSERVERAGENT,SQLSERVER_SQLSERVER_Transfer Objects Task,{6B0681DE-D895-4E61-82B0-57682BB61E50},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:53,06/06/2016 12:31:53,0,0x,Connected to server SQLSERVER
OnInformation,SQLSERVER,NT Service\SQLSERVERAGENT,CDW_SQLSERVER_SQLSERVER_1,{A77E694E-5A0D-4244-A8E7-07B0DF384B94},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:53,06/06/2016 12:31:53,0,0x,Connected to server SQLSERVER
OnInformation,SQLSERVER,NT Service\SQLSERVERAGENT,SQLSERVER_SQLSERVER_Transfer Objects Task,{6B0681DE-D895-4E61-82B0-57682BB61E50},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:53,06/06/2016 12:31:53,0,0x,Connecting to server SQLSERVER
OnInformation,SQLSERVER,NT Service\SQLSERVERAGENT,CDW_SQLSERVER_SQLSERVER_1,{A77E694E-5A0D-4244-A8E7-07B0DF384B94},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:53,06/06/2016 12:31:53,0,0x,Connecting to server SQLSERVER
OnInformation,SQLSERVER,NT Service\SQLSERVERAGENT,SQLSERVER_SQLSERVER_Transfer Objects Task,{6B0681DE-D895-4E61-82B0-57682BB61E50},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:53,06/06/2016 12:31:53,0,0x,Connected to server SQLSERVER
OnInformation,SQLSERVER,NT Service\SQLSERVERAGENT,CDW_SQLSERVER_SQLSERVER_1,{A77E694E-5A0D-4244-A8E7-07B0DF384B94},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:53,06/06/2016 12:31:53,0,0x,Connected to server SQLSERVER
OnInformation,SQLSERVER,NT Service\SQLSERVERAGENT,SQLSERVER_SQLSERVER_Transfer Objects Task,{6B0681DE-D895-4E61-82B0-57682BB61E50},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:53,06/06/2016 12:31:53,0,0x,Transferring server objects to server SQLSERVER from SQLSERVER
OnInformation,SQLSERVER,NT Service\SQLSERVERAGENT,CDW_SQLSERVER_SQLSERVER_1,{A77E694E-5A0D-4244-A8E7-07B0DF384B94},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:53,06/06/2016 12:31:53,0,0x,Transferring server objects to server SQLSERVER from SQLSERVER
OnInformation,SQLSERVER,NT Service\SQLSERVERAGENT,SQLSERVER_SQLSERVER_Transfer Objects Task,{6B0681DE-D895-4E61-82B0-57682BB61E50},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:53,06/06/2016 12:31:53,0,0x,Transferring server objects to server SQLSERVER from SQLSERVER
OnInformation,SQLSERVER,NT Service\SQLSERVERAGENT,CDW_SQLSERVER_SQLSERVER_1,{A77E694E-5A0D-4244-A8E7-07B0DF384B94},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:53,06/06/2016 12:31:53,0,0x,Transferring server objects to server SQLSERVER from SQLSERVER
OnInformation,SQLSERVER,NT Service\SQLSERVERAGENT,SQLSERVER_SQLSERVER_Transfer Objects Task,{6B0681DE-D895-4E61-82B0-57682BB61E50},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:53,06/06/2016 12:31:53,0,0x,Transferred server objects to SQLSERVER
OnInformation,SQLSERVER,NT Service\SQLSERVERAGENT,CDW_SQLSERVER_SQLSERVER_1,{A77E694E-5A0D-4244-A8E7-07B0DF384B94},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:53,06/06/2016 12:31:53,0,0x,Transferred server objects to SQLSERVER
OnInformation,SQLSERVER,NT Service\SQLSERVERAGENT,SQLSERVER_SQLSERVER_Transfer Objects Task,{6B0681DE-D895-4E61-82B0-57682BB61E50},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:53,06/06/2016 12:31:53,0,0x,Transferring database LIVE_DB from SQLSERVER server as TEST_DB to SQLSERVER server
OnInformation,SQLSERVER,NT Service\SQLSERVERAGENT,SQLSERVER_SQLSERVER_Transfer Objects Task,{6B0681DE-D895-4E61-82B0-57682BB61E50},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:53,06/06/2016 12:31:53,0,0x,Added data file LIVE_DB in path E:\Microsoft SQL Server\MSSQL11.SQLSERVER\MSSQL\DATA\TEST_DB.mdf to file group [PRIMARY]
OnInformation,SQLSERVER,NT Service\SQLSERVERAGENT,CDW_SQLSERVER_SQLSERVER_1,{A77E694E-5A0D-4244-A8E7-07B0DF384B94},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:53,06/06/2016 12:31:53,0,0x,Added data file LIVE_DB in path E:\Microsoft SQL Server\MSSQL11.SQLSERVER\MSSQL\DATA\TEST_DB.mdf to file group [PRIMARY]
OnInformation,SQLSERVER,NT Service\SQLSERVERAGENT,SQLSERVER_SQLSERVER_Transfer Objects Task,{6B0681DE-D895-4E61-82B0-57682BB61E50},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:53,06/06/2016 12:31:53,0,0x,Added log file LIVE_DB_Log in path E:\Microsoft SQL Server\MSSQL11.SQLSERVER\MSSQL\DATA\TEST_DB_log.ldf
OnInformation,SQLSERVER,NT Service\SQLSERVERAGENT,CDW_SQLSERVER_SQLSERVER_1,{A77E694E-5A0D-4244-A8E7-07B0DF384B94},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:53,06/06/2016 12:31:53,0,0x,Added log file LIVE_DB_Log in path E:\Microsoft SQL Server\MSSQL11.SQLSERVER\MSSQL\DATA\TEST_DB_log.ldf
OnInformation,SQLSERVER,NT Service\SQLSERVERAGENT,SQLSERVER_SQLSERVER_Transfer Objects Task,{6B0681DE-D895-4E61-82B0-57682BB61E50},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:53,06/06/2016 12:31:53,0,0x,Transferring data to database TEST_DB from LIVE_DB
OnInformation,SQLSERVER,NT Service\SQLSERVERAGENT,CDW_SQLSERVER_SQLSERVER_1,{A77E694E-5A0D-4244-A8E7-07B0DF384B94},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:31:53,06/06/2016 12:31:53,0,0x,Transferring data to database TEST_DB from LIVE_DB
OnError,SQLSERVER,NT Service\SQLSERVERAGENT,SQLSERVER_SQLSERVER_Transfer Objects Task,{6B0681DE-D895-4E61-82B0-57682BB61E50},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:32:35,06/06/2016 12:32:35,0,0x,An error occurred while transferring data. See the inner exception for details.
StackTrace: at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
InnerException-->Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SqlServer.Management.Smo.Transfer.ExecuteStatements(SqlConnection destinationConnection, IEnumerable`1 statements, SqlTransaction transaction)
at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
OnError,SQLSERVER,NT Service\SQLSERVERAGENT,CDW_SQLSERVER_SQLSERVER_1,{A77E694E-5A0D-4244-A8E7-07B0DF384B94},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:32:35,06/06/2016 12:32:35,0,0x,An error occurred while transferring data. See the inner exception for details.
StackTrace: at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
InnerException-->Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SqlServer.Management.Smo.Transfer.ExecuteStatements(SqlConnection destinationConnection, IEnumerable`1 statements, SqlTransaction transaction)
at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
OnProgress,SQLSERVER,NT Service\SQLSERVERAGENT,SQLSERVER_SQLSERVER_Transfer Objects Task,{6B0681DE-D895-4E61-82B0-57682BB61E50},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:32:35,06/06/2016 12:32:35,0,0x,Database transfer failed for 1 database(s).
OnProgress,SQLSERVER,NT Service\SQLSERVERAGENT,SQLSERVER_SQLSERVER_Transfer Objects Task,{6B0681DE-D895-4E61-82B0-57682BB61E50},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:32:35,06/06/2016 12:32:35,100,0x,Transfer objects finished execution.
OnTaskFailed,SQLSERVER,NT Service\SQLSERVERAGENT,SQLSERVER_SQLSERVER_Transfer Objects Task,{6B0681DE-D895-4E61-82B0-57682BB61E50},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:32:35,06/06/2016 12:32:35,0,0x,
OnPostExecute,SQLSERVER,NT Service\SQLSERVERAGENT,SQLSERVER_SQLSERVER_Transfer Objects Task,{6B0681DE-D895-4E61-82B0-57682BB61E50},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:32:35,06/06/2016 12:32:35,0,0x,
OnWarning,SQLSERVER,NT Service\SQLSERVERAGENT,CDW_SQLSERVER_SQLSERVER_1,{A77E694E-5A0D-4244-A8E7-07B0DF384B94},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:32:35,06/06/2016 12:32:35,-2147381246,0x,SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
OnPostExecute,SQLSERVER,NT Service\SQLSERVERAGENT,CDW_SQLSERVER_SQLSERVER_1,{A77E694E-5A0D-4244-A8E7-07B0DF384B94},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:32:35,06/06/2016 12:32:35,0,0x,
DiagnosticEx,SQLSERVER,NT Service\SQLSERVERAGENT,CDW_SQLSERVER_SQLSERVER_1,{A77E694E-5A0D-4244-A8E7-07B0DF384B94},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:32:35,06/06/2016 12:32:35,0,0x,<?xml version="1.0"?><DTS:ProcessMemoryUsage xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:PeakWorkingSetSize>105385984</DTS:PeakWorkingSetSize></DTS:ProcessMemoryUsage>
PackageEnd,SQLSERVER,NT Service\SQLSERVERAGENT,CDW_SQLSERVER_SQLSERVER_1,{A77E694E-5A0D-4244-A8E7-07B0DF384B94},{47E1B488-1421-49AE-A0D0-9F95D44B8EFB},06/06/2016 12:32:35,06/06/2016 12:32:35,1,0x,End of package execution.
我是 SQL 新手,因此如果我需要做什么,您能否提供分步说明。
为了保护无辜者,名字均已更改:
服务器名称:SQLSERVER。原始数据库名称:LIVE_DB。目标数据库名称:TEST_DB。所有这些都在 SA 帐户下运行。
如果您需要更多信息,请告诉我
答案1
通过创建具有正确详细信息的空白数据库并在其上进行恢复来解决了该问题。