dbaship.psm1
|
<#
.SYNOPSIS Provides scripts to build a basic logshipping process get-help about_dbaship .PARAMETER version 0.0.8 .DESCRIPTION #> # ======================================================================================= function get-shipHelp{ get-help about_dbaship } function out-shipMsg{ <# .SYNOPSIS For internal use of dbaShip module only #> param( [string]$param, [string]$value, [switch]$flush ) if( !$script:msg ){ $script:msg = @() } if( $param ){ $script:msg += [pscustomobject]@{ param = $param # value = $value # ( } } if( $flush.IsPresent ){ $msg | format-table -AutoSize if( $script:msg ){ $script:msg = @() } } #log if( $script:ship_log_folder ){ if( test-path $script:ship_log_folder -PathType Container ){ ( ($param+(" "*50)).Substring(0,35)+" "+($value+(" "*100)).Substring(0,100) ) | out-file $ship_log -Append } } } function join-AnyPath { ($args -join '\') -replace '(?!^)[\\/]+', [IO.Path]::DirectorySeparatorChar } function get-shipValue{ <# .SYNOPSIS For internal use of dbaShip module only #> param( [System.Object]$res, [string]$key, [switch]$echo ) $ret = $null $found = "" foreach( $row in $res ){ if( $row.param -eq $key ){ $ret = $row.value } if( $echo.IsPresent ){ if( $ret ){ #-and $found -eq "" $found = "*" } write-output "$( ($row.param+(" "*50)).Substring(0,35) )$($row.value)$($found)" $found = " " } } return $ret } function get-shipParams{ [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns","")] # For internal use of dbaShip module only param( [System.Management.Automation.PSCredential]$credential, [string]$primary_server = ".", [string]$primary_database, [string]$primary_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_root, [string]$data_root, [string]$log_root, [switch]$nosql ) $primary_server = $primary_server.ToUpper() $secondary_server = $secondary_server.ToUpper() $script:hoy = get-date -f "yyyyMMdd" $script:ts = get-date -f "yyyyMMddHHmm" # log out-shipMsg 'command' ( Get-PSCallStack )[1].Command out-shipMsg 'ts' $ts if( $credential -and $credential.UserName ){ out-shipMsg 'UserName' $credential.UserName }else{ throw '-credential not found' } # verify if( [string]::IsNullOrEmpty($primary_root) ){ throw '-primary_root not found' } if( [string]::IsNullOrEmpty($secondary_root) ){ throw '-secondary_root not found' } # default if ( [string]::IsNullOrEmpty($secondary_database) ){ $secondary_database = $primary_database $script:secondary_database = $secondary_database } $script:share_name = Split-Path $primary_root -Leaf $script:ship_backup_directory = join-anypath $primary_root $primary_database $script:ship_share = join-anypath '\' $primary_server $share_name $script:ship_destination_directory = join-anypath $secondary_root $primary_database $script:UserName = $credential.UserName $script:backup_job_name = 'LSBackup_'+$primary_database $script:backup_source_directory = $ship_share $script:copy_job_name = 'LSCopy_'+$primary_server.ToUpper()+'_'+$primary_database $script:backup_schedule_name = 'LSBackupSchedule_'+$primary_database $script:copy_schedule_name = 'LSCopySchedule_'+$primary_server.ToUpper()+'_'+$primary_database $script:restore_job_name = 'LSRestore_'+$primary_server.ToUpper()+'_'+$primary_database $script:restore_schedule_name = 'LSRestoreSchedule_'+$primary_server.ToUpper()+'_'+$primary_database; $script:primary_monitor = $primary_server $script:secondary_monitor = $secondary_server $script:primary_backup_name = $primary_database+'_'+$ts+'.bak' $script:primary_backup = join-anypath $primary_root $primary_database $script:primary_backup_name $script:server_root = $env:computername -eq $primary_server ? $primary_root : $secondary_root ; $script:ship_log_folder = join-anypath $script:server_root "dbaship" $script:ship_log = join-anypath $script:ship_log_folder "dbaship_$($hoy).txt" $script:standby_folder = join-anypath $secondary_root "standby" $script:data_folder = join-anypath $data_root 'DATA' $script:log_folder = join-anypath $log_root 'Log' if( $nosql.IsPresent ){ return; } # load sqlserver module if( !(get-module sqlserver) ){ import-module sqlserver } $sqlparams = @" -- $ts if db_name()<>'master' use master; set nocount on; select [param]='@ts', value=convert(varchar(500),'$ts' ); "@ return $sqlparams } function show-shipParams{ [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns","")] param( [System.Management.Automation.PSCredential]$credential, [string]$primary_server, [string]$primary_database, [string]$primary_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_root, [string[]]$databases, [string]$data_root, [string]$log_root ) get-shipParams @PSBoundParameters -nosql $secondary_database = $script:secondary_database write-host "`$hoy $script:hoy " write-host "`$ts $script:ts " write-host "`$secondary_database $script:secondary_database " write-host "`$share_name $script:share_name " write-host "`$ship_backup_directory $script:ship_backup_directory " write-host "`$ship_share $script:ship_share " write-host "`$ship_destination_directory $script:ship_destination_directory " write-host "`$UserName $script:UserName " write-host "`$backup_job_name $script:backup_job_name " write-host "`$backup_source_directory $script:backup_source_directory " write-host "`$copy_job_name $script:copy_job_name " write-host "`$backup_schedule_name $script:backup_schedule_name " write-host "`$copy_schedule_name $script:copy_schedule_name " write-host "`$restore_job_name $script:restore_job_name " write-host "`$restore_schedule_name $script:restore_schedule_name " write-host "`$primary_monitor $script:primary_monitor " write-host "`$secondary_monitor $script:secondary_monitor " write-host "`$primary_backup_name $script:primary_backup_name " write-host "`$primary_backup $script:primary_backup " write-host "`$server_root $script:server_root " write-host "`$ship_log_folder $script:ship_log_folder " write-host "`$ship_log $script:ship_log " write-host "`$standby_folder $script:standby_folder " write-host "`$data_folder $script:data_folder " write-host "`$log_folder $script:log_folder " } function test-shipConfig{ [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns","")] param( [System.Management.Automation.PSCredential]$credential, [string]$primary_server, [string]$primary_database, [string]$primary_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_root, # [string]$data_root, [string]$log_root, # [string[]]$databases, # [switch]$verbose, [switch]$force, [switch]$show ) get-shipParams @PSBoundParameters -nosql > null $secondary_database = $script:secondary_database out-shipMsg 'servername' $env:computername if( $env:computername -eq $primary_server ){ out-shipMsg "server" "primary_server" $server = $primary_server }else{ out-shipMsg "server" "secondary_server" $server = $secondary_server } $sqlserver = ( get-module sqlserver ) if( $sqlserver ){ out-shipMsg 'sqlserver' 'module imported' }else{ out-shipMsg 'sqlserver' 'module not found...' } $version = $psversiontable.psversion.tostring() out-shipMsg "PSVersion" $version if( $psversiontable.psversion.major -lt 7 ){ out-shipMsg "Powershell" "Upgrade to 7+" } if( $env:computername -eq $primary_server ){ out-shipMsg 'primary_root' $primary_root if ( !(test-path $primary_root -PathType Container) ){ out-shipMsg $primary_root 'not found...' } # check share $shareExists = Get-SmbShare | Where-Object { $_.Path -eq $primary_root } out-shipMsg 'share' $ship_share if( $shareExists ){ out-shipMsg $ship_share 'Shared' }else{ out-shipMsg $ship_share 'Not found...' } # check access to share if( (test-path -path $ship_share ) ){ out-shipMsg $ship_share 'Accessible' }else{ out-shipMsg $ship_share 'Is not accessible...' } if( $databases.count -eq 0 ){ out-shipMsg '$databases' 'no datatabes provided' }else{ out-shipMsg "databases" $databases.count $query = "" foreach( $database in $databases ){ $query = $query +" union all select '$($database)' [database]`n" } # write-output $query # write-output "--test--" # write-output ($databases -join ",") } } if( $env:computername -eq $secondary_server ){ out-shipMsg 'secondary_root' $secondary_root if ( !(test-path $secondary_root -PathType Container) ){ out-shipMsg 'secondary_root' 'not found...' } # check access to share if( (test-path -path $ship_share ) ){ out-shipMsg $ship_share 'Accessible' }else{ out-shipMsg $ship_share 'Is not accessible...' } # check data and logs root out-shipMsg 'data_root' $data_root if ( !(test-path $data_root -PathType Container) ){ out-shipMsg 'data_root' 'not found...' } out-shipMsg 'log_root' $log_root if ( !(test-path $log_root -PathType Container) ){ out-shipMsg 'log_root' 'not found...' } } $query = @" SELECT [param] = princ.name collate Latin1_General_CI_AS, [value] = princ.type FROM sys.server_principals princ INNER JOIN sys.syslogins logins ON princ.sid = logins.sid WHERE logins.sysadmin = 1 union select UserName = princ.name collate Latin1_General_CI_AS, UserType = princ.type from sys.database_principals princ INNER JOIN sys.syslogins logins ON princ.sid = logins.sid WHERE logins.sysadmin = 1; "@ $res = invoke-sqlcmd ` -ServerInstance $server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query ` -Verbose # verify sysadmin role $admin = get-shipValue $res $credential.UserName #-echo out-shipMsg 'UserName' $credential.UserName if( $admin ){ out-shipMsg 'sysadmin' 'OK' }else{ out-shipMsg $credential.UserName 'Is not sysadmin...' } out-shipMsg "dbaship_log" $ship_log if( !(test-path $ship_log_folder -PathType Container) ){ out-shipMsg "dbaship_log_folder" "not found..." } out-shipMsg -flush # show list of calculated parameters from basic ones if( $show.IsPresent ){ $show_params = @{} + $PSBoundParameters $show_params.remove('force') | Out-Null $show_params.remove('list') | Out-Null show-shipParams @show_params } } # ======================================================================================= function get-shipConfig{ param( [System.Management.Automation.PSCredential]$credential, [string]$primary_server, [string]$primary_database, [string]$primary_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_root, [switch]$verbose, [switch]$all ) get-shipParams @PSBoundParameters | out-null $secondary_database = $script:secondary_database $query1 = @" select @@servername [primary_server], primary_database, secondary_server, secondary_database, case when user_specified_monitor = 1 then monitor_server else null end [monitor_server], last_backup_date, backup_directory from msdb.dbo.log_shipping_primary_databases pd left join msdb.dbo.log_shipping_primary_secondaries ps on ps.primary_id = pd.primary_id; "@ $res1 = invoke-sqlcmd ` -ServerInstance $primary_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query1 ` -Verbose $res1 |format-table -autosize $query2 = @" select primary_server, primary_database, @@servername [secondary_server], secondary_database, case when user_specified_monitor = 1 then monitor_server else null end [monitor_server], last_copied_date, last_restored_date, restore_mode from msdb.dbo.log_shipping_secondary lss join msdb.dbo.log_shipping_secondary_databases lssd on lssd.secondary_id = lss.secondary_id; "@ $res2 = invoke-sqlcmd ` -ServerInstance $secondary_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query2 ` -Verbose $res2 |format-table -autosize out-shipMsg -flush } function add-shipFolders{ [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns","")] param( [System.Management.Automation.PSCredential]$credential, [string]$primary_server, [string]$primary_database, [string]$primary_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_root, [string[]]$databases, [string]$data_root, [string]$log_root, [switch]$grant, [switch]$verbose ) if( !$PSBoundParameters.ContainsKey('databases') ){ $databases += $primary_database } get-shipParams @PSBoundParameters | out-null $secondary_database = $script:secondary_database out-shipMsg 'servername' $env:computername if( $env:computername -eq $primary_server -or $env:computername -eq $secondary_server ){ if( $env:computername -eq $primary_server ){ out-shipMsg "server" "primary_server" }else{ out-shipMsg "server" "secondary_server" } # check if root exist, creates it out-shipMsg 'server_root' $server_root if ( !(test-path $server_root -PathType Container) ){ new-item -Path $server_root -ItemType Directory -Force | out-null } # create database folders for log shipping trn and bak foreach( $database in $databases ){ $path = join-path $server_root $database out-shipMsg ("db:"+$database) $path new-item -Path $path -ItemType Directory -Force | out-null } # log folder for dbaship out-shipMsg 'dbaship logs' $ship_log_folder if ( !(test-path $ship_log_folder -PathType Container) ){ new-item -Path $ship_log_folder -ItemType Directory -Force | out-null } # only in primary if( $env:computername -eq $primary_server ){ #check if share exiss out-shipMsg "ship_share" $ship_share if ( !(Get-SmbShare -Name $share_name -ErrorAction SilentlyContinue) ){ out-shipMsg "warning" "share does not exists..." } } #only in secondary if( $env:computername -eq $secondary_server ){ # create database data and log folders in secondary out-shipMsg 'mdf' $data_folder new-item -Path $data_folder -ItemType Directory -Force | out-null out-shipMsg 'ldf' $log_folder new-item -Path $log_folder -ItemType Directory -Force | out-null # creates standby folder out-shipMsg 'standby' $standby_folder if ( !(test-path $standby_folder -PathType Container) ){ new-item -Path $standby_folder -ItemType Directory -Force | out-null } } } out-shipMsg -flush <# if( $grant.IsPresent ){ # get agent $query = @" SELECT [param]='@servicename', value= service_account FROM master.sys.dm_server_services WHERE servicename LIKE 'SQL Server Agent%'; "@ # get agent $res = invoke-sqlcmd ` -ServerInstance $server ` -Database master ` -Credential $Credential ` -TrustServerCertificate ` -Query $query ` -Verbose $agent = get-shipValue $res '@servicename' #-echo out-shipMsg "`$agent" $agent if( !$agent ){ throw "Error: Service account not found..." } } #> } function Install-shipPrimary{ param( [System.Management.Automation.PSCredential]$credential, [string]$primary_server, [string]$primary_database, [string]$primary_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_root, [switch]$verbose, [switch]$script ) $sqlparams = get-shipParams @PSBoundParameters $secondary_database = $script:secondary_database $query = @" $sqlparams -- Execute the following statements at the Primary to configure Log Shipping -- for the database [$primary_server].[$primary_database], -- The script needs to be run at the Primary in the context of the [msdb] database. ------------------------------------------------------------------------------------- select [param]='@primary_server', value = convert(varchar(100), '$primary_server' ) union all select [param]='@primary_database', value = convert(varchar(100), '$primary_database' ) DECLARE @LS_BackupJobId AS uniqueidentifier DECLARE @LS_PrimaryId AS uniqueidentifier DECLARE @SP_Add_RetCode As int -- must run from master EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database @database = N'$primary_database' ,@backup_directory = N'$ship_backup_directory' ,@backup_share = N'$ship_share\$primary_database' ,@backup_job_name = N'$backup_job_name' ,@backup_retention_period = 7200 ,@backup_compression = 1 ,@backup_threshold = 45 ,@threshold_alert_enabled = 1 ,@history_retention_period = 7200 ,@backup_job_id = @LS_BackupJobId OUTPUT ,@primary_id = @LS_PrimaryId OUTPUT ,@overwrite = 1 select [param]='@SP_Add_RetCode', value = convert(varchar(100), @SP_Add_RetCode) IF (@@ERROR = 0 AND @SP_Add_RetCode = 0) BEGIN select [param]='@backup_job_name', value = convert(varchar(100), '$backup_job_name' ) DECLARE @LS_BackUpScheduleUID As uniqueidentifier DECLARE @LS_BackUpScheduleID AS int EXEC msdb.dbo.sp_add_schedule @schedule_name = N'$backup_schedule_name' ,@enabled = 1 ,@freq_type = 4 ,@freq_interval = 1 ,@freq_subday_type = 4 ,@freq_subday_interval = 15 ,@freq_recurrence_factor = 0 ,@active_start_date = $hoy ,@active_end_date = 99991231 ,@active_start_time = 0 ,@active_end_time = 235900 ,@schedule_uid = @LS_BackUpScheduleUID OUTPUT ,@schedule_id = @LS_BackUpScheduleID OUTPUT EXEC msdb.dbo.sp_attach_schedule @job_id = @LS_BackupJobId ,@schedule_id = @LS_BackUpScheduleID EXEC msdb.dbo.sp_update_job @job_id = @LS_BackupJobId ,@enabled = 1 END select [param]='@primary_AlertJob', value = convert(varchar(100), 'TBD') EXEC master.dbo.sp_add_log_shipping_alert_job select [param]='@secondary_server', value = convert(varchar(100), '$secondary_server' ) union all select [param]='@secondary_database', value = convert(varchar(100), '$secondary_database' ) -- must run from master EXEC master.dbo.sp_add_log_shipping_primary_secondary @primary_database = N'$primary_database' ,@secondary_server = N'$secondary_server' ,@secondary_database = N'$secondary_database' ,@overwrite = 1 select [param]='Ret', value='0'; "@ if( $script.IsPresent ){ write-output $query }else{ invoke-sqlcmd ` -ServerInstance $primary_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query ` -Verbose } } function backup-shipPrimary{ param( [System.Management.Automation.PSCredential]$credential, [string]$primary_server, [string]$primary_database, [string]$primary_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_root, # [switch]$verbose, [switch]$script ) $sqlparams = get-shipParams @PSBoundParameters $secondary_database = $script:secondary_database # verify the first logshipping has run $query1 = @" declare @last_backup_date datetime; select @last_backup_date = last_backup_date from msdb.dbo.log_shipping_primary_databases where primary_database = '$primary_database' if @last_backup_date is null select 0 found; else select 1 found; "@ $res = invoke-sqlcmd ` -ServerInstance $primary_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query1 ` -Verbose if( $res.found -eq 0 ){ throw "Job $backup_job_name has not run yet. Wait for Job to run or run Job manually." } $query2 = @" $sqlparams BACKUP DATABASE [$primary_database] TO DISK = N'$primary_backup' WITH COPY_ONLY, FORMAT, INIT, NAME = N'$primary_database-Full Database Backup copy_only', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 5 "@ write-output $query2 invoke-sqlcmd ` -ServerInstance $primary_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query2 ` -Verbose } # ============================================ function copy-shipBackup{ param( [System.Management.Automation.PSCredential]$credential, [string]$primary_server, [string]$primary_database, [string]$primary_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_root, # [string]$source, [switch]$verbose, [switch]$script ) get-shipParams @PSBoundParameters > null $secondary_database = $script:secondary_database if( (!$script.IsPresent) -and $env:COMPUTERNAME.ToUpper() -ne $secondary_server.ToUpper() ){ throw "This script must be run in [$secondary_server]" } if( !$PSBoundParameters.ContainsKey('source') ){ $source = $ship_share+"\"+$primary_database } $destin = $ship_destination_directory #$log = "$ship_destination_directory\copylog_$hoy.txt" $cmd = { robocopy "$source" "$destin" "*.bak" /XO /ZB /R:3 /W:30 /MT:4 /TEE /LOG+:"$ship_log" } if( $script.IsPresent ){ $cmds = $cmd.ToString() $cmds = $cmds.Replace( '$source', $source ) $cmds = $cmds.Replace( '$destin', $destin ) $cmds = $cmds.Replace( '$log', $log ) write-output $cmds }else{ # Enable-PSRemoting -Force Invoke-Command ` -ScriptBlock $cmd ` -ArgumentList $source,$destin,$log } out-shipMsg -flush # -ComputerName $secondary_server `-Credential $credential ` } function restore-shipSecondary{ param( [System.Management.Automation.PSCredential]$credential, [string]$primary_server, [string]$primary_database, [string]$primary_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_root, # [string]$data_root, [string]$log_root, # [switch]$verbose, [switch]$script, [switch]$bypass ) # defaults if( !$PSBoundParameters.ContainsKey('data_root') ){ $data_root = "C:\MSSQL" } if( !$PSBoundParameters.ContainsKey('log_root') ){ $log_root = $data_root } get-shipParams @PSBoundParameters > null $secondary_database = $script:secondary_database if( !$bypass.IsPresent -and ( $env:COMPUTERNAME.ToUpper() -ne $secondary_server.ToUpper() ) ){ throw "This script must be run in [$secondary_server]" } # find most recent backup file $bak = (Get-ChildItem -Path $ship_destination_directory -Filter "*.bak" -File | Sort-Object LastWriteTime -Descending | Select-Object -First 1 ).FullName if( !$script.IsPresent ){ # create folder for database data and log if ( !(test-path $data_folder -PathType Container) ){ out-shipMsg 'data_folder' $data_folder new-item -Path $data_folder -ItemType Directory -Force | out-null } if ( !(test-path $log_folder -PathType Container) ){ out-shipMsg 'log_folder' $log_folder new-item -Path $log_folder -ItemType Directory -Force | out-null } } if( $script.IsPresent ){ # basic logical and physical $moves = @" MOVE N'$primary_database' TO N'$data_folder\$secondary_database.mdf', MOVE N'$($primary_database)_log' TO N'$log_folder\$($secondary_database)_log.ldf', "@ }else{ # get logical and physical files from primary database $query1 = @" select d.name [database], f.name [logical], 'MOVE N'''+f.name+''' TO N''' +case f.type_desc when 'rows' then '$data_folder\' when 'log' then '$log_folder\' else '$data_folder\' end +case when f.type_desc='rows' and file_id = 1 then '$secondary_database.mdf''' when f.type_desc='rows' and file_id > 1 and file_id<100 then '$($secondary_database)_'+right('0'+convert(varchar(2),file_id),2)+'.ndf''' when f.type_desc='log' then '$($secondary_database)_log.ldf''' else '$($secondary_database)_'+convert(varchar(12),file_id)+'.ndf''' end [physical] FROM sys.master_files f INNER JOIN sys.databases d ON d.database_id = f.database_id where d.name = '$primary_database' order by d.name,f.file_id; "@ $files = invoke-sqlcmd ` -ServerInstance $primary_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query1 ` -Verbose $moves = "" foreach( $file in $files ){ $moves = $moves+' '+$file.physical+",`n" } } $query2 = @" -- use master RESTORE DATABASE [$secondary_database] FROM DISK = N'$bak' WITH FILE = 1, $moves NORECOVERY, NOUNLOAD, STATS = 5 "@ write-output $query2 if( !$script.IsPresent ){ invoke-sqlcmd ` -ServerInstance $secondary_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query2 ` -Verbose } out-shipMsg -flush } function Install-shipSecondary{ param( [System.Management.Automation.PSCredential]$credential, [string]$primary_server, [string]$primary_database, [string]$primary_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_root, # [switch]$verbose, [switch]$script ) $sqlparams = get-shipParams @PSBoundParameters $query = @" -- Execute the following statements at the Secondary to configure Log Shipping -- for the database [$secondary_server].[$primary_database], -- the script needs to be run at the Secondary in the context of the [msdb] database. ------------------------------------------------------------------------------------- $sqlparams select [param]='@secondary_server', value = convert(varchar(100), '$secondary_server' ) union all select [param]='@secondary_database', value = convert(varchar(100), '$secondary_database' ) DECLARE @LS_Secondary__CopyJobId AS uniqueidentifier DECLARE @LS_Secondary__RestoreJobId AS uniqueidentifier DECLARE @LS_Secondary__SecondaryId AS uniqueidentifier DECLARE @LS_Add_RetCode As int EXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary @primary_server = N'$primary_server' ,@primary_database = N'$primary_database' ,@backup_source_directory = N'$backup_source_directory\$primary_database' ,@backup_destination_directory = N'$ship_destination_directory' ,@copy_job_name = N'$copy_job_name' ,@restore_job_name = N'$restore_job_name' ,@file_retention_period = 4320 ,@overwrite = 1 ,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT ,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT ,@secondary_id = @LS_Secondary__SecondaryId OUTPUT select [param] = '@copy_job_name', value = convert(varchar(500), '$copy_job_name' ) union all select '@restore_job_name', convert(varchar(500), '$restore_job_name' ) union all select '@LS_Secondary__SecondaryId', convert(varchar(500), @LS_Secondary__SecondaryId ) union all select '@LS_Add_RetCode', convert(varchar(500), @LS_Add_RetCode) IF (@@ERROR = 0 AND @LS_Add_RetCode = 0) BEGIN select [param]='@copy_schedule_name', value = convert(varchar(100), '$copy_schedule_name' ) DECLARE @LS_SecondaryCopyJobScheduleUID As uniqueidentifier DECLARE @LS_SecondaryCopyJobScheduleID AS int EXEC msdb.dbo.sp_add_schedule @schedule_name = N'$copy_schedule_name' ,@enabled = 1 ,@freq_type = 4 ,@freq_interval = 1 ,@freq_subday_type = 4 ,@freq_subday_interval = 15 ,@freq_recurrence_factor = 0 ,@active_start_date = $hoy ,@active_end_date = 99991231 ,@active_start_time = 700 ,@active_end_time = 235900 ,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT ,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT EXEC msdb.dbo.sp_attach_schedule @job_id = @LS_Secondary__CopyJobId ,@schedule_id = @LS_SecondaryCopyJobScheduleID DECLARE @LS_SecondaryRestoreJobScheduleUID As uniqueidentifier DECLARE @LS_SecondaryRestoreJobScheduleID AS int EXEC msdb.dbo.sp_add_schedule @schedule_name = '$restore_schedule_name' ,@enabled = 1 ,@freq_type = 4 ,@freq_interval = 1 ,@freq_subday_type = 4 ,@freq_subday_interval = 15 ,@freq_recurrence_factor = 0 ,@active_start_date = $hoy ,@active_end_date = 99991231 ,@active_start_time = 0 ,@active_end_time = 235900 ,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT ,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT select [param] = '@restore_schedule_name', value = convert( varchar(500), '$restore_schedule_name' ) EXEC msdb.dbo.sp_attach_schedule @job_id = @LS_Secondary__RestoreJobId ,@schedule_id = @LS_SecondaryRestoreJobScheduleID END DECLARE @LS_Add_RetCode2 As int IF (@@ERROR = 0 AND @LS_Add_RetCode = 0) BEGIN select [param]='@secondary_database', value=convert(varchar(500), '$secondary_database' ) EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database @secondary_database = N'$secondary_database' ,@primary_server = N'$primary_server' ,@primary_database = N'$primary_database' ,@restore_delay = 0 ,@restore_mode = 1 ,@disconnect_users = 1 ,@restore_threshold = 45 ,@threshold_alert_enabled = 1 ,@history_retention_period = 7200 ,@overwrite = 1 ,@ignoreremotemonitor = 1 select [param]='@LS_Add_RetCode2', value=convert(varchar(500), @LS_Add_RetCode2 ) END IF (@@error = 0 AND @LS_Add_RetCode = 0) BEGIN EXEC msdb.dbo.sp_update_job @job_id = @LS_Secondary__CopyJobId ,@enabled = 1 EXEC msdb.dbo.sp_update_job @job_id = @LS_Secondary__RestoreJobId ,@enabled = 1 END select [param]='Ret', value='0'; "@ if( $script.IsPresent ){ write-output $query }else{ invoke-sqlcmd ` -ServerInstance $secondary_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query ` -Verbose } out-shipMsg -flush } # =========================================== function add-shipLinkedServer{ param( [System.Management.Automation.PSCredential]$credential, [string]$primary_server, [string]$primary_database, [string]$primary_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_root, # [Parameter(Mandatory)] [string]$monitor_server, [System.Management.Automation.PSCredential]$monitor_credential, [string[]]$instance = ("primary", "secondary" ) ) $sqlparams = get-shipParams @PSBoundParameters $secondary_database = $script:secondary_database $monitor_server = $monitor_server.ToUpper() $query1 = @" $sqlparams if (db_name() != N'master') begin raiserror('Needs to run in master',16,1); return; end declare @server sysname = '$monitor_server' set @server = upper( @server ); select @server = N'LOGSHIPLINK_' + substring(@server, 1, 90) + N'_' + cast(checksum(@server) as nvarchar(20)); declare @name sysname = ( select top 1 [name] from sys.servers where [name] = @server ); if @name is not null begin select [param]='linkedserver', [value]=@name; return; end declare @res int exec @res = master.dbo.sp_addlinkedserver @server = @server, @srvproduct =N'SQL Server' --,@provstr=N'Encrypt=Yes;TrustServerCertificate=yes' ; if @res=0 begin exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@server ,@useself=N'True' -- ,@locallogin=NULL -- ,@rmtuser='{rmtuser}',@rmtpassword='{rmtpassword}' ; exec sp_setnetname @server = @server, @netname = N'MONITOR-SQL'; exec master.dbo.sp_serveroption @server=@server, @optname=N'rpc', @optvalue=N'true'; exec master.dbo.sp_serveroption @server=@server, @optname=N'rpc out', @optvalue=N'true'; exec master.dbo.sp_serveroption @server=@server, @optname=N'data access', @optvalue=N'true'; exec master.dbo.sp_serveroption @server=@server, @optname=N'use remote collation', @optvalue=N'true'; exec master.dbo.sp_serveroption @server=@server, @optname=N'remote proc transaction promotion', @optvalue=N'true'; exec master.dbo.sp_serveroption @server=@server, @optname=N'system', @optvalue=N'true'; select [param]='linkedserver', [value]=@server; end "@ if( $instance -contains "primary" ){ if( $monitor_server.ToUpper() -eq $primary_server.ToUpper() ){ $server1 = $primary_server }else{ $res1 = invoke-sqlcmd ` -ServerInstance $primary_server ` -Database master ` -Credential $Credential ` -TrustServerCertificate ` -Query $query1 ` -Verbose $server1 = get-ShipValue $res1 'linkedserver' } if( $server1 ){ out-shipMsg 'primary-linkedserver' $server1 }else{ out-shipMsg 'primary-linkedserver' "unable to create linked server in $primary_server " } } if( $instance -contains "secondary" ){ if( $monitor_server.ToUpper() -eq $secondary_server.ToUpper() ){ $server2 = $secondary_server }else{ $res2 = invoke-sqlcmd ` -ServerInstance $secondary_server ` -Database master ` -Credential $Credential ` -TrustServerCertificate ` -Query $query1 ` -Verbose $server2 = get-ShipValue $res2 'linkedserver' } if( $server2 ){ out-shipMsg 'secondary-linkedserver' $server2 }else{ out-shipMsg 'secondary-linkedserver' "unable to create linked server in $secondary_server " } } out-shipMsg -flush } function install-shipMonitorPrimary{ param( [System.Management.Automation.PSCredential]$credential, [string]$primary_server, [string]$primary_database, [string]$primary_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_root, # [Parameter(Mandatory)] [string]$monitor_server, [switch]$script ) # add linked server if it does not exists add-shipLinkedServer @params -monitor_server $monitor_server -instance "primary" $sqlparams = get-shipParams @PSBoundParameters $secondary_database = $script:secondary_database # pull primary db info $query1 = @" $sqlparams declare @primary_id uniqueidentifier; select top 1 @primary_id = primary_id FROM msdb.dbo.log_shipping_monitor_primary WHERE primary_database = '$primary_database'; select [param]='@primary_id', value=convert(varchar(500), @primary_id ); "@ $res = invoke-sqlcmd ` -ServerInstance $primary_server ` -Database master ` -Credential $Credential ` -TrustServerCertificate ` -Query $query1 ` -Verbose $primary_id = get-ShipValue $res '@primary_id' if( !$primary_id ){ throw "Error: Could not find [primary_id] in $primary_server for $primary_database ..." } # set the primary monitor $query2 = @" $sqlparams select [param]='primary_database', value=convert(varchar(500), '$primary_database' ) declare @primary_id uniqueidentifier = '$primary_id'; EXEC msdb.dbo.sp_processlogshippingmonitorprimary @mode = 1 ,@primary_id = @primary_id ,@primary_server = N'$primary_server' ,@monitor_server = N'$monitor_server' --Intended monitor server ,@monitor_server_security_mode = 1 ,@primary_database = N'$primary_database' ,@backup_threshold = 45 ,@threshold_alert = 14420 ,@threshold_alert_enabled = 1 ,@history_retention_period = 7200 select [param]='primary monitor', value='$monitor_server'; "@ invoke-sqlcmd ` -ServerInstance $monitor_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query2 ` -Verbose # update primary server with monitor info $query3 = @" $sqlparams declare @primary_id uniqueidentifier = '$primary_id'; UPDATE msdb.dbo.log_shipping_primary_databases SET monitor_server = '$monitor_server' ,user_specified_monitor = 1 WHERE primary_id = @primary_id; select [param]='$primary_server', value='monitor updated'; "@ invoke-sqlcmd ` -ServerInstance $primary_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query3 ` -Verbose out-shipMsg -flush # https://www.sqlservercentral.com/articles/adding-a-log-shipping-monitor # https://www.mssqltips.com/sqlservertip/2799/steps-to-add-log-shipping-monitor-into-an-existing-sql-server/ } function Install-shipMonitorSecondary{ param( [System.Management.Automation.PSCredential]$credential, [string]$primary_server, [string]$primary_database, [string]$primary_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_root, # [Parameter(Mandatory)] [string]$monitor_server, [switch]$script ) # add linked server if it does not exists add-shipLinkedServer @params -monitor_server $monitor_server -instance "secondary" $sqlparams = get-shipParams @PSBoundParameters $secondary_database = $script:secondary_database # pull secondary db info $query1 = @" $sqlparams declare @secondary_id uniqueidentifier select top 1 @secondary_id = secondary_id from msdb.dbo.log_shipping_monitor_secondary where primary_server=N'$primary_server' and primary_database = N'$primary_database' -- and secondary_server= N'$secondary_server' -- and secondary_database= N'$secondary_database' select [param]='@secondary_id', value=convert(varchar(500), @secondary_id ) "@ if( $script.IsPresent ){ write-output $query1 }else{ $res = invoke-sqlcmd ` -ServerInstance $secondary_server ` -Database master ` -Credential $Credential ` -TrustServerCertificate ` -Query $query1 ` -Verbose } # set secondary monitor if( $script.IsPresent ){ $secondary_id_src = '-- variable @secondary_id from previous script' }else{ $secondary_id = get-ShipValue $res '@secondary_id' # -echo $secondary_id_src = @" declare @secondary_id uniqueidentifier = N'$secondary_id'; "@ out-shipMsg 'secondary_id' $secondary_id } $query2 = @" $sqlparams $secondary_id_src select [param]='secondary_database', value=convert(varchar(500), '$secondary_database' ) EXEC msdb.dbo.sp_processlogshippingmonitorsecondary @mode = 1 ,@secondary_server = N'$secondary_server' ,@secondary_database = N'$secondary_database' ,@secondary_id = @secondary_id ,@primary_server = N'$primary_server' ,@primary_database = N'$primary_database' ,@restore_threshold = 45 ,@threshold_alert = 14421 ,@threshold_alert_enabled = 1 ,@history_retention_period = 5760 ,@monitor_server = N'$monitor_server' ,@monitor_server_security_mode = 1 --,@monitor_server_login = @monitor_server_login --,@monitor_server_password = @monitor_server_password --,@monitor_connection_options = N'Encrypt=Mandatory;TrustServerCertificate=Yes;' select [param]='secondary monitor', [value]='$monitor_server'; "@ if( $script.IsPresent ){ write-output $query2 }else{ if( $secondary_id ){ invoke-sqlcmd ` -ServerInstance $monitor_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query2 ` -Verbose }else{ throw "Error: Could not find [secondary_id] in $secondary_server for $primary_database ..." } } # update secondary with monitory info $query3 = @" $sqlparams declare @secondary_id uniqueidentifier = N'$secondary_id'; UPDATE msdb.dbo.log_shipping_secondary SET monitor_server = '$monitor_server' ,user_specified_monitor = 1 WHERE secondary_id = @secondary_id; select [param]='$secondary_server', [value]='monitor updated'; "@ if( $secondary_id ){ invoke-sqlcmd ` -ServerInstance $secondary_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query3 ` -Verbose } out-shipMsg -flush } # ======================================================================================= function remove-shipConfig{ [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns","")] [CmdletBinding(SupportsShouldProcess)] param( [System.Management.Automation.PSCredential]$credential, [string]$primary_server, [string]$primary_database, [string]$primary_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_root, # [switch]$backups, [switch]$logs, [switch]$reverse ) if( $reverse.IsPresent ){ $temp_server = $primary_server $primary_server = $secondary_server $secondary_server = $temp_server } $sqlparams = get-shipParams @PSBoundParameters $secondary_database = $script:secondary_database # on the primary server # ================================= $query = @" $sqlparams -- must run from master EXEC master.dbo.sp_delete_log_shipping_primary_secondary @primary_database = N'$primary_database' ,@secondary_server = N'$secondary_server' ,@secondary_database = N'$secondary_database' ; exec sp_delete_log_shipping_primary_database @database = N'$primary_database' ; select [param]='primary_server', [value]=@@servername "@ if( $PSCmdlet.ShouldProcess($query, 'delete') ){ invoke-sqlcmd ` -ServerInstance $primary_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query ` -Verbose } # on the secondary server # ================================= $query = @" $sqlparams EXEC master.dbo.sp_delete_log_shipping_secondary_database @secondary_database = N'$secondary_database' ; exec sp_delete_log_shipping_secondary_primary @primary_server = N'$primary_server' ,@primary_database = N'$primary_database' select [param]='secondary_server', [value]=@@servername "@ if( $PSCmdlet.ShouldProcess($query, 'delete') ){ invoke-sqlcmd ` -ServerInstance $secondary_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query ` -Verbose } # back at primary server (again) # ================================= $query = @" $sqlparams EXEC master.dbo.sp_delete_log_shipping_primary_database @database = N'$primary_database' ; select [param]='primary_server', [value]=@@servername "@ if( $PSCmdlet.ShouldProcess($query, 'delete') ){ invoke-sqlcmd ` -ServerInstance $primary_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query ` -Verbose } out-shipMsg -flush # remove jobs $jobs_params = @{} + $PSBoundParameters $jobs_params.remove('logs') | Out-Null $jobs_params.remove('backups') | Out-Null remove-shipJobs @jobs_params # remove backups if( $backups.IsPresent ){ $backups_params = @{} + $PSBoundParameters $backups_params.remove('logs') | Out-Null remove-shipFiles @backups_params -force } # remove shipping logs if( $logs.IsPresent ){ $logs_params = @{} + $PSBoundParameters $logs_params.remove('backups') | Out-Null remove-shipFiles @logs_params -force } out-shipMsg -flush } function remove-shipJobs{ [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns","")] [CmdletBinding(SupportsShouldProcess)] param( [System.Management.Automation.PSCredential]$credential, [string]$primary_server, [string]$primary_database, [string]$primary_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_root, # [switch]$reverse ) if( $reverse.IsPresent ){ $temp_server = $primary_server $primary_server = $secondary_server $secondary_server = $temp_server } get-shipParams @PSBoundParameters > null $secondary_database = $script:secondary_database # on the primary server # ================================= $query = @" select [param]='@primary_server', value='$primary_server' union all select [param]='@backup_job_name', value= N'$backup_job_name' ; if exists( select [name] from msdb.dbo.sysjobs where name = '$backup_job_name' ) exec msdb.dbo.sp_delete_job @job_name = N'$backup_job_name' ; else select [param]='$backup_job_name', value='Job not found'; "@ invoke-sqlcmd ` -ServerInstance $primary_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query ` -Verbose # on the secondary server # ================================= $query = @" select [param]='@secondary_server', value='$secondary_server' union all select [param]='@copy_job_name', value='$copy_job_name'; if exists( select [name] from msdb.dbo.sysjobs where name = '$copy_job_name' ) exec msdb.dbo.sp_delete_job @job_name = N'$copy_job_name' ; else select [param]='$copy_job_name', value='Job not found'; go select [param]='@restore_job_name', value= '$restore_job_name' ; if exists( select [name] from msdb.dbo.sysjobs where name = '$restore_job_name' ) exec msdb.dbo.sp_delete_job @job_name = N'$restore_job_name' ; else select [param]='$restore_job_name', value='Job not found'; "@ if( $PSCmdlet.ShouldProcess($query, 'remove') ){ invoke-sqlcmd ` -ServerInstance $secondary_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query ` -Verbose } out-shipMsg -flush } function remove-shipFiles{ [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns","")] [CmdletBinding(SupportsShouldProcess)] param( [System.Management.Automation.PSCredential]$credential, [string]$primary_server, [string]$primary_database, [string]$primary_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_root, [switch]$force, [switch]$backups, [switch]$logs ) get-shipParams @PSBoundParameters -nosql > null $secondary_database = $script:secondary_database $filter = ".donotfind" if( $backups.IsPresent ){ $filter = "*.bak" $switchx = '-backups' }elseif( $logs.IsPresent ){ $filter = "*.trn" $switchx = '-logs' }else{ throw "You must specify either -backups or -logs" } out-shipMsg "remove" $filter write-output ( Get-PSCallStack )[1].Command if( $logs.IsPresent -and ( Get-PSCallStack )[1].Command -ne 'remove-shipConfig' ){ throw 'to remove log files run remove-shipConfig @params -logs ' } if( $env:COMPUTERNAME.ToUpper() -eq $primary_server.ToUpper() ){ out-shipMsg 'primary_server' $primary_server $database_folder = join-AnyPath $primary_root $primary_database out-shipMsg 'database_folder' $database_folder $baks = (Get-ChildItem -Path $database_folder -Filter $filter -File ).FullName foreach( $bak in $baks ){ out-shipMsg 'removing' $bak if( $PSCmdlet.ShouldProcess($bak, 'remove') ){ if( $force.IsPresent ){ remove-item -path $bak -Force }else{ remove-item -path $bak -ErrorAction Continue } } } }else{ out-shipMsg 'primary_server' "run remove-shipFiles $switchx in $primary_server !!!" } if( $env:COMPUTERNAME.ToUpper() -eq $secondary_server.ToUpper() ){ out-shipMsg 'secondary_server' $secondary_server $database_folder = join-AnyPath $secondary_root $secondary_database out-shipMsg 'database_folder' $database_folder $baks = (Get-ChildItem -Path $database_folder -Filter $filter -File ).FullName foreach( $bak in $baks ){ out-shipMsg 'removing' $bak if( $PSCmdlet.ShouldProcess($bak, 'remove') ){ if( $force.IsPresent ){ remove-item -path $bak -Force }else{ remove-item -path $bak -ErrorAction Continue } } } }else{ out-shipMsg 'secondary_server' "run remove-shipFiles $switchx in $secondary_server !!!" } out-shipMsg -flush } function clear-shipHistory{ param( [System.Management.Automation.PSCredential]$credential, [string]$primary_server, [string]$primary_database, [string]$primary_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_root, [switch]$verbose, [switch]$reverse ) if( $reverse.IsPresent ){ $temp_server = $primary_server $primary_server = $secondary_server $secondary_server = $temp_server } $sqlparams = get-shipParams @PSBoundParameters $secondary_database = $script:secondary_database # on the primary server # ================================= $query = @" $sqlparams delete from msdb.[dbo].[log_shipping_monitor_primary] where primary_database = N'$primary_database' ; delete from msdb.[dbo].[log_shipping_primary_databases] where primary_database = N'$primary_database' ; delete from msdb.[dbo].[log_shipping_primary_secondaries] where secondary_database = N'$secondary_database' ; "@ invoke-sqlcmd ` -ServerInstance $primary_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query ` -Verbose # on the secondary server # ================================= $query = @" $sqlparams delete from msdb.[dbo].[log_shipping_monitor_secondary] where secondary_database = N'$secondary_database' ; delete from msdb.[dbo].[log_shipping_secondary] where primary_database = N'$primary_database' ; delete from msdb.[dbo].[log_shipping_secondary_databases] where secondary_database = N'$secondary_database' ; "@ invoke-sqlcmd ` -ServerInstance $secondary_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query ` -Verbose } # ======================================================================================= function add-shipAgentRight{ param( [string]$agent ) $rights = "SeServiceLogonRight", "SeAssignPrimaryTokenPrivilege", "SeChangeNotifyPrivilege", "SeIncreaseQuotaPrivilege", "SeBatchLogonRight" ( $rights ) | foreach-object{ set-UserRights -UserName $agent -UserRight $_ -AddRight } } function get-shipAgentRight{ param( [string]$agent ) get-userRights -UserName $agent } # ======================================================================================= function add-shipSecret{ [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSAvoidUsingConvertToSecureStringWithPlainText", "")] param( [string]$name, [pscredential]$credential, [byte[]]$key, [switch]$force, [string]$server = ".", [string]$database = "master" ) # create vault table $query=@" if not exists ( select [name] from sys.databases where [name]='$database' ) select [param]='database', [value]='not found'; else begin select [param]='database', [value]='dbaship'; if not exists( select [name] from sys.tables where object_id = object_id('dbo.dbaship_vault') ) begin create table dbo.dbaship_vault( valult_id int identity primary key, [name] varchar(500), [UserName] varchar(1000), [value] varchar(1000), last_updated datetime default ( getdate() ) ); revoke select, insert, update, delete on object::dbaship_vault from public; select [param]='table', value='dbaship_vault'; end end "@ $res = invoke-sqlcmd ` -ServerInstance $server ` -Database master ` -TrustServerCertificate ` -Query $query $db = get-shipValue $res 'database' # this is for database other than master if( $db -ne 'dbaship' ){ throw "Error: database not found..." out-shipMsg -flush } # verify if secret name already exists $cred1 = get-shipSecret $name -checkonly if( $cred1 -and !$force.IsPresent ){ throw "Error: secret already exists..." } # if key is not provided, get a randome one if( !$key ){ $key = get-shipKey } $keystr = "( $($key -join ',') )" # encrypt $secure = ConvertTo-SecureString -String $credential.UserName -AsPlainText $username = ConvertFrom-SecureString -SecureString $secure -Key $key $value = ConvertFrom-SecureString -SecureString $credential.Password -Key $key $query = @" merge into dbaship_vault tgt using ( select [name]='$name', [username]='$username', [value]='$value' )src on tgt.[name] = src.[name] when matched then update set [username] = src.[username], [value] = src.[value], last_updated = getdate() when not matched by target then insert( [name], [username], [value] ) values( src.[name], src.[username], src.[value] ); select [param]='$name', [value]='PSCredential' union all select [param]='`$key =', [value]='$keystr'; "@ invoke-sqlcmd ` -ServerInstance $server ` -Database $database ` -TrustServerCertificate ` -Query $query ` -Verbose out-shipMsg -flush } function get-shipSecret{ param( [string]$name, [byte[]]$key, [string]$server = ".", [switch]$checkonly, [string]$database = "master" ) $query = @" declare @username varchar(max), @value varchar(max); select @username = [username], @value = [value] from dbo.dbaship_vault where [name] = '$name'; select [param] = 'username', [value] = isnull(@username,'') union all select [param] = 'value', [value] = @value; "@ $res = invoke-sqlcmd ` -ServerInstance $server ` -Database $database ` -TrustServerCertificate ` -Query $query ` -Verbose # collect username $encrypted_name = get-shipValue $res 'username' # check if exists only if( $checkonly.IsPresent ){ if( $null -eq $encrypted_name -or $encrypted_name -eq "" -or !$encrypted_name ){ return $false }else{ return $true } } if( !$key ){ throw 'Error: invalid key...' } if( !(get-shipSecret $name -checkonly) ){ throw 'secret not found...' } # decript username and value $secure_username = ConvertTo-SecureString -String $encrypted_name -Key $key -ErrorAction SilentlyContinue if( $null -eq $secure_username ){ throw 'Error: invalid key...' } $username = ConvertFrom-SecureString -SecureString $secure_username -AsPlainText $encrypted_secure = get-shipValue $res 'value' $secure = ConvertTo-SecureString -String $encrypted_secure -Key $key # create credential from username and value [pscredential]$newCredential = New-Object System.Management.Automation.PSCredential( $username, $secure ) return $newCredential } function remove-shipSecret{ [CmdletBinding(SupportsShouldProcess)] param( [string]$name, [byte[]]$key, [string]$server = ".", [string]$database = "master" ) # verify if secret name already exists if( !(get-shipSecret $name -checkonly) ){ throw "Error: secret not found" } $cred2 = get-shipSecret $name $key if( !$cred2 ){ throw 'invalid key...' } $query = @" delete v from dbo.dbaship_vault v where [name] = '$name'; select [param]='rowcount', [value]=@@rowcount; "@ $res = invoke-sqlcmd ` -ServerInstance $server ` -Database $database ` -TrustServerCertificate ` -Query $query ` -Verbose $rows = get-shipValue $res 'rowcount' if( $rows -gt 0 ){ out-shipMsg $name 'deleted' }else{ #out-shipMsg $name write-output "Error: unable to delete $name" } out-shipMsg -flush } function get-shipKey{ # build a randon 256 (32-byte) array for encryption $newKey = New-Object Byte[] 32 [Security.Cryptography.RNGCryptoServiceProvider]::Create().GetBytes($newKey) return $newKey <# $bytes = New-Object byte[] 32 [System.Security.Cryptography.RandomNumberGenerator]::Create().GetBytes($bytes) $key = [BitConverter]::ToString($bytes).Replace("-", [string]::Empty) Write-Host "Generated AES Key: $key" #> } # Dot-source all .ps1 files in the Public directory $publicFolderPath = Join-Path -Path $PSScriptRoot -ChildPath 'private' if (Test-Path -Path $publicFolderPath) { Get-ChildItem -Path $publicFolderPath -Filter '*.ps1' | ForEach-Object { . $_.FullName } } # Export-ModuleMember -Function 'get-UserRights', 'set-UserRights' |