通过SMO访问Azure SQL Server会抛出TransferException
本文关键字:TransferException Server SQL SMO 访问 Azure 通过 | 更新日期: 2023-09-27 17:50:26
我试图将一个Azure SQL数据库复制到位于同一服务器上的另一个数据库。我正在使用SMO (System.Data.SqlClient)进行此操作。我知道这不是最有效的方法,但是我的意图是利用这个脚本在我的本地SQL服务器上创建我的Azure SQL数据库的预定副本。
这是我的代码(Dev是原始数据库,Dev- mirror是目标数据库):
var serverConnection = new ServerConnection();
serverConnection.NetworkProtocol = NetworkProtocol.TcpIp;
serverConnection.ConnectionString = "Server=tcp:mysernamename.database.windows.net,1433;User ID=user@server;Password=password;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;";
var server = new Server(serverConnection);
var transfer = new Transfer(server.Databases["Dev"]);
transfer.CopyAllObjects = true;
transfer.CopyAllUsers = true;
transfer.Options.WithDependencies = true;
transfer.DestinationDatabase = server.Databases["Dev-Mirror"].Name;
transfer.DestinationServer = server.Name;
transfer.DestinationLoginSecure = true;
transfer.CopySchema = true;
transfer.CopyData = true;
transfer.Options.ContinueScriptingOnError = true;
transfer.TransferData();
大约两分钟后,这段代码在执行TransferData之后抛出以下TransferException(但是,到服务器本身的连接成功,并且填充了server实例上的Databases属性):
Microsoft.SqlServer.Management.Common.TransferException was unhandled
HResult=-2146233088
Message=An error occurred while transferring data. See the inner exception for details.
Source=Microsoft.SqlServer.SmoExtended
StackTrace:
at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
at SmoDatabaseCopy.Program.Main(String[] args) in c:'Users'bronf_000'Documents'Visual Studio 2013'Projects'SmoDatabaseCopy'SmoDatabaseCopy'Program.cs:line 32
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException: System.Data.SqlClient.SqlException
HResult=-2146232060
Message=A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Source=.Net SqlClient Data Provider
ErrorCode=-2146232060
Class=20
LineNumber=0
Number=2
Server=""
State=0
StackTrace:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
InnerException: System.ComponentModel.Win32Exception
HResult=-2147467259
Message=The system cannot find the file specified
ErrorCode=-2147467259
NativeErrorCode=2
InnerException:
好的,显然答案比我想象的要简单。根据这一点,微软只在Azure SQL上实现了部分SMO,以支持使用该API的SQL Server Management Studio。不建议在Azure SQL上实际使用SMO API
我们最终使用了Microsoft.Sql。Dac库,允许执行完全备份&还原(BAKPAK和BACPAC文件)到您选择的数据库。