Exchanging Schemas with SQL Compare Snapshots

Recently I was working with a customer and they asked if they could somehow package up their schema without the data and send this to a colleague. Absolutely, and that’s one reason we have SQL Compare snapshots.

Here’s a quick example of this working, where I’ll move a database from my desktop to a laptop. Separate SQL Compare machines and licenses.

First, I start with a database on my local instance. This is a copy of the ContosoRetailDW that I got from Microsoft. It’s set up, and I want to get the schema to another machine without doing a backup.

2020-07-28 11_29_28-SQLQuery2.sql - ARISTOTLE_SQL2017.master (ARISTOTLE_Steve (59)) - Microsoft SQL

I could script this into one big file with SSMS/SMO, but I’d lose some of the flexibility I have with SQL Compare, where I could filter things, set options, etc.

Let’s start SQL Compare. When this opens, I have the basic dialog asking for source and target. I’ll set the source to my database. For the target, I have many options, the third of which is Snapshot (in SQL Compare 14).

2020-07-28 11_56_04-New project_

When I click this, I see a simple dialog. This is because SQL Compare is looking for me to pick a snapshot to compare my database to. I don’t want to make a comparison. What I want to do is create a snapshot, so I’ll click Create.

2020-07-28 11_56_20-New project_

This gives me a different dialog. Here I connect to a server and database and Compare will create a snapshot. I pick a few options for where to store this and whether to decrypt things and use case sensitivity.

2020-07-28 11_56_42-Create new snapshot

This runs and Compare lets me know what’s happening at each stage.

2020-07-28 11_56_49-Creating snapshot - Running

When this is done, I’m back to the project dialog. Here I can select my snapshot from those in my local folder. Since I’m not going to do anything here, I’ll cancel out of this and close SQL Compare. Then I’ll transfer the file to a laptop.

2020-07-28 12_04_17-New project_

Restoring the Snapshot

I can email myself this file and save it on another machine. I’ll do that in the same SQL Compare\Snapshots folder. Now I can open SQL Compare on this machine. When I do, I’ll select the snapshot as my source.

2020-08-20 08_22_41-New project_

The target is a database, but in this case, it’s a new one. I’ll create one from the SQL Compare dialog.

2020-08-20 08_22_55-Create new database

Once this is created, I can run the comparison.

2020-08-20 10_01_20-New project_

When this completes, I have the system objects as identical, but I can see there are a number of use objects that don’t exist.

2020-08-20 10_01_55-SQL Compare - New project_

I can click Deploy and walk through that process. There are a number of confirmations to approve, but I am not showing those. At the end, a new comparison shows things are matching.

2020-08-20 10_03_07-SQL Compare - New project_

Note this doesn’t move data, just schema objects. This is primarily for development purposes, though if I needed data, I’d just use SQL Server backup and restore. Or SQL Data Compare if I needed limited data from tables.

SQL Compare is one of the most popular products from Redgate. If you’ve never used it, give it a try today with an eval. If you have the SQL Toolbelt, make sure you are using Compare to check and move schema changes around in an ad hoc manner.

About way0utwest

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.