Every now and again you have a database where you don’t necessarily need the data. It can easily be re-loaded or re-created. Or maybe you need a copy of the database, but since you don’t need the data, a backup of the 750gb database seems a bit excessive. (I see this happen every now and again with large test databases that need to be pushed up to production.) Unfortunately since there is no WITH SCHEMAONLY option on the BACKUP DATABASE command what do we do? Well, fortunately, we have a new command line tool called sqlpackage. (I say new and I assume it was created in the last few years and I think it was primarily created with Azure in mind, but honestly, it’s always possible I missed it and it’s been around as long as bcp. Although I doubt it.) sqlpackage lets you create DACPACs and BACPACs. A little while back created a BACPAC and then used it to copy the on-premises DB into Azure. This time I’m going to use a DACPAC as a schema only backup.

To start, sqlpackage is a command line tool. This means you need to run it from a command prompt. Next, if you try to run sqlpackage and it says it can’t be found, (the directory isn’t in the PATH variable) then assuming it’s been installed (SSMS or SSDT installs) it can be found in several different places depending on how it got installed.

There are a lot of options for sqlpackage and I highly recommend at least reviewing what’s available. That said, I’m going to use the absolute minimum for my task.

Backup

sqlpackage.exe /Action:Extract /SourceDatabaseName:"Test" /SourceServerName:"KENNETH-LAPTOP\SQL2016CS" /TargetFile:"C:\temp\test.dacpac"

Connecting to database ‘Test’ on server ‘KENNETH-LAPTOP\SQL2016CS’.
Extracting schema
Extracting schema from database
Resolving references in schema model
Successfully extracted database and saved it to file ‘C:\temp\test.dacpac’.

Restore

sqlpackage.exe /Action:Publish /SourceFile:"C:\temp\test.dacpac" /TargetServerName:"KENNETH-LAPTOP\SQL2016CS" /TargetDatabaseName:"NotTest"

Publishing to database ‘NotTest’ on server ‘KENNETH-LAPTOP\SQL2016CS’.
Initializing deployment (Start)
*** The object [CreateTemporalTable] already exists in database with a different definition and will not be altered.
*** The object [Doc] already exists in database with a different definition and will not be altered.
Initializing deployment (Complete)
Analyzing deployment plan (Start)
Analyzing deployment plan (Complete)
Updating database (Start)
Creating NotTest…
Creating [CreateTemporalTable]…
Creating [Doc]…
Creating [Kenneth_Test]…
Creating [SQLTest]…
Creating …
Creating [PartWithNullsPF]…
Creating [PartWithNullsPFLeft]…
Creating [PartWithNullsPFRight]…
Creating [pfWhatsInAPart]…
Creating [PartWithNullsPS]…
Partition scheme ‘PartWithNullsPS’ has been created successfully. ‘PRIMARY’ is marked as the next used filegroup in partition scheme ‘PartWithNullsPS’.
Creating [PartWithNullsPSLeft]…
………….
Creating [dbo].[ck_PartWithNullsTab_Part]…
Creating [dbo].[trDestinationTable]…
Creating [dbo].[ViewToRefresh]…
Creating [dbo].[sp_LogMe]…
Creating [tr_WhatsChanged]…
Update complete.
Updating database (Complete)
Successfully published database.

You’ll note the warnings about CreateTemporalTable and Doc. That’s because they are Logins that already exist so there is no reason to create them at the instance level.

Warning: When publishing, if there are any errors (encrypted SPs, for example, will error out because they can’t be scripted out properly) then the whole thing will fail and the new database will not be created.

Note: If you apply a DACPAC to an existing database new objects will be created but existing objects will be ignored (not updated or removed).