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"
Step 2: Start the SQLPS script with a normal command catch file
Something like..
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}"
Comments
Post a Comment