BACPAC or Backup: When an Export Is the Wrong Tool

A BACPAC is a logical export of schema and data, not a backup you can recover from. It is a fine way to move a database, and the wrong tool when the real goal is recovery. Pick the tool after you know which job you are doing.

Two jobs that look alike

Picture a Friday afternoon request: “Please copy the sales database to the new server.” You find last month’s BACPAC export in the shared folder, and it contains rows. Job done? Not yet. A copy for a test team and a copy you could recover from are different jobs.

A native backup is made for recovery. A BACPAC rebuilds the schema and loads the table data into a new database, which is great for moving a database between servers. It does not carry your log chain, and it knows nothing about your server beyond the database itself. So ask first: do I need to recover, or do I need to move?

I can’t create a BACPAC from T-SQL, because that needs a separate tool. But I can show you everything around the decision, using a small demo database. It creates one database, one backup file and one restored copy, and the last block removes all of it.

Build a small database with some baggage

The table is simple. The baggage is the interesting part: a view that reads from the master database, and a user with its own permission.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemoCopy;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
CREATE DATABASE SqlAuthorityDemo;
GO
USE SqlAuthorityDemo;
GO
CREATE TABLE dbo.Orders (
    OrderId  int           NOT NULL CONSTRAINT PK_Orders PRIMARY KEY,
    Customer varchar(30)   NOT NULL,
    Amount   decimal(10,2) NOT NULL);
INSERT dbo.Orders (OrderId, Customer, Amount)
VALUES (1, 'North', 120.50), (2, 'South', 75.00), (3, 'East', 310.25);
GO
CREATE VIEW dbo.MasterLookup AS SELECT TOP (1) name FROM master.dbo.spt_values;
GO
CREATE USER ReportUser WITHOUT LOGIN;
GRANT SELECT ON dbo.Orders TO ReportUser;

Take an inventory before you export

Before any logical export, list what lives in the database. These three queries are a starting point, not a full exportability check. Watch the last one. Anything that reaches into another database will not come along for the ride.

SELECT name, type_desc
FROM sys.objects
WHERE is_ms_shipped = 0
ORDER BY type_desc, name;

SELECT name, type_desc
FROM sys.database_principals
WHERE principal_id > 4 AND type <> 'R'
ORDER BY name;

SELECT OBJECT_NAME(referencing_id) AS ObjectName, referenced_database_name,
       referenced_schema_name, referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referenced_database_name IS NOT NULL
ORDER BY ObjectName;

The demo shows a table with its key, a view, one user named ReportUser, and one cross-database reference: MasterLookup reads master.dbo.spt_values. In a real migration, that reference is a to-do item.

Why recovery needs a backup chain

A backup plan has a spine: the full backup and the differentials built on top of it. Each regular full backup resets the differential base. The first block below uses the NUL device, which throws the data away, to show it happening. Never do this to a database you care about, because the chain would break.

USE master;
CREATE TABLE #BaseLsn (Step int IDENTITY, WhatHappened varchar(40), DifferentialBaseLsn numeric(25,0) NULL);

INSERT #BaseLsn (WhatHappened, DifferentialBaseLsn)
SELECT 'New database', differential_base_lsn
FROM sys.master_files WHERE database_id = DB_ID(N'SqlAuthorityDemo') AND file_id = 1;

BACKUP DATABASE SqlAuthorityDemo TO DISK = N'NUL';

INSERT #BaseLsn (WhatHappened, DifferentialBaseLsn)
SELECT 'After a regular full backup', differential_base_lsn
FROM sys.master_files WHERE database_id = DB_ID(N'SqlAuthorityDemo') AND file_id = 1;

The base was NULL for the new database and holds a number after the first full backup. Now take a copy for the move. COPY_ONLY says “this is a side copy, leave my chain alone”. CHECKSUM checks the pages as they are written. The block makes a small folder under the default backup folder for the file.

DECLARE @folder nvarchar(400) =
    CONVERT(nvarchar(300), SERVERPROPERTY('InstanceDefaultBackupPath')) + N'\SqlAuthorityDemo';
EXEC master.sys.xp_create_subdir @folder;

DECLARE @file nvarchar(400) = @folder + N'\SqlAuthorityDemo.bak';
BACKUP DATABASE SqlAuthorityDemo TO DISK = @file WITH COPY_ONLY, CHECKSUM, INIT;
RESTORE VERIFYONLY FROM DISK = @file WITH CHECKSUM;

INSERT #BaseLsn (WhatHappened, DifferentialBaseLsn)
SELECT 'After a COPY_ONLY backup', differential_base_lsn
FROM sys.master_files WHERE database_id = DB_ID(N'SqlAuthorityDemo') AND file_id = 1;

SQL Server reports that the backup set is valid. Run one more regular full backup, then read the numbers side by side.

BACKUP DATABASE SqlAuthorityDemo TO DISK = N'NUL';

INSERT #BaseLsn (WhatHappened, DifferentialBaseLsn)
SELECT 'After another regular full backup', differential_base_lsn
FROM sys.master_files WHERE database_id = DB_ID(N'SqlAuthorityDemo') AND file_id = 1;

SELECT Step, WhatHappened, DifferentialBaseLsn FROM #BaseLsn ORDER BY Step;

SELECT TOP (1) database_name, is_copy_only, has_backup_checksums
FROM msdb.dbo.backupset
WHERE database_name = N'SqlAuthorityDemo' AND type = 'D' AND is_copy_only = 1
ORDER BY backup_set_id DESC;

The COPY_ONLY step shows the same number as the step before it. The next regular backup moves the base. So the side copy left the chain alone, and a casual full backup would not have. The msdb row confirms the copy was recorded as copy-only, with checksums.

Restore it and see what traveled

Now the payoff. Restore the file as a new database and look inside. This is what a native backup gives you: the whole database, not a rebuilt one.

DECLARE @file nvarchar(400) =
    CONVERT(nvarchar(300), SERVERPROPERTY('InstanceDefaultBackupPath')) + N'\SqlAuthorityDemo\SqlAuthorityDemo.bak';
DECLARE @data nvarchar(400) = CONVERT(nvarchar(300), SERVERPROPERTY('InstanceDefaultDataPath')) + N'\';
DECLARE @log  nvarchar(400) = CONVERT(nvarchar(300), SERVERPROPERTY('InstanceDefaultLogPath')) + N'\';
DECLARE @sql  nvarchar(max) = N'RESTORE DATABASE SqlAuthorityDemoCopy FROM DISK = N''' + @file + N'''
    WITH MOVE N''SqlAuthorityDemo'' TO N''' + @data + N'SqlAuthorityDemoCopy.mdf'',
         MOVE N''SqlAuthorityDemo_log'' TO N''' + @log + N'SqlAuthorityDemoCopy_log.ldf'', RECOVERY;';
EXEC (@sql);
GO
USE SqlAuthorityDemoCopy;
SELECT COUNT(*) AS OrderRows FROM dbo.Orders;
SELECT name FROM sys.database_principals WHERE name = N'ReportUser';
SELECT OBJECT_NAME(major_id) AS ObjectName, permission_name, state_desc
FROM sys.database_permissions
WHERE grantee_principal_id = USER_ID(N'ReportUser') AND class = 1;

Three rows, the user, and its SELECT permission on Orders all came back.

Clean up and decide

The last block drops both databases, deletes the backup file and removes the backup history. The delete helper only looks inside our own small folder, so nothing else is touched. The empty folder stays behind, and you can remove it by hand.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemoCopy;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
DECLARE @folder nvarchar(400) =
    CONVERT(nvarchar(300), SERVERPROPERTY('InstanceDefaultBackupPath')) + N'\SqlAuthorityDemo';
EXEC master.sys.xp_delete_file 0, @folder, N'bak', N'2099-01-01';
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'SqlAuthorityDemo';
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'SqlAuthorityDemoCopy';
DROP TABLE IF EXISTS #BaseLsn;

My rule of thumb: if the goal is recovery, take a native backup and test a restore. If the goal is a move, check the inventory first, then export from a copy that nobody is writing to. Whatever you pick, keep the native backup until the other side is accepted.

Write down whether you are recovering or moving before you create the file.

A BACPAC is not a backup, it is a way to move a database.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.