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.

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.

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

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.

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.

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.

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.

Filed under: Blog Tagged: Redgate, SQL Compare, sql server, syndicated