Voice of the DBA

Tag: syndicated

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.

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.

August 26, 2020
Daily Coping 26 Aug 2020

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here.

Today’s tip is to take time to forgive someone who hurt you in the past.

This is a personal item. I think many of us feel wronged or hurt by others, sometimes by accident, sometimes on purpose. In either case, the event can be very upsetting and impactful to us.

I don’t want to publicly note who I’m forgiving, but I am making it a point to do so, and let someone know that I forgive their past transgression.

This is certainly something I need to work on. There are people that I feel have treated me poorly, and I haven’t forgiven them. I should let some things go, but I am struggling, especially when some people don’t seem to acknowledge their actions.

August 26, 2020
Daily Coping 25 Aug 2020

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here.

Today’s tip is to tell someone about a book or article you found helpful.

I go through a random collection of books and articles all the time. I read a lot, but mostly fiction, and it’s rare I pass along recommendations to others. However, I’ve seen some good articles that I sometimes pass along to friends or my kids.

Here’s one I sent on. It’s from David Perell and is titled, The Magic Moment. This talks about the creative process, and how you should go with it when you feel creativity is flowing. Don’t want and plan things, or reschedule them. When you feel ready to create something, spend the time right then to do so.

I sent this to a few people I know that are creative, but I think it applies to software developers as well. I know that when I feel like writing, I write. If I get in the zone writing some code, I’ll let me day fall apart and focus on that project. Usually that highly focused time is valuable.

Don’t make people go to meetings if they are productive elsewhere.

August 25, 2020
Don’t Forget Unique with FILESTREAM–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

While testing FILSTREAM with SQL Clone the other day, I kept getting an error while trying to create a table. I’d click Execute and see this:

My mind kept focusing on the ROWGUIDCOL part, and not thinking unique. It’s been a few years since I worked with FILESTREAM as it’s not an Azure feature and things have been going that way for me.

In any case, after running this a few times, and then checking an old demo, I realized that I had forgotten UNIQUE as an attribute for the column. Once I added that, it worked.

The docs for CREATE TABLE shows this that unique is listed as a constraint property, but under the FILESTREAM section, is does say this about the ROWGUID column: “This column must not allow null values and must have either a UNIQUE or PRIMARY KEY single-column constraint.”

Don’t forget this, but if you do, read the error message,

SQLNewBlogger

This post took me about as long to write as it did to realize I was being silly and forgetting to read. Overall, this was about 10 minutes to compile, take the screenshots, and get the references.

When you write, look for places you’ve learned something, and then use those as ideas for blogs.

August 24, 2020