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.
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).
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.
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.
This runs and Compare lets me know what’s happening at each stage.
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.
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.
The target is a database, but in this case, it’s a new one. I’ll create one from the SQL Compare dialog.
Once this is created, I can run the comparison.
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.
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.
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.