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.

Two approaches will be examined. A custom coded approach using PowerShell to script objects into individual DDL scripts and then load them into source control, and a 3^rd 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
   

Committing scripted SQL objects with ApexSQL Script

ApexSQL Script is a SQL scripting tool that allows users scripting of 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 users scripting each SQL object to an individual .sql file and committing them to source control in the following way:

1. Start ApexSQL Script

2. In the New project window, choose the server and a way of authentication, click the Connect button to connect to a SQL Server and select a database:

   

3. Click the Advanced options and select the Object filter tab:

   

4. By default, all objects types with their objects are checked for the scripting process. But, if there is a need to check some particular objects, click on the object type in All objects panel and then check/uncheck desired ones:

   

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

   

6. Once everything is set up, click the Load 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:

   
   

8. Click the Save button from the Home tab in order to save the project file with current project settings and object selection that can be used later for the automation process:

   

9. Click the Script button , from the Home tab, to start the scripting process:

   

10. In the first step of the scripting process, select the Scripting mode and in this example, the Structure mode is selected:

    

11. In the second step, choose the Output type and since in this example objects need to be committed to source control, the Source control output type is selected

    

12. The third step allows users scripting of dependent objects, including them, if needed:

    

13. In the next step, click the Setup button to configure the source control settings.

    

    Note that the source control repository needs to be created before proceeding and more about it can be found from here.

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

    

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

    

16. After setting up the source control settings, click the Automation script button to create a batch or PowerShell script:

    

17. In the Script panel, all current project settings are “translated” into the CLI switches. In order to have less CLI switches, most of them can be replaced with the project switch (/project), by checking the Project file option:

    

    To use it in the automated process, click the Save button in the bottom-right corner of the Automation script window, and the project file and the batch file will be saved on provided location

18. Once, the automations script is created, the last step of the Script wizard will be shown and the Commit button can be clicked to start committing scripts to source control

19. After the committing process is finished, results are shown in the Results dialog as shown 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 8, 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:

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.