Sql-Server
在 SQL Server 上導入具有 25000 個表的 bacpac 文件失敗並超時
我想導入一個從 500MB 數據庫中導出的 bacpac 文件，但該數據庫有大約 25000 個表。bacpac 本身大約 18MB。
通過 SQL Server Management Studio 的 UI 導入失敗並出現 OutOfMemoryException，因此我將按以下方式使用 SqlPackage.exe：
"C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe" /Action:Import /SourceFile:"path\to\MyDB.bacpac" /TargetConnectionString:"Data Source=localhost;Initial Catalog=MyDB;Integrated Security=True;"我成功地使用此命令對數據庫的先前（較小，表較少）版本進行了導入，但現在它失敗並顯示以下輸出。我可以在這裡做什麼嗎？
Importing to database 'MyDB' on server 'localhost'. Creating deployment plan Initializing deployment Verifying deployment plan Analyzing deployment plan Importing package schema and data into database Updating database An unexpected failure occurred: Data plan execution failed with message Unable t o reconnect to database: Timeout expired. The timeout period elapsed prior to c ompletion of the operation or the server is not responding.. Unhandled Exception: Microsoft.SqlServer.Dac.Data.DataException: Data plan execu tion failed with message Unable to reconnect to database: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlReverseEngineer Exception: Unable to reconnect to database: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. - --> System.Data.SqlClient.SqlException: Timeout expired. The timeout period ela psed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out --- End of inner exception stack trace --- at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolea n breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObj ect stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, Run Behavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBe havior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 time out, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameter EncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehav ior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletio nSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehav ior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, S tring method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlReverseEngineerImpl.Execute Populators(Tuple`2 connectionTuple, IList`1 populators, Int32 totalPopulatorsCou nt, Int32 startIndex, Boolean progressAlreadyUpdated, ReverseEngineerOption opti on, SqlReverseEngineerRequest request) --- End of inner exception stack trace --- at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlReverseEngineerImpl.Execute PopulatorsInPass(SqlReverseEngineerConnectionContext context, ReverseEngineerOpt ion option, SqlReverseEngineerRequest request, Int32 totalCount, Tuple`2[] popul atorsArray) at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlReverseEngineerImpl.Populat eBatch(SqlReverseEngineerConnectionContext context, SqlSchemaModel model, Revers eEngineerOption option, ErrorManager errorManager, SqlReverseEngineerRequest req uest, SqlImportScope importScope) at Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlReverseEngineer.PopulateEle ments(SqlReverseEngineerConnectionContext context, DataSchemaModel model, Revers eEngineerOption option, ErrorManager errorManager, SqlImportScope importScope, S qlReverseEngineerRequest[] requests) at Microsoft.Data.Tools.Schema.Sql.Deployment.Steps.DacBulkCopyStep.GetImport TableMetadata(ICollection`1 tables) at Microsoft.Data.Tools.Schema.Sql.Deployment.Steps.DacBulkCopyStep.Execute(I DbConnection conn, CancellationToken token) at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlPlanDeployment.Execute(Deplo ymentPlanHandle plan, String connectionString, String targetDatabaseName, Boolea n IsAzureTarget, String databaseScriptPath, String masterScriptPath, Cancellatio nToken cancelToken) at Microsoft.SqlServer.Dac.Deployment.PlanExecutor.Execute(DeploymentPlan pla n, SqlConnectionStringBuilder connectionString, Boolean isAzureTarget, LoggingCo ntext loggingContext, CancellationToken cancelToken) --- End of inner exception stack trace --- at Microsoft.SqlServer.Dac.Deployment.PlanExecutor.Execute(DeploymentPlan pla n, SqlConnectionStringBuilder connectionString, Boolean isAzureTarget, LoggingCo ntext loggingContext, CancellationToken cancelToken) at Microsoft.SqlServer.Dac.DeployOperation.UpdateDatabaseData(LoggingContext loggingContext, CancellationToken token) at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass10.<>c__DisplayCl ass12.<CreatePlanExecutionOperation>b__f() at Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action) at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass10.<CreatePlanExe cutionOperation>b__e(Object operation, CancellationToken token) at Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(O perationContext context) at Microsoft.SqlServer.Dac.ReportMessageOperation.Microsoft.SqlServer.Dac.IOp eration.Run(OperationContext context) at Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation .Run(OperationContext context) at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, D acLoggingContext loggingContext, CancellationToken cancellationToken) at Microsoft.SqlServer.Dac.DacServices.InternalDeploy(IPackageSource packageS ource, Boolean isDacpac, String targetDatabaseName, DacDeployOptions options, Ca ncellationToken cancellationToken, DacLoggingContext loggingContext) at Microsoft.SqlServer.Dac.DacServices.ImportBacpac(BacPackage package, Strin g targetDatabaseName, DacImportOptions importOptions, Nullable`1 cancellationTok en) at Microsoft.Data.Tools.Schema.CommandLineTool.Program.DoImportBacpacOperatio n(CommandLineArguments parsedArgs) at Microsoft.Data.Tools.Schema.CommandLineTool.Program.PerformAction(CommandL ineArguments parsedArgs) at Microsoft.Data.Tools.Schema.CommandLineTool.Program.Run(String[] args) at Microsoft.Data.Tools.Schema.CommandLineTool.Program.Main(String[] args)
事實證明，解決方案就像指定更大的超時一樣簡單。不過，哪個超時確實很重要（SqlPackage.exe 有幾個超時開關，而且它既不是連接字元串中可指定的連接超時），它是
CommandTimeout. 即使使用包含大量表的 bacpac，以下修改後的命令也不會失敗（注意最後一個新添加的開關）："C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe" /Action:Import /SourceFile:"path\to\MyDB.bacpac" /TargetConnectionString:"Data Source=localhost;Initial Catalog=MyDB;Integrated Security=True;" /p:CommandTimeout="0"“0”表示沒有超時，所以這僅適用於如果操作卡住或執行意外長時間沒有危險，例如像我一樣在本地導入bacpac進行開發。