First published on MSDN on Dec 15, 2016
I had a support case recently where the export of a SQL Server database into a BACPAC file was failing. The first attempt was done through SQL Server Management Studio 2016, by right-clicking the database -> Tasks -> Export Data-Tier Application. Another attempt using the sqlpackage.exe command-line tool showed the identical results though.
The following operations were displayed in the export status messages:
Exporting database
Extracting schema
Extracting schema from database
And the final error message was:
Could not extract package from specified database. (Microsoft.SqlServer.Dac)
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)
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server)
The wait operation timed out.
Cause:
The reason was related to a complex database schema, which took longer to evaluate than the 60 seconds default timeout. This may happen for any database, either with on-premise SQL Server or Azure SQL Database. Unfortunately neither Management Studio nor sqlpackage.exe allow you to change the query timeout for this operation.
Solution:
UPDATE 2023-08-10:
sqlpackage.exe now supports three parameters "/p:CommandTimeout", "/p:DatabaseLockTimeout", and "/p:LongRunningCommandTimeout" for configuring timeout behaviour. See Troubleshooting issues and performance with SqlPackage - Timeout errors for more information.
ORIGINAL TEXT FROM 2016-12-15:
The DAC components for the BACPAC operations are based on Visual Studio. This allows you to change the query timeout for the BACPAC export through a Visual Studio registry key. The default is 60 seconds, which might not be sufficient for complex database schemas. You may increase it to 300 or even 600 seconds to solve your issue.
Use the following registry key to set the query timeout for the BACPAC export:
HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\10.0\SQLDB\Database\QueryTimeoutSeconds (REG_DWORD)
(Note: Decreasing the value might be a bad idea though. For testing, I had set it to 1 second - forgot about it - and then wondered why all my exports were failing...)
Microsoft
