How to automate batch SQL object scripting and committing to source control

Many development teams have the need for a quick and easy but effective solution to committing SQL Server database objects to source control, but aren’t yet ready to move to full source control integration at check in/check out level.

In order to get objects under a source control in a “Poor man’s” approach, creation scripts need to be produced for each table, stored procedure, and any other object in a database. Afterwards, the scripts need to be committed to a source control system.

We’ll examine two approaches. A custom coded approach using Powershell to script objects into individual DDL scripts and then load them into source control, and a 3rd party solution, that specializes in scripting and loading objects into source control.

Using a PowerShell project and a batch file

One way to automate the process of committing SQL objects to a source control system is to:

  1. Create a PowerShell project that will script the desired SQL objects in a database:

    $ServerName='CRaZY' # enter the name of a server
    $Database='ObjectScripting' # enter the name of a database
    $DirectoryToSaveTo='D:\SC\ScriptObject' # enter the directory where to store SQL scripts for objects
    # load SMO assembly, and if SQL 2008 DLLs are run, load the SMOExtended and SQLWMIManagement libraries
    $v = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')
    if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
    }
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SmoEnum') | out-null
    set-psdebug -strict # catch a few extra bugs
    $ErrorActionPreference = "stop"
    $My='Microsoft.SqlServer.Management.Smo'
    $srv = new-object ("$My.Server") $ServerName # Attach to the server
    if ($srv.ServerType-eq $null) # If it managed to find a server
                    {
       Write-Error "Sorry, but I couldn't find Server '$ServerName' "
       return                
    } 
    $scripter = new-object ("$My.Scripter") $srv # create the scripter
    $scripter.Options.ToFileOnly = $true 
    # first we get the bitmap of all the object types we want 
    $all =[long] [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::all
    # and we store them in a datatable
    $d = new-object System.Data.Datatable
    # get everything except the servicebroker object, the information schema and system views
    $d=$srv.databases[$Database].EnumObjects([long]0x1FFFFFFF -band $all) | `
        Where-Object {$_.Schema -ne 'sys'-and $_.Schema -ne "information_schema" -and $_.DatabaseObjectTypes -ne 'ServiceBroker'}
    # and write out each scriptable object as a file in the directory you specify
    $d| FOREACH-OBJECT { # for every object we have in the datatable.
                    $SavePath="$($DirectoryToSaveTo)\$($_.DatabaseObjectTypes)"
                    # create the directory if necessary (SMO doesn't)
                    if (!( Test-Path -path $SavePath )) # create it if not existing
                        {Try { New-Item $SavePath -type directory | out-null } 
                     Catch [system.exception]{
                        Write-Error "error while creating '$SavePath' $_"
                         return
                      } 
                    }
                    
                    $scripter.Options.Filename = "$SavePath\$($_.name -replace '[\\\/\:\.]','-').sql";
                    # Create a single element URN array
        $UrnCollection = new-object ('Microsoft.SqlServer.Management.Smo.urnCollection')
                    $URNCollection.add($_.urn)
                    # and write out the object to the specified file
        $scripter.script($URNCollection)
    } 
  2. Save the following code as a batch file, to commit scripted SQL objects to source control as a batch file:

    @echo off
    Powershell.exe -executionpolicy remotesigned -File D:\SC\ScriptObject\PSscript\Script.ps1
    timeout 10
    cd D:\SC\ScriptObject
    %ProgramFiles(x86)%\Microsoft Visual Studio 10.0\Common7\IDE\TF.exe add 
    D:\SC\ScriptObject>tf checkin /noprompt
    
  3. Committing a scripted SQL objects with ApexSQL Script

    ApexSQL Script is a SQL scripting tool that allows scripting SQL Server database objects and committing SQL scripts to source control. It can export databases directly to source control and it can automate SQL scripting and schedule the migration process unattended.

    ApexSQL Script also allows scripting each SQL object to an individual .sql file and committing them to source control:

    1. Start ApexSQL Script
    2. In the New project window, choose the server and a way of authentication, click the Connect button connect to a server and select a database:

    3. In the Project dialog, click the Show advanced options button and select the Object filter tab:

    4. By default, all objects are checked for the scripting process. But, if there is a need to check some particular objects, click the Filter checkbox for a desired object type and select that object type node in the Select object types to script window:

    5. Select all desired objects to include in the scripting process:

    6. Once everything is set up, click the Open button in the bottom-right corner, of the New project window, to load database objects:

    7. Under the Structure view, select objects for committing to source control and click the Script button, from the Home tab, to start the scripting process:

    8. In the first step of the scripting process, select the scripting mode and the output type:

    9. The second step allows scripting dependent objects, if needed:

    10. In the next step, choose the Create and commit to source control action, from the Action drop-down list, and click the Edit button to configure the source control settings. Please note that the source control repository needs to be created before proceeding and more about it can be found from here.

    11. In the Source control wizard, choose the source control system and click the Next button:

    12. Set the login information for the selected source control system and click the Finish button:

    13. After setting up the source control, click the Script file tab and select the Script each object into an individual file option from the Granularity drop-down list:

    14. Click the Next button and the Export batch step will be shown:

      In the Batch script pane, all current project settings are “translated” into the CLI switches. In order to have less CLI switches, all of them can be replaced with the project switch ( /pr), by checking the Use project file option in the bottom-left corner:

      To use it in the automated process, click the Save as batch button, and the project file along with the batch file will be saved on provided location

    15. Click the Create button to start committing scripts to source control

    16. After the committing process is finished, results are shown in the window below:

    Automating the process with ApexSQL Script CLI

    The other way to perform committing individual scripts to a source control repository is by using ApexSQL Script’s Command Line Interface (CLI). The easiest way is to save the project file, as in the step 14, because it’s easier to set all of those settings in the GUI, than adding switches for each option in the CLI.

    After saving the project file, call it from the CLI and add the rest of the optional switches, to commit scripts to source control:

    “ApexSQLScript.com” # application path
    /pr:”MyProject.axsc” # project file path
    /v # prints all messages in the console
    /f # overwrites existing script files
    /out:”Output.txt” # redirects console output to a specified file
    @echo ApexSQL Script return code is %errorlevel% >> /out:”Output.txt” # return code upon finished scripting process

    All ApexSQL Script CLI switches are explained in the article on this link, along with some working examples.

    If there is a need to allow prompting for database/login credentials, check out the article on this link for information on how to achieve it.

    April 15, 2015