SQL Server / BACPAC / Could not extract package from specified database – The wait operation timed out

Posted by in SQL Server, SQL Tips and Tricks on May 13th, 2020 | 0 comments

Export Data-tier Application. This is usually really simple and straight forward process.

But what if you have database with over 100K tables, 200K indexes?

You would probably end up with following timeout error during export process using SSMS

To resolve this possible “Execution Timeout Expired” issue you may use SqlPackage.exe command-line utility to export your database including database schema and user data from SQL Server Database to a BACPAC package (.bacpac file) 

More information about this command-line utility tool and how to install and from where to download you can find on the following link:

https://docs.microsoft.com/en-us/sql/tools/sqlpackage?view=sql-server-ver15

 

Command-Line Syntax

Usage example

Export Parameters and Properties I used for previous example:

Parameters specific to the Export action

Parameter

Short Form

Value

Description

/Action:

/a

Export

Specifies the action to be performed.

/OverwriteFiles:

/of

{True|False}

Specifies if sqlpackage.exe should overwrite existing files. Specifying false causes sqlpackage.exe to abort action if an existing file is encountered. Default value is True.

/SourceDatabaseName:

/sdn

{string}

Defines the name of the source database.

/SourceServerName:

/ssn

{string}

Defines the name of the server hosting the source database.

/TargetFile:

/tf

{string}

Specifies a target file (that is, a .dacpac file) to be used as the target of action instead of a database. If this parameter is used, no other target parameter shall be valid. This parameter shall be invalid for actions that only support database targets.

Properties specific to the Export action

Property

Value

Description

/p:

CommandTimeout=(INT32 ’60’)

Specifies the command timeout in seconds when executing queries against SQL Server.

/p:

TableData=(STRING)

Indicates the table from which data will be extracted. Specify the table name with or without the brackets surrounding the name parts in the following format: schema_name.table_identifier. This option may be specified multiple times.

/p:CommandTimeout – Specifies the running command timeout in seconds when executing queries against SQL Server. Use 0 to wait indefinitely.

Example with PowerShell

The SQLPackage.exe is part of the DacFramework.

Once the DacFramework is installed you can locate the SQLPackage.exe in the following path:

C:\Program Files (x86)\Microsoft SQL Server\<Version>\DAC\bin

Every released version of SQLPackage.exe supports different database compatibility level ranges (80-150)

If you are using wrong version you may end up with following error:

*** Error exporting database:The database compatibility level ’14’ is not within the supported range of 80 to 130.

SQLPackage 18 and above has support for database compatibility level 150 (SQL Server 2019).

You can find more information on the following MS article:

https://docs.microsoft.com/en-us/sql/tools/release-notes-sqlpackage?view=sql-server-ver15

Similar Posts:

Leave a Reply

Your email address will not be published. Required fields are marked *