John Xiong

Data Engineer & Outdoor Fanatic

Data

SQL Server – Drop All Objects in Database via SqlPackage

John

The Quick Answer: Deploy an empty database project. If you have temporal tables, you’ll need to turn system versioning off for each table first, and then the deploy will drop the tables; this can be scripted dynamically. If you need to clear multiple databases on multiple servers, sqlpackage.exe has a /TargetConnectionString parameter where you can choose where it needs to go.

The Detailed Scenario Answer: Being a part of a data team, one of our responsibilities is handling database initializations. To create a fresh slate of databases for others, usually we must tear down Always On, drop databases, deploy databases, and then setup Always On again. While simple, it can be time consuming when handling 40+ databases with Always On and fixing security problems. To avoid touching Always On, one of our genius DBA’s had the idea to deploy an empty database project to drop all the objects in a database.

The Database Projects

Wanted to initially apologize because even though I’m initially mentioned Always On, this example doesn’t include Always On; which is one of the main parts to avoid anyway, right?

The Actual Database

The image above shows a simple database with procedures, tables, and a database role. A couple of items to remember are that dbo.tblSalesforceMessages has a foreign key to dbo.tblBatch and that there is a security role. To save reading, we will assume this database is already deployed.

The Shell Database

The image above shows an empty database with a publish file that will be used to clear objects from the actual database. What is important here is that we have settings in the publish file so that we drop what we actually want. We’ll deploy this to the server via SqlPackage.exe later.

Shell Database Publish File

After many trial-and-errors, these were the final settings we needed to set to have our databases dropped how we wanted. A few things that are important here are the empty values for /TargetDatabaseName and /TargetConnectionString and also the /DoNotDrop* values. You can fiddle with the settings as needed, however the /TargetConnectionString value will be passed in via command line arguments later.

Deploying the Empty Database Project

After deploying the actual database project, we should have something on the server like this:

The goal is to use the empty database project to drop the tables and procedures but keep the security (Role_Developer). To do this, we can use SqlPackage.exe to deploy the empty database project and pass in the connection string. By doing this, we can use one generic project across all compatible servers and databases to drop objects.

SqlPackage.exe

I’ll be using the 140 SqlVersion of SqlPackage.exe tool (find SqlPackage.exe) and passing in the arguments below:

  • /Action:Publish
  • /SourceFile – dacpac of database project
  • /Profile – publish file
  • /TargetConnectionString – connection string of where to deploy
    • If you only need this for one database, you can populate this variable in the publish file and there’s no need to pass it in via command line.

The statement should look something like so:

“C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\140\SQLPackage.exe” /Action:Publish /SourceFile:”C:\Temp\DataLake\DataLakeDBShell\bin\Debug\DataLakeDBShell.dacpac” /Profile:”C:\Temp\DataLake\DataLakeDBShell\Publish\DataLakeDBShell.publish.xml” /TargetConnectionString:”Data Source=%SERVERNAME%;Initial Catalog=%DATABASENAME%;Integrated Security=True;Pooling=False”

A couple of things needed before running this.

  • Build your projects to create the dacpacs in the debug folder
  • Replace the paths to your dacpac and publish files
  • Replace %SERVERNAME% and %DATABASENAME%

The output should look something like:

Verifying the database dropped all objects except my security database role:

Deploying Shell to Database with Temporal Tables

If your database includes temporal tables, you’ll need to set the system versioning off for those tables prior to deploying the shell. SqlPackage.exe doesn’t realize that needs to happen before trying to drop the tables.

Here is an example of a script I used to do this (based off this blog):

DECLARE @alter_sql   varchar(MAX)
      , @report_only bit = 0;

DECLARE [crs] CURSOR FOR
    SELECT              'ALTER TABLE ' + QUOTENAME( SCHEMA_NAME( [t].[schema_id] )) + '.' + QUOTENAME( [t].[name] ) + ' SET ( SYSTEM_VERSIONING = OFF )'
      FROM              [sys].[tables] [t]
      LEFT OUTER JOIN   [sys].[tables] [h] ON
                        [t].[history_table_id] = [h].[object_id]
     WHERE              [t].[temporal_type] = 2;

OPEN [crs];

FETCH NEXT FROM [crs]
 INTO @alter_sql;

WHILE @@FETCH_STATUS <> -1
BEGIN
    RAISERROR( @alter_sql, 0, 1 ) WITH NOWAIT;

    IF @report_only = 0 EXEC ( @alter_sql );

    FETCH NEXT FROM [crs]
     INTO @alter_sql;
END;

CLOSE [crs];
DEALLOCATE [crs];

Other Thoughts

Leveraging Azure DevOps Pipeline

If you have the ability, I’d recommend creating a pipeline with this and you can easily rerun it whenever it is needed. You can utilize the tools and pass in the same arguments needed for SqlPackage.exe to get all of this done.

Why not dynamically script drop statements?

While you can dynamically script drop statements for all the objects, an obstacle here is when you have constraints such as foreign keys, checks, or triggers. When there are constraints, you’ll need to drop objects in a particular order. By leveraging tools like SqlPackage.exe, it will handle the dependencies for you.

Why not drop the database and redeploy?

You can definitely dynamically script the database drop and redeploy the current code. However, if you have Always On, this won’t work because you’ll need to remove the database from Always On before you can do that; then you’ll need to add the database back after the deploy. The goal in this example was to avoid touching Always On.

Back To Top