Clearing Out a Database With SQL Compare

I ran into a question recently about how to clear out a database of all objects. I assume someone was testing some type of deployment scenario, and didn’t want to drop the database, but rather just remove objects and redeploy.

I know SQL Compare could do this, so I mocked up a quick project.

I’ll start with a database. I grabbed one I had lying around, and in this case, I’ll use the Sandbox database, which has a number of tables in it.

2015-07-23 11_19_06-XML_Basic_Queries.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (69)) - Microsoft SQL

Perhaps I’m testing upgrades, installations, or something and don’t want to drop this. Perhaps it’s in Azure, and recreating it is a pain. Maybe I want users/roles/security still to be there. Whatever the reason, this is a use case that SQL Compare can handle.

First, I need a new database.

2015-07-23 11_36_06-SQLQuery2.sql - ARISTOTLE.tSQLt_Exercises (ARISTOTLE_Steve (65))_ - Microsoft SQ

With this, I can now run SQL Compare and look at my two databases.

2015-07-23 11_36_49-New Project_

I am comparing the blank database to the existing one. The empty database is essentially development. I want to get the existing one to look like this one. I might need to edit the filter rules on the left to exclude roles, users, etc., but running the comparison shows me the differences that I can examine in more detail.

2015-07-23 11_37_47-SQL Compare - New Project_

In my case, I did exclude security, and once I was happy with the list, I clicked the Deployment Wizard button. This immediately generated a script, but gave me warnings.

2015-07-23 11_38_05-Deployment

I am well aware that dropping these objects could result in lost data. That’s because I’m removing tables. However that’s what I want.

If I open the script in an editor, I can see all the drops, properly ordered, in the script.

2015-07-23 11_40_46-SQL Compare844a2b43-aebf-4f51-bf84-ab3f73065053.sql - (local).sandbox (ARISTOTLE

This is a quick way to remove the objects from a database to test your installation or rebuild of a database. I’m not sure this makes sense for most deployments, as you’ll usually want to just remove the objects that were added in the failed deployment.

Generating a rollback script is a task for another day. For now, if you need a clean database, here’s one way to remove everything from an existing database.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.