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, [switch]$force ) # time stamps and dates $now = get-date $ts = $now.toString("yyyyMMdd") #$backdate = $now.AddDays( -$keep_days ).ToString("yyyyMMdd") $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 } # load assemblies, instance, dtabase collection $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 $serverInstance = New-Object Microsoft.SqlServer.Management.Smo.Server $hostx # 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 } } $databasesFiltered.count # included types, excluded schemas $ExcludeSchemas = @("sys","Information_Schema",'guest') $batch = @" go "@ 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 # script only these types foreach( $type in $IncludeTypes ){ $obj_path = join-AnyPath $db_path $type # script objects foreach ( $obj in $database.$type ){ if ( $ExcludeSchemas -notcontains $obj.Schema ){ if( !(test-path -path $obj_path -PathType Container) ){ $null = new-item -Path $obj_path -ItemType Directory -Force } $out_file = join-anypath $obj_path ("$($obj.Schema).$($obj.Name).sql" ) $obj.script() | out-file $out_file $batch | out-file $out_file -Append $count += 1 } } } $count } # create zip file Compress-Archive -Path $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' } } # backup-dbScript #-force |