My Version Control System for MSSQL

I previously used a trigger to store previous versions of MSSQL stored procedures, documented elsewhere in my blog, at Version Control of Stored Procedures - MSSQL

This was nice and provided a good automated backup for MSSQL structure changes, however I always wanted something to punch through to git.

I now have it...

Step 1: Create a SQLPS script 

$ServerName = "DB\SQL"

$SQL_Databases = @("db1","db2")

$dbentities =@("Tables","StoredProcedures","Triggers","Views","UserDefinedFunctions")

$doTableTriggers = $true

$dumpPath = "D:\DB_Structure_VersionControl"

foreach( $db in $SQL_Databases) {

foreach( $ent in $dbentities) {

$targetPath = "$dumpPath\$db\$ent\"

$pathok = Test-Path $targetPath

if(!$pathok){

    New-Item -ItemType Directory -Path $targetPath

}

foreach ($tbl in Get-ChildItem SQLSERVER:SQL\$ServerName\Databases\$db\$ent )

{

$k="$targetPath” + $($tbl.Schema) + “.” + $($tbl.name) + “.sql”

$tbl.Script() > $k

}

}}

##   Special Case for Triggers#

if($doTableTriggers){

    $targetPath = "$dumpPath\$db\TableTriggers\"

    $pathok = Test-Path $targetPath

    if(!$pathok){

        New-Item -ItemType Directory -Path $targetPath

    }

    foreach ($tbl in Get-ChildItem SQLSERVER:SQL\$ServerName\Databases\$db\Tables )

    {

        if($tbl.triggers -ne $null)

            {

            $k="$targetPath\tbl_TRIGGER." + $($tbl.Schema) + “.” + $($tbl.name) + “.sql”

              foreach ($trigger in $tbl.triggers)

              {

                $trigger.Script() > $k

              }

            }

    }

}



Step 2: Start the SQLPS script with a normal command catch file 

Something like..

sqlps -Command "&{C:\pathtoyourStep1powershellfile\dbs.ps1}"