Я пытаюсь скопировать базу данных из нашей действующей системы в тестовую. (тот же сервер, другая БД)
Тестовая база данных была удалена в процессе подготовки, так что у нас есть чистый лист. Я попытался создать резервную копию БД, а затем восстановить ее, но он не может сказать, что у него нет монопольного доступа к БД. (Я не понимаю этого, поскольку файл резервной копии - это просто файл .bak, wby не будет ли у него доступа? И если это означает целевую БД. Он был удален, поэтому он даже не должен вмешиваться?)
Я погуглил проблему, нет открытых окон запросов. Я единственный, кто подключен к серверу sql, по умолчанию для db установлено значение master. Это все простые вещи, которые я мог проверить, но не помогло.
Кто-то рекомендовал использовать Copy Database ... вместо этого ...
Итак, я прохожу через мастер, выбираю базу данных для копирования, выбираю имя для тестовой (тот же сервер), а затем он запускается и терпит неудачу.
Вот журнал:
#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.
Если вам нужна дополнительная информация, дайте мне знать
Устранена проблема путем создания пустой базы данных с правильными данными и восстановления поверх нее.