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.

Building Rollback Scripts with SQL Compare

There’s a neat switch in SQL Compare that lets you build rollback scripts. It looks like this:


I’ve used this before to help with deployments, as many of you have. The idea is that when you finish with your code in your dev environment, you run a comparison with production, doing something like this:


I’ve set the source as my development place, and the destination as production. I click "Compare Now", get a script that will deploy the changes from development to production, and save it. I can use that script as part of my deployment process.

However as soon as I’ve saved that script, I return to this screen, and I click the "Switch" button at the bottom.


If you look closely, you’ll see that my source (on the left) and destination (on the right), have changed places. This means I’ll now generate a script that takes my production environment and generates the script to get back production from development.

This is a rollback script.

It doesn’t work in all situations, and you really have to think about what you’re changing, but if you’re just doing views/procedures/functions, this is a great way to get that quick rollback script that you store alongside the deployment script in case things go badly.

Excluding Schemas in SQL Compare

I saw this question posted the other day and thought it was a great idea.


I haven’t been a big schema user in the past, but I see the value of excluding some schemas. CDC for sure, but I know some people have an ETL schema, which is much nicer than having Staging_Customers. I think the exact same structure in ETL.Customers moving to dbo.Customers is a good idea.

sql-compare-logoI sent a note to the PM for SQL Compare, since I thought this might be one to bump up on the list of things that might make it into Compare 11. We’re on Compare 10 now, which is amazing. I remember Andy Warren talking about how cool this product was back in 2002 when it was just SQL Compare.

The PM told me we already do this, so I started digging in. Sure enough, we can do it.

I can do a quick compare of two databases on a test instance. I’ll pick a new project and select two similar, but slightly different dbs.


Once the comparison runs, you see there are three objects that are in one database, but not the other.


This shows me I have a schema (ETL) and a table (ETL.Contact) in that schema, as well as a separate table in another schema (Person.Contact2). I want to exclude the ETL stuff, so what can I do?

There’s a Filter Rules button, that shows the filters on the left side by default. It contains all the types of objects, which is what I normally use it for, excluding some types.


However I can click the “Edit Filter Rules” button, and I get a dialog, where I can enter various conditions.


Here I’ve excluded (upper left) objects if the Schema begins with ETL. I can click OK and then re-run the comparison. I’ll then get:


Better, but I still have the ETL schema, which I may not want to move over.

As a visual cue (if you look for it), I can see I have a rule set in the Filter pane on the left. It’s subtle, but it’s in a maroon-like color.


If I go back, I can actually add a second condition here and change my operators to prevent mismatches with similar names, by using an “Equals” operator.


Now I get what I wanted. Note that this took effect immediately and I didn’t have to run the comparison again. SQL Compare finds all the changes and then filters them.


If this is something I do regularly, like move from Development to QA, I can save the project:


The next time I need to run this, instead of using the New Project dialog, I can just open this one and run the comparison.