Creating an Automated Database Update\Upgrade Script with Redgate SQL Compare Command-Line and Powershell |
|
Problem: The QA team as well as Dev team will update their Development Database with the current code via an update\upgrade script. We fall under the “State or Model Based Approach” meaning “DB Developers only concern themselves with defining the desired end state, not how the transition occurs”, per Redgate documentation regarding DevOps approaches. The current process in place now relies on SQL Compare to compare a previous version of the DB vs the current source control. Once SQL Compare finds the changes and creates the script, a very time-consuming process begins of copy and pasting other changes from other static scripts in TFS into the script created by SQL Compare. It is laborious and often error prone. It can take upwards of 4 hours a week to generate these scripts. Doing some basic math, you could extrapolate that this process costs the company over 10K a year in hours worked.
There has got to be a way to automate this process and schedule an update\upgrade script creation.
Solution: Use SQL Compare Command-Line and Powershell to automate this process.
For this process to work, you must have a DB, locally on your box using Redgate SQL Source Control synced to TFS, GIT or some Source Control platform, as seen below.
The process can be scheduled in an Agent Job or called directly from Powershell.
There has got to be a way to automate this process and schedule an update\upgrade script creation.
Solution: Use SQL Compare Command-Line and Powershell to automate this process.
For this process to work, you must have a DB, locally on your box using Redgate SQL Source Control synced to TFS, GIT or some Source Control platform, as seen below.
The process can be scheduled in an Agent Job or called directly from Powershell.
The following code example includes comments to help you understand the process.
- I have created several Write-Debug messages to help understand what is going on when the script is being executed. By changing the Powershell Preference Variable we can turn off the debug output in one place. I always like seeing stuff printed to the screen.
- The $outfile_for_script variable is where the script will be written too.
- This process will check to see if there are any files that already exist in the directory found in the $outfile_for_script variable. If any files are found it will delete them.
- To run RedGate SQL Compare from the command-line you must set the location to the directory.
Switches Used: I put each switch in the code example below on a single line. When executing this script, you must have all switches on one line.
https://documentation.red-gate.com/sc13/reviewing-the-comparison-results/using-filters
- /Sourcecontrol1 defines what source you are using for Compare
- /revision1:HEAD tells Compare to look at the latest version of your source-controlled DB
- /sfx If using sql source control you must add the SQL Compare XML Fragment in a text file.
- Please see the link below to find this XML Fragment..
- https://documentation.red-gate.com/sc13/using-the-command-line/switches-used-in-the-command-line#Switchesusedinthecommandline-/ScriptsFolderXML
- /server2 is the target server
- /u2 user for the target server
- /p2 password for the target server user account
- /db2 database used on the target server
- /include this switch is issued as it suppresses errors if objects are identical
- /scriptFile tells Compare where to write out script
- /force tells Compare to overwrite the file if one is already in place
- /Quiet will suppress output to the console
- /filter:"<path to sql Compare >\SQL Compare\Filters\Tables_filter.scpf"
- The filter was created in the SQL Compare GUI and saved to the file system.
- Each execution of SQL Compare with the associated filters generates a file with only the objects the filter specifies
- The below links provide all the documentation for the /filter and /switches
https://documentation.red-gate.com/sc13/reviewing-the-comparison-results/using-filters
I also use several options to change the default behavior of SQL Compare and those are explained below.
/Options:ie,ip,f,oec,iup,iu,nc,ndl, dacia
/Options:ie,ip,f,oec,iup,iu,nc,ndl, dacia
The above example executes SQL Compare and filters on only the tables with the specified switches. In my process, I use five different filters and several static files. I won’t bore you with all five filters. Because its more of the same. I do want to show you how I get the static files into the final script. Using the Get-Content cmdlet, I pull out the contents of the static files and append it to the file.
Now that the process is complete, I then place the contents of the script into a new file with a different naming convention MM_DD_YYYY_Upgrade_Version.sql and copy it to the network share. You can add additional logic to check for a files existence as well as run this process multiple times a day if needed.
Schedule this process in a SQL Agent job using the job type Operating System (CmdExec)
Conclusion:
This process will create a .sql script that will update the development database to be in sync with the current development.
I hope this will help with automating an Update\Upgrade script of you Development database.
This process will create a .sql script that will update the development database to be in sync with the current development.
I hope this will help with automating an Update\Upgrade script of you Development database.