Feodor Georgiev

19 September 2016

• SQL Compare
• Database Source Control

How to create a directory of object-level scripts using SQL Compare

In this simple ‘how to’ article, I’ll explain why it’s often useful to be able to create a directory of object-level scripts from an existing database, and then how to create that directory of scripts using SQL Compare. For each object in the database there will be one object build file (*.sql). The scripts will reside in a root directory specified by the user (AdventureWorks2014 in this example), automatically categorized into sub-folders according to object type, by SQL Compare.

I’ve used the latest release of SQL Compare v12, with its new and clean interface, together with SQL Server 2014, but the principles are broadly the same for other versions of the Redgate and Microsoft products.

Why do we need to create a directory of database object scripts?

The four most common reasons I need to generate a directory of object scripts are as follows:

• Putting a database into version control
• Tracking changes to a database outside of version control (for example, during Proof of Concept)
• Searching through a directory of scripts
• Deploying just a few changed objects from one database to another

Putting a database into version control

Perhaps the most obvious reason to generate a directory of object scripts is to put an existing database into version control, and it is possible to link the directory of object scripts generated by SQL Compare to a Version Control system (TFS, Git, SVN, and so on).

However, if you do need to put an entire database into version control for the purpose of team-based development, then you’ll probably want to take a look instead at Redgate’s SQL Source Control tool. It uses the SQL Compare engine under the covers to generate the object scripts, but then offers a lot of additional functionality that will allow each developer to commit changes to the repository, update their own repository or directory with the changes of others, resolve merge conflicts, build deployment scripts, and more.

Tracking development changes during Proof of Concept

My favorite use case for creating a directory of object scripts is when I’m working on a Proof of Concept (PoC). At this very early stage of development, I’m often unsure whether my current coding efforts are going to have any value. At PoC, I’m not ready to commit these potentially unstable changes to a VCS. However, I do need to keep track of the previous version of my development iterations, and to have some easy way to keep track of what changes I am doing and what objects are affected by my code.

In this case, the object-level scripting functionality of Redgate’s SQL Compare is a perfect fit. I can generate scripts for the current database, perform the next swathe of database changes, then compare the new database to the existing scripts directory to see exactly what I changed and how, for example, when encountering bugs.

Searching through a directory of scripts

Often I’ll create the database object-level scripts to do a global search-and-replace of text, such as to change a column name across multiple objects. With the object scripts created, it is an easy task to use a third-party tool, such as GREP, to do the search-and-replace.

Building objects from one database schema in another

Another very useful application of SQL Compare’s object-level scripting is when you need to base one database on another one. For example, you may want a few objects from one Payroll database to build another payroll database for a different legislative area. In my case, I often need to build tables in our Staging environment and then in our data mart. The transactional OLTP data must be transformed and then saved to the Staging environment. For example, the data in the ‘raw’ rCustomers table must be transformed and saved to the sCustomers table in Staging. From Staging we merge the data into the dCustomers table in the data mart.

We find SQL Compare a great tool to help set up the Staging tables and the data mart, since all we need to do is script the objects to a folder, grab the scripts of the needed objects, change just the object names and create them in the new database. The benefit of this approach is that we get to audit what is created in the destination and we have options to control the granularity of the objects: we can choose whether we want constraints, indexes and so on.

SQL Compare demo: how to create a directory of object scripts from an existing database

We’ll now walk through the simple steps to create the directory of object scripts, using SQL Compare. The following screens are from v12 of the tool; if you’re using an earlier version they will look very different, but the various options I describe haven’t changed.

Start SQL Compare 12, open a New Project and select Database as the source.

I connected to my local instance of SQL Server 2014 using Windows credentials, and established AdventureWorks2014 as the source database.

For the Target, select Scripts folder (though note, as discussed earlier, that you can also select a version control directory as the target).

On the next screen, since we’re creating scripts from this database for the first time, we need to specify a folder in which the object scripts will be saved. I’ve chosen SQLCompareScripts\AdventureWorks2014. A very valuable additional step here is to ensure that the default collation setting and SQL Server version for the scripts are set to the correct values. If you’re updating a database and use the wrong collation setting, it could cause collation mismatches later on, when joining to other tables.

Click on the Compare now button and SQL Compare will compare the source database to the target directory (currently empty) and generate a list of objects that exist only in the source (all of them, in this case), or exist only in the target, or exist in both but have differences. In this case, there are 142 objects that exist only in the database. There are also 23 objects listed as ‘identical’ in both source and target. These are various fixed database roles and associated schema. Since SQL Server creates these automatically for every database, SQL Compare does not script out these objects.

On the left hand side of this screen, SQL Compare offers custom filter rules that let us choose any objects you may wish to exclude from the comparison.

Expand the non-identical objects, and click on any one, and you’ll see the side-by-side comparison of the object script in the two environments. Since the object only exists in the database, we’re essentially just seeing the object build script that will be created for that object in the directory. We can also use the object check boxes to exclude individual objects, if necessary. For example, a developer might be working on objects in one particular schema, or might be refactoring only the View objects and will want to script only those objects.

In this example, select all the objects by checking the very top box.

When you’re ready, hit the Deploy button. At the next screen, we have the option simply to have SQL Compare write the scripts to the target folder directly, or we can ask it to generate a deployment script, which we can inspect in SSMS and run manually. We’ll just select the former option here.

We also have the option to create a snapshot of the target before deploying changes. It’s just an empty directory here, so we won’t, but in some cases this can be useful.

On clicking Next, SQL Compare will generate the SQL script to synchronize the source database and the empty directory. In this case, that simply means creating scripts for every object. It will warn of any dependencies (where objects included in the deployment reference or are referenced by objects not included in the deployment such that the deployment cannot proceed automatically). There won’t be any dependencies in this case and we simply see one warning that full text indexes can’t be deployed as part of a transaction, so will be deployed separately.

Click Update Scripts. SQL Compare will fill the specified directory with object scripts, as shown previously in Figure 1, and will now report that all objects in source and target are identical.

We can now save this project, and reuse it. Every subsequent synchronization of the source database and target directory will result in the contents of the files in that directory changing to reflect the current state of the source database. The synchronization will result in the file of any changed object being updated, the addition of a file for every new object and the deletion of the file of any object that is deleted in the database.

The SQL Compare command line approach

It you want to automate this process then you can simply call the SQL Compare command line tool from PowerShell. I’m not going to delve into the command line details in this article, but the PowerShell script below will compare the live AdventureWorks2014 database to an existing directory (\Documents\GitHub\AdventureWorks2014, in this example) and then the call to SQL Compare Pro will synchronize them.

$SQLCompare="${env:ProgramFiles(x86)}\Red Gate\SQL Compare 12\sqlcompare.exe"# full path
$MyServerInstance='MyServer\MyInstance' #The SQL Server instance
$MyDatabase='AdventureWorks2014' #The name of the database
$MyDatabasePath =
"$($env:HOMEDRIVE)$($env:HOMEPATH)\Documents\GitHub\$MyDatabase"

$AllArgs = @("/server1:$MyServerInstance", "/database1:$MyDatabase ",
"/scripts2:$MyDatabasePath", '/q', '/synch',
"/report:$($MyDatabasePath).html",
"/reportType:Simple","/rad","/force" )
&$SQLCompare $AllArgs
if ($?) {'updated successfully'}
else {if ($LASTEXITCODE=63) {'Database and scripts were identical'}
else {"we had an error! (code $LASTEXITCODE)"}}

Just adapt the variable values as required, paste the script into the PowerShell ISE and run it. This script assumes the servers are part of the same domain, but if not, you might need credentials with SQL Server security, meaning that all the $AllArgs settings will be different.

If the directory is empty, SQL Compare will write out all the script files and create the necessary subdirectories to hold them. Each time we run the script, after making changes to the database, SQL Compare will resynchronize the two, updating any changed files, adding files for new database objects, and so on.

Summary

I think SQL Compare is perfect for single-developer tasks. Its ability to create, and keep synchronized, a directory of objects script for a database is a great way to track changes during small, single-developer projects, or during Proof of Concept!

If you’d like to read more articles by Feodor Georgiev, you can find him on Simple Talk, and at sqlconcept.com.