PSRedgate: automating greatness

Being an automation DBA

Most of my job is figuring out how to do something, and then doing it over and over again. The first part is great, solving complicated problems or creating value for my company is a rewarding feeling and always makes me feel accomplished. The second part, where I have to keep doing it over and over, often makes me want to smash the snooze button on my alarm in the morning.

I love automation. It’s honestly my favorite part of being a DBA. I’m a programmer by training, and came to being a Database Administrator from web development. These days I really enjoy building quality tools. I also enjoy using those tools to do things quicker and more accurately than I ever could without them. That’s what led me to create PSRedgate.

Why not just do it by hand?

First of all, just is a four letter word. Second being a production database administrator means my responsibilities are very wide. I am responsible for backups and replication, tuning, monitoring, triage, on-call response, and deployment. My team of 4 support a development staff of around 30 and a business that processes millions of dollars worth of transactions a day. We also support web and mobile applications that serve hundreds of thousands of requests a day.  We have several environments for development, integration testing, staging, demoing, and production. All of these are backed up by SQL Servers with databases as large as nearly half a terabyte.

We’re not a big company, we’re not even big relative to the small town we’re located in, but there is a lot of pressure to do things right and do more with less. Doing things the RightWay ™ is often difficult for those involved and requires lots of code. You could double the size of my team and we’d still have plenty of work to do. The best thing I can do for my company is to allow people to do as much work as possible, with as little chance of making mistakes, with lots of documentation. Bonus points for not needing a whole lot of training to start being productive. Tools are the best way to do that.



My company has been using Redgate’s family of tools for at least as long as I’ve been here, and I’ve been doubling down over the past few years. SQL prompt alone is worth it’s weight in gold.

We use SQL Compare to do all of our database deployments from version control to production, we also use it to promote changes to different environments, we use SQL Source Control to version our database changes, we use SQL Backup to handle all of our retention. You could say we’re pretty invested.

My problem

Every time you want to deploy, backup, restore, or sync you have to fire up some application and press a whole bunch of buttons. What if you need to do it for 10 servers? What about 100? Not only is that not feasible, it would quickly become the largest and least enjoyable part of my job. No thanks! Fortunately my friends at Redgate are very mindful about creating developer first tools. What does that mean? For the most part, everything that you can do in their GUI (graphical user interface) applications, you can do by interacting with the CLI (command line) application. SQL Backup has a really nice interface that they built for it, but if you have used it much, you’ll notice that it’s just building a command to fire off to the server.



I have created a powershell module that I am calling PSRedgate. If you use powershell very often, you’re probably aware of how much of a pain in the neck it can be to use windows command line tools. A lot of them are really picky about how you send them parameters, they have to be in your path, and don’t seem to handle errors very well. All of that and a little bit more is what I’m offering in this new powershell module. It will make interacting with the tools from Redgate on your machine that much easier and will hopefully enable you to do things you can’t currently do easily.

I have completely automated my replication process using this module. I have internal administration module that uses this code to create backup jobs with Redgate command embedded for my test environments, fire off adhoc database backups and restores. You can even write logic to locate all of your backup files for point in time recovery.

Getting started with PSRedgate is pretty simple, all you have to do is open up powershell and install the module from the powershell gallery.

# From an elevated prompt for the whole machine.
Install-Module PSRedgate

# Or just for the current user
Install-Module PSRedgate -Scope CurrentUser

You can find out more about the module at PSRedgate – github. If you find things that need to be improved or bugs, open an issue. If you would like to contribute, please feel free to fork the code base and submit a pull request. I plan to continue maintaining this module for the foreseeable future. I use it every day for administering my databases and so it will continue to grow and evolve.

Automating administration using Redgate

Where did we start?

My friends at Redgate make some pretty amazing tools. SQL Prompt alone increases my quality of life a significant amount. The problem comes when I have too many servers and not enough me. We need to restore several databases on a regular basis to the same point in time. We need to save off schema in our integration environments and reapply it after pulling down new restores. We need to enable push button deployment from version control to our staging environment. All of these things are easy with my Redgate tools, but all of them require me personally login, point, click, and wait. That doesn’t scale, and personally just doesn’t work for me. So I found another way.

The Idea!

I was running a restore for the hundredth time and I see the part at the end that shows you the command it was building using the tool. I remember thinking to myself, I could do that! I should do that. I’ve been using powershell for a few years now. I set out to take the documentation from the redgate website, and try to translate it to a powershell cmdlet that would take the inputs, and turn it into a call to the standard windows command line tools, or to the extended stored procedure that ships with the Redgate tools.

What did I do exactly?

I created a module for interacting with the Redgate command line utilities that make it easier to call on tools like SQL Compare and SQL Backup using powershell. You can execute the command using all of the parameters with added protections afforded to powershell cmdlets. They have the added benefits of not needing to be added to the path and including the documentation directly out of Redgate’s website.

for example, we turn this

Web Documentation sql_backup_docu

into THIS!



What is next?

I’m going to go in depth with how the cmdlets function and how you can use them to automate tasks that you normally do with the tools from Redgate. I will be going over some of the internals and then go over each tool. The following tools are currently part of the module.


All of the code for this module can be found on my github in the project Redgate.


Getting started writing queries

How do we get started?

This is the first article in a series that will go over how to begin writing queries against a database. This will include things like writing code to pull out the data, filtering, inventing new data on the fly, and aggregating things.

The language we’re going to use is SQL and it is the language that we use to tell the database what we want it to return to us, or how we want it to change the data stored within. A lot of this is based off of my experience with SQL Server but I believe that most of the things I will show you fall under the ANSI standard and should be compatible regardless of the platform you are using.

I’m making a few assumptions. You are on a blog that is obviously dedicated to databases so I’m going to skip the part where I laud the merits of storing your data in a relational based system. I’m also going to assume that you have a basic understanding of what a database is used for and what you want to get, but you’re not quite sure how to get it. I will make references or comparisons to programming methodologies as examples but often it’s just to give context, you should be fine without it and can just skip it.  I’m also making an assumption for now that you are actually able to query a database and just need the know how. I’ll write an article at a future date to help you get setup with a local database with some test data. For now I will simply provide the scripts with the sample data that I am using here in my blog and you can run them on your server to use as a basis for following along.

The order of things

Before we get started, I want to lay the ground work for something that was the most confusing thing for me when first starting out writing queries. I’m am a programmer by trade and have experience with languages like Java, C, C#, and Powershell. I’m used to code being executed the way that I write it, and explicitly writing code to make things happen. This type of programming is called Imperative programming and is a common mainstay of modern programming languages. Most mainstream languages are either all or mostly imperative in nature. It was shocking and sometimes befuddling to find out that SQL does not work the same way. This is because SQL is an declarative programming language. Instead of explicitly telling the server where to go, how to find, what methods to use, and what algorithms to sort, you simply say go, find, or sort, and it does.

The first thing we need to understand is the processing order of our queries. I won’t belabor this too much, but I will refer to it again later and so I wanted to put it here now. It’s okay if you don’t understand this right now, it will make more sense later.

Logical query processing order

  1. FROM
  2. JOIN ON
  3. OUTER
  4. WHERE
  10. ORDER BY
  11. TOP

Again, this doesn’t have to make sense yet, but just understand that when the query is being processed, it doesn’t execute the way it is written, instead it follows this order. This will help you later to understand why certain operations don’t work like you would expect them to.

Getting started

I’m going to provide a few files to run that will help you get started, but a quick overview won’t hurt.

We’re going to be creating a few tables:

  • TblIndividual
    • This is a table that contains a people that we use for our example data. This was all generated data.
  •  TblAddresses
    • This is a table containing addresses belonging to our people, we will use this for demonstrating joins and other operations.
  •  TblAddressTypes
    • This is our final table and is used to demonstrate following normal forms and how to do complex, multi-table joins.

Here is a sample of one of the tables. The script attached below has the full command to create and populate the tables, but this is an example of what our individual table looks like.

CREATE TABLE TblIndividuals
, Prefix VARCHAR(50) NULL
, FirstName VARCHAR(50) NOT NULL
, MiddleName VARCHAR(50) NULL
, MaidenName VARCHAR(50) NULL
, Suffix VARCHAR(50) NULL
, Gender VARCHAR(1) NULL
, GraduationYear INT NULL
, Major VARCHAR(100) NULL
, Class INT NULL

We will be using this table and the others to learn how to write queries to access and modify the data stored within. We will then learn how to join the tables together to create a single result set out of multiple sets of data.

Lets SELECT some data!

While not true for all RDMS, in SQL Server, SELECT is the only required keyword to have a functioning query.

SELECT 1 AS 'Test'

Is a valid query that will return a result set. This query will create a column named test, and put the number 1 in it. It will look like this.


This isn’t very interesting though, so let’s get started writing real queries. Our first one is pretty simple.

SELECT i.IndividualID
, i.Prefix
, i.FirstName
, i.MiddleName
, i.LastName
, i.MaidenName
, i.Suffix
, i.Gender
, i.SSN
, i.GraduationYear
, i.GPA
, i.Major
, i.Class
FROM dbo.TblIndividuals i
  • In the above example, according to our order of operations, The FROM is processed 1st, even though it comes after the SELECT.
  • The way that the query is processed is that it takes data from the table and then applies a SELECT filter on it which modifies the columns that come out of the data set.
  • In our case, the SELECT is reducing the number of columns. Below is our result.
IndividualID Prefix FirstName MiddleName LastName MaidenName Suffix Gender SSN GraduationYear GPA Major Class
1 Mrs Warren Blanca Colon M 200089265 1993 4.84 Technical 2008
2 Dr Liza Micah Griffin Morgan F 558709643 2005 3.97 Technical 2002
3 Mr Lakesha Abel Bates M 373646793 2010 4.82 Technical 2013
4 Mr Stacey Maggie Andrews Burgess Ph.D. F 735142950 1997 2.00 Prepaid Customer 2013
5 Mrs Serena Christie Chen D.D. F 224608707 2003 4.69 Accounting 2012

As you can see, we are using FROM to target a table that is our source, and then the SELECT keyword is shaping the data coming out of the table and returning the columns specified to the screen. I should note that you could also use SELECT * but this should really only be used when locating the data that you are after to begin with. SELECT * is considered a bad practice when used in application code because you very seldom need every single column and returning more data than necessary is wasteful and can cause performance problems both in volume and index problems (which we will talk about later).

1.1 – Filters using a WHERE clause

See, that wasn’t so hard! Lets keep diving in and do something a little bit more complicated. What if we only want to look at a small section of this data. Based on some value in a column. We have a column called [Class] this column represents someone’s class year, or when they started school. Let’s write a query to find only people who started school after the year 2000.

SELECT i.IndividualID
, i.Prefix
, i.FirstName
, i.MiddleName
, i.LastName
, i.MaidenName
, i.Suffix
, i.Gender
, i.SSN
, i.GraduationYear
, i.GPA
, i.Major
, i.Class
FROM dbo.TblIndividuals i
WHERE i.Class > 2000;
  • We’re using the same query as before, but this time we’re adding a filter to the data set that we’re pulling back.
  •  Instead of having all individuals, now we only individuals where [Class] is > 2000.
  •  As you can see below, we have less people, and of those people, there are no values in the [Class] column that are less than 2000.
IndividualID Prefix FirstName LastName MaidenName Suffix Gender SSN GraduationYear GPA Major Class
1 Mrs Warren Colon M 200089265 1993 4.84 Technical 2008
2 Dr Liza Griffin Morgan F 558709643 2005 3.97 Technical 2002
3 Mr Lakesha Bates M 373646793 2010 4.82 Technical 2013
4 Mr Stacey Andrews Burgess Ph.D. F 735142950 1997 2.00 Prepaid Customer 2013
5 Mrs Serena Chen D.D. F 224608707 2003 4.69 Accounting 2012
6 Dr. Mandi Greer M 453585211 2013 2.79 Prepaid Customer 2004
7 Ms. Paige Parker M 598517710 1997 2.62 Technical 2006
8 Mr Craig Mc Mahon M 069056634 1998 2.80 Marketing 2011
9 Ms. Milton Moody F 017940008 1992 2.65 Accounting 2002
10 Dr. Freddie Velazquez F 272093597 2006 3.72 Accounting 2009

This is really where the power of SQL comes in. Doing this in an imperative language would be pretty tedious. You’d have to put all of the values into some sort of array structure, loop over them, segment off only ones where one of the attributes was greater than 2000. On top of that, you might even have to manipulate the data you’re keeping to make sure it only has the attributes you wanted. SQL makes it easy to quickly find and shape your data.