Overview

🛡️ As Microsoft proceeds with the retirement of Access web apps, existing applications are automatically transitioned into specialized Access app packages. These packages are archived within a dedicated document library, typically sharing the same name as the original application. Under normal circumstances, administrators retrieve data by extracting a *.DACPAC file and deploying it directly to a SQL Server instance as a data-tier application.

⚠️ However, IT administrators may encounter significant roadblocks during this migration. If an Access web app contains underlying validation errors or structural inconsistencies, the standard deployment process often fails. Common symptoms include the following error messages in SQL Server Management Studio (SSMS):

• Error SQL72014: Could not deploy package.
• Error SQL72045: Script execution error.

This guide provides a comprehensive manual workaround to bypass these deployment failures and successfully recover table data using the SQL Server Data-Tier Application Framework and the Bulk Copy Program (BCP) utility.

⚙️ Key Technical Details

When the automated deployment fails, you must manually deconstruct the DACPAC file and rebuild the database structure and data layers. Follow these detailed technical steps:

1. Decompressing the DACPAC

• First, ensure you have the Microsoft SQL Server Data-Tier Application Framework (17.8 GA DacFx) installed on your workstation.
• Locate the appdb.dacpac file within your Access app package.
• Double-click the file, designate a local destination folder, and select Unpack to reveal the internal source files, including model.sql and data folders.

2. Initializing the Target SQL Database

• Open SQL Server Management Studio (SSMS).
• Manually create a new, empty database on your SQL Server instance to serve as the recovery target.

3. Reconstructing Schemas and Tables

🛠️ Because the DACPAC cannot be deployed as a single unit, you must manually execute the table definitions found in the model.sql file:

• Open a new query window in SSMS and ensure the context is set to your newly created database.
• Execute a CREATE SCHEMA command to establish the necessary namespace for your tables.
• Open the model.sql file (found in your unpacked folder) using a text editor or SSMS.
• Identify the CREATE TABLE blocks for the specific tables you need to recover.
• Copy and paste these blocks into your query window and execute the script.

4. Data Ingestion via BCP Utility

📊 Once the table structures exist, use the Bulk Copy Program (BCP) to move data from the unpacked DACPAC folders into the SQL tables. The bcp utility is included with Microsoft Command Line Utilities 14.0 for SQL Server.

Open a Command Prompt and use the following syntax based on your authentication method:

Option A: SQL Server Authentication

bcp <NewDatabaseName>.<Schema>.<Table> in "<BCPFilePath>" -N -S <ServerName> -U <UserName> -P <Password>

Example:

bcp SampleDatabase.Access.Person in "c:\temp\appdb\Data\Access.Person\TableData-000-00000.BCP" -N -S Server1 -U User1 -P PWD

Option B: Integrated Security / Trusted Connection

bcp <NewDatabaseName>.<Schema>.<Table> in "<BCPFilePath>" -N -T

Example:

bcp SampleDatabase.Access.Person in "c:\temp\appdb\Data\Access.Person\TableData-000-00000.BCP" -N -S Server1 -T

⚠️ Note: This process must be repeated individually for every table that requires data restoration.

📅 Impact

This manual recovery process has a direct impact on data retention strategies for organizations moving away from Access Services in SharePoint:

• Data Integrity: Allows for the recovery of critical business data even when the original app package is technically “corrupt” or fails validation checks.
• Administrative Overhead: This method is more labor-intensive than a standard DACPAC deployment, as it requires manual schema creation and individual table imports via CLI.
• Continuity: Ensures that historical records stored in retired Access web apps remain accessible for auditing and migration into modern platforms like Power Apps or standard SQL Server applications.