Problem

I was importing a BACPAC generated on another server into my local development environment using SQL Server Management Studio and ran into the following errors:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Could not import package.
Warning SQL0: A project which specifies Microsoft Azure SQL Database v12 as the target platform may experience compatibility issues with SQL Server 2014.
Warning SQL72012: The object [databaseXYZ_Data] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source check box.
Warning SQL72012: The object [databaseXYZ_Log] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source check box.
Error SQL72014: .Net SqlClient Data Provider: Msg 12824, Level 16, State 1, Line 5 The sp_configure value 'contained database authentication' must be set to 1 in order to alter a contained database. You may need to use RECONFIGURE to set the value_in_use.
Error SQL72045: Script execution error. The executed script:
IF EXISTS (SELECT 1
FROM   [master].[dbo].[sysdatabases]
WHERE  [name] = N'$(DatabaseName)')
BEGIN
ALTER DATABASE [$(DatabaseName)]
SET CONTAINMENT = PARTIAL
WITH ROLLBACK IMMEDIATE;
END

Error SQL72014: .Net SqlClient Data Provider: Msg 5069, Level 16, State 1, Line 5 ALTER DATABASE statement failed.
Error SQL72045: Script execution error. The executed script:
IF EXISTS (SELECT 1
FROM   [master].[dbo].[sysdatabases]
WHERE  [name] = N'$(DatabaseName)')
BEGIN
ALTER DATABASE [$(DatabaseName)]
SET CONTAINMENT = PARTIAL
WITH ROLLBACK IMMEDIATE;
END

(Microsoft.SqlServer.Dac)

Fix

Run the following T-SQL on the target SQL Server instance before importing the BACPAC:

EXEC sp_configure 'contained database authentication', 1;
GO
RECONFIGURE;
GO

Once that setting is enabled, rerun the import.

Why This Happens

The import process is trying to set the target database to partial containment:

ALTER DATABASE [YourDatabaseName]
SET CONTAINMENT = PARTIAL;

If contained database authentication is disabled on the SQL Server instance, that step fails and the import stops with SQL72014 and SQL72045.

This often happens when the BACPAC comes from Azure SQL Database and is being imported into a local SQL Server environment, where contained database authentication may be turned off by default.

Explanation

At first I suspected the issue might be caused by a corrupt BACPAC or Transparent Data Encryption (TDE), but the real cause was much simpler: the import required support for a contained database.

A partially contained database reduces dependencies on the master database and allows authentication and configuration to live more at the database level rather than relying entirely on server-level logins.

Because this has security implications, SQL Server does not always enable it by default on local or on-premises instances.

Notes

• SQL72014 and SQL72045 are the main errors causing the import failure.
• The SQL72012 warnings about data and log objects are not the root cause here.
• For most local development environments, enabling contained database authentication is enough to complete the import successfully.

Further Reading

• Contained database authentication server configuration option
• SqlPackage import documentation
• Import a BACPAC file to create a new database

Large Optimizely or EPiServer projects tend to collect old page types over time. New templates are introduced, editors move on to newer content types, and some older page types quietly stop being used.

If you are cleaning up a long-running CMS solution, one of the first things worth checking is which page types are still in use and which ones have a page count of zero.

The SQL query below gives you a quick usage report for page types. It does not cover block types, but it is a useful first step when auditing legacy CMS projects.

Quick SQL Audit

Run the following query in SQL Server to list page type names, filenames, and the number of pages using each type:

SELECT
    pt.Name,
    pt.Filename,
    COUNT(p.pkID) AS PageCount
FROM tblPageType AS pt
LEFT JOIN tblPage AS p
    ON p.fkPageTypeID = pt.pkID
GROUP BY
    pt.Name,
    pt.Filename
ORDER BY
    PageCount DESC,
    pt.Name;

Why This Is Useful

This report helps you quickly spot:

• page types that are heavily used
• page types that are rarely used
• page types with a PageCount of 0, which may be candidates for cleanup

Before You Run It

Empty the recycle bin first. Deleted content can still affect the result and make unused page types look active when they are not.

Important Note

A page count of zero is a strong signal, but it should not automatically mean “safe to delete”. Before removing a page type from code, double-check whether it is still referenced by old templates, import jobs, migrations, or content type availability rules.

For older self-hosted EPiServer or Optimizely CMS solutions, this is a quick and practical way to start a cleanup exercise without building a custom report first.