ps1/backup-dbscript.ps1
|
function backup-dbScript{ param( [string]$server_conn = ".", [string[]]$databases, # default to all dbs [string]$backup_root = 'c:\mssql\backup\scripts', [string[]]$IncludeTypes = @("StoredProcedures","Views","UserDefinedFunctions", "Triggers"), [int]$keep_days = 7, [string]$object_name, [switch]$force, [switch]$nozip, [switch]$stoponerror, [switch]$log ) # time stamps and dates $now = get-date $ts = $now.toString("yyyyMMdd") # backup folders, remove existing one $backup_path = join-path $backup_root $ts $zip_path = "$($backup_path).zip" $zip_path; # $backup_path; $ts; if( !(test-path -path $backup_path -PathType Container) ){ $null = new-item -Path $backup_path -ItemType Directory -Force } <# $conn = $server_conn $serverx = $conn.Contains(',') ? $conn.split(',')[0] : $conn $instx = $conn.contains('\') ? $serverx.split('\')[1] : $null $instn = $instx ? $instx : $serverx $hostx = $instx ? $serverx.split('\')[0] : $serverx #> # load assemblies, instance, dtabase collection $serverInstance = New-Object Microsoft.SqlServer.Management.Smo.Server($server_conn) # except sys databases $sysdb = @( 'master','msdb', 'tempdb', 'SSISDB', 'model', 'distribution' ) $alldb = $serverInstance.databases # $alldbNames = $alldb.Name if( $databases ){ # check if database exists $databasesFiltered = $alldb | where-object{ $databases -contains $_.name } }else{ # do all databases except sys databases $databasesFiltered = $alldb | where-object{ $sysdb -notcontains $_.name } } $msg = ("databases: $($databasesFiltered.count) ") $msg if( $log.IsPresent ){ $log_file = join-anypath $backup_path 'log.txt' $msg | out-file $log_file } # included types, excluded schemas $ExcludeSchemas = @("sys","Information_Schema",'guest') $batch = @" go "@ $tot_errors = 0 $tot_count = 0 $error_list = @() $timer = [System.Diagnostics.Stopwatch]::StartNew() foreach( $database in $databasesFiltered ){ $dbname = ( ( $database.name -replace "\[","" ) -replace "\]", "" ) (" $($dbname): ") # create database script folder $db_path = join-AnyPath $backup_path $dbname # $db_path if( !(test-path -path $db_path -PathType Container) ){ $null = new-item -Path $db_path -ItemType Directory -Force } $count = 0 $errors = 0 # script only these types foreach( $type in $IncludeTypes ){ $obj_path = join-AnyPath $db_path $type # script objects foreach ( $obj in $database.$type ){ $obj_schema = $obj.Schema -replace "\\", "_" $out_file = join-anypath $obj_path ("$($obj_Schema).$($obj.Name).sql" ) if ( $ExcludeSchemas -notcontains $obj.Schema ){ $count += 1 try{ if( !(test-path -path $obj_path -PathType Container) ){ $null = new-item -Path $obj_path -ItemType Directory -Force } if( [string]::IsNullOrEmpty($object_name) -or $obj.name -eq $object_name ){ $obj.script() | out-file $out_file $batch | out-file $out_file -Append }else{ $count -= 1 } }catch{ $errors += 1 ('-- Unable to script object') | out-file $out_file $error_list += ("$($dbname).$($obj_Schema).$($obj.Name).sql" ) if( $stoponerror ){ throw "unable to script object $($obj.name) " } } } } } $msg = (" $($count-$errors)/$($count) " ) $msg $tot_count += $count $tot_errors += $errors if( $log.IsPresent ){ ("$($dbname): $($msg)") | out-file $log_file -Append } } $msg = ("tot: $($tot_count-$tot_errors)/$($tot_count) " ) $msg if( $log.IsPresent ){ $msg | out-file $log_file -Append $msg = ("errors: $tot_errors") $msg | out-file $log_file -Append $error_file | out-file $log_file -Append } # list of errors file if( $tot_errors -gt 0 ){ $error_file = join-anypath $backup_path "errors.txt" ("errors: $tot_errors $error_file ") $error_list | out-file $error_file } # create zip if( $tot_count -gt 0 -and !$nozip.IsPresent ){ # create zip file compress-archive -path ( join-anypath $backup_path '*' ) -compressionLevel optimal -destinationPath $zip_path -force:$force # verify zip file was created if( test-path -path $zip_path ){ # remove source files if ( test-Path -path $backup_path -PathType Container ){ remove-item -Path $backup_path -Recurse } # remove all zip files older than -keep_days Get-ChildItem -Path $backup_root -filter "*.zip" -File | ` Where-Object { $_.LastWriteTime -lt $now.AddDays( -$keep_days ) } | ` remove-item -force }else{ throw 'Unable to create zip file' } }elseif( !$nozip.IsPresent ){ ("no objects found... ") } # elapse time $timer.Stop() $msg = ( $timer.Elapsed.ToString("hh\:mm\:ss") ) $msg if( $log.IsPresent ){ $msg | out-file $log_file -Append } # exit code for sql jobs return $tot_errors } # backup-dbScript #-force |