March 22, 2012 at 11:14 am
Hi..
I am trying to use SSIS to copy a database to another server, but am getting an error a network share error. This is the error I am receiving:
[Transfer Database Task] Error: The following exception occurred during an offline database transfer: Microsoft.SqlServer.Management.Smo.FailedOperationException: Attach database failed for Server 'DRSTEST'. ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: The file "\\DRSTEST\c$\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\NS_Test.mdf" is on a network path that is not supported for database files.
I have shared the folders on both the source and destination servers. I used this path for the source server files:
\\DRSDEV\c$\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\NS_Test.mdf
I have searched and searched on Google and read BOL. I still have not found an answer. Can someone please offer some advice?
Thanks for your help!
NS
March 22, 2012 at 1:22 pm
I got it working..
March 23, 2012 at 11:14 am
Since you couldn't find the answer when you searched, could you post the solution that you used for someone else searching in the future?
March 23, 2012 at 11:41 am
Probably a trace flag to allow SQL Server to use a network share. I know I have seen that somewhere on ssc, just can't remember where. With that, yes, please post how you solved your problem.
March 23, 2012 at 12:39 pm
Now it is not working... I tinkered and it does not work... I have to configure network shares on both sides, right?
ERROR:
[Transfer Database Task] Error: The following exception occurred during an offline database transfer: Microsoft.SqlServer.Management.Smo.FailedOperationException: Attach database failed for Server 'DRSDEV'. ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: The file "\\DRSDEV\c$\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\iMedRIS_test.mdf" is on a network path that is not supported for database files.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
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.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Server.AttachDatabaseWorker(String name, StringCollection files, String owner, AttachOptions attachOptions)
at Microsoft.SqlServer.Management.Smo.Server.AttachDatabase(String name, StringCollection files)
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.Smo.Server.AttachDatabase(String name, StringCollection files)
at Microsoft.SqlServer.Dts.Tasks.TransferDatabaseTask.TransferDatabaseTask.OfflineTransfer(Server serverSrc, Server serverDest, IDTSComponentEvents events, IDTSLogging log).
DESTINATION:
"iMedRIS_test.mdf","\\DRSDEV\c$\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA","\\DRSDEV\c$\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA";"iMedRIS_test_log.ldf","\\DRSDEV\c$\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA","\\DRSDEV\c$\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA"
SOURCE:
"iMedRIS_Test1.mdf","\\DRSTEST\c$\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA","\\DRSTEST\c$\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA";"iMedRIS_Test1_log.ldf","\\DRSTEST\c$\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA","\\DRSTEST\c$\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA"
Any ideas are GREATLY appreciated!
Thanks so much!
March 26, 2012 at 4:58 am
noelle.stimely (3/23/2012)
Now it is not working... I tinkered and it does not work... I have to configure network shares on both sides, right?
What did you tinker with? What did you do that made it start working and what did you do after that?
March 26, 2012 at 7:47 am
I did not realize I had changed the primary database to single user mode for some testing. Stupid! I changed it back and now it works!
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply