I'm trying to copy a database from our live system to the test system. (same Server, different DB)
The Test DB has been deleted in preparation so we have a clean slate. I've tried backing up the DB, then restoring it but it fails saying it doesn't have exclusive access to the DB. (I don't understand this as the backup file is just a .bak file, wby would it not have access? and if it means the destination DB. it has been deleted so shouldn't even be there to interfere?)
I've googled the issue, there are no query windows open. I'm the only one connected to the sql server, default db is set to master. These are all easy things I could check but didn't help.
Someone recommended using Copy Database... instead so...
So I go through the wizard, choose the db to copy, choose the name for the test one, (same server,) and then it runs and fails.
Here's the log:
#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.
I am a SQL Novice, so can you please provide a step by step if there is something I need to do.
Names have been changed to protect the innocent:
Server name: SQLSERVER. Original DB name: LIVE_DB. Destination DB name: TEST_DB. This is all being run under SA account.
If you need more info please let me know
