Skip to content

Instantly share code, notes, and snippets.

@trashvin
Last active May 9, 2018 02:01
Show Gist options
  • Save trashvin/63560936733217c9635615a325f32004 to your computer and use it in GitHub Desktop.
Save trashvin/63560936733217c9635615a325f32004 to your computer and use it in GitHub Desktop.
Powershell : Backup SQL SP,View, Function read from text file
# types : vw-views, sp-stored procs, fn - function, tg - triggerparam($serverName, $fileName, $type, $location)[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null $dbName = "GlobalSTORE"$srv = new-object Microsoft.SqlServer.Management.Smo.Server($serverName) $db = $srv.Databases.Item($dbName) $folderLocation = $location$type = $type.ToString().ToUpper()
function Alert {   
param($m1,$m2,$m3,$m4,$m5)   
Write-Host $m1 $m2 $m3 $m4 $m5
}
function GetBackupLocation {   
if ($type -eq "VW") {
$folderLocation = $location + "\Views\";   
} elseif ($type -eq "SP") {
$folderLocation = $location + "\StoredProcedures\";   
} elseif ($type -eq "FN") {
$folderLocation = $location + "\Functions\";   
} elseif ($type -eq "TG") {
$folderLocation = $location + "\Triggers\";   
} else {
$folderLocation = $location;   
}   
if(!( Test-Path $folderLocation)) {
New-Item -ItemType Directory -Force -Path $folderLocation | Out-Null   
}       
return $folderLocation
}
function GetProcess {
if ($type -eq "VW") {
$proc = $db.Views | ?{ $_.Name -eq $objectName}   
} elseif ($type -eq "SP") {
$proc = $db.StoredProcedures | ?{ $_.Name -eq $objectName}   
} elseif ($type -eq "FN") {
$proc = $db.UserDefinedFunctions | ?{ $_.Name -eq $objectName}   
} elseif ($type -eq "TG") { 
$proc = $db.Triggers | ?{ $_.Name -eq $objectName}   
} else {
$proc = $null;   
}   
return $proc}
try{   
$reader = [System.IO.File]::OpenText($fileName)   
while($null -ne ($objectName = $reader.ReadLine())) {
Alert "Backing up  " $objectName       
$proc = GetProcess               
if ($proc -eq $null) {
Alert "WARNING : "  $objectName  " NOT FOUND"       
} else {
$retval = $proc.ScriptHeader($true) + $proc.TextBody                         # create backup folder if not exist           
if(!( Test-Path $location)) {
New-Item -ItemType Directory -Force -Path $location | Out-Null           
$folderLocation = GetBackupLocation
$filename = $folderLocation + "\" + $objectName + ".sql" 
$retval > $filename       
}     
}}
finally {    $reader.Close()}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment