dbaship.psm1
|
<#
.SYNOPSIS Provides scripts to build a basic logshipping process get-help about_dbaship .PARAMETER version 0.0.8 .DESCRIPTION #> # ======================================================================================= # INTERNAL FUNCTIONS AND PREP # ======================================================================================= 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 set-shipParam{ [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns","")] [CmdletBinding(SupportsShouldProcess)] param( [string]$primary_server, [Parameter(Mandatory)] [string]$primary_database, [Parameter(Mandatory)] [string]$primary_root, [Parameter(Mandatory)] [string]$secondary_server, # defaults to primary_database [string]$secondary_database, [Parameter(Mandatory)] [string]$secondary_root, # [Parameter(Mandatory)] [string]$data_root, # defaults to $data_root [string]$log_root, # [string[]]$databases, # [string]$monitor_server ) # default if( [string]::IsNullOrEmpty($secondary_database) ){ $secondary_database = $primary_database } if( [string]::IsNullOrEmpty($log_root) ){ $log_root = $data_root } # store in environment $env:ship_primary_server = $primary_server $env:ship_primary_database = $primary_database $env:ship_primary_root = $primary_root $env:ship_secondary_server = $secondary_server $env:ship_secondary_database = $secondary_database $env:ship_secondary_root = $secondary_root $env:ship_data_root = $data_root $env:ship_log_root = $log_root $env:ship_databases = $databases # it will be stored as space separated strings $env:ship_monitor_server = $monitor_server if( $PSCmdlet.ShouldProcess($query, 'test') ){ test-shipParam } } function get-shipParam{ [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns","")] # For internal use of dbaShip module only param( [switch]$nosql ) # get from environment $script:primary_server = $env:ship_primary_server $script:primary_database = $env:ship_primary_database $script:primary_root = $env:ship_primary_root $script:secondary_server = $env:ship_secondary_server $script:secondary_database = $env:ship_secondary_database $script:secondary_root = $env:ship_secondary_root $script:data_root = $env:ship_data_root $script:log_root = $env:ship_log_root $script:databases = $env:ship_databases ? $env:ship_databases.split(' ') : $null $script:monitor_server = $env:ship_monitor_server $script:primary_server = $primary_server.ToUpper() $script:secondary_server = $secondary_server.ToUpper() $script:hoy = get-date -f "yyyyMMdd" $script:ts = get-date -f "yyyyMMddHHmm" $script:p_id = [System.Diagnostics.Process]::GetCurrentProcess().ID # 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($script:primary_root) ){ throw '-primary_root not found' } if( [string]::IsNullOrEmpty($script:secondary_root) ){ throw '-secondary_root not found' } # default if ( [string]::IsNullOrEmpty($script:secondary_database) ){ throw '-secondary_database not found' } $script:primary_host = $script:primary_server.contains('\') ? $script:primary_server.split('\')[0] : $script:primary_server $script:secondary_host = $script:secondary_server.contains('\') ? $script:secondary_server.split('\')[0] : $script:secondary_server $script:share_name = Split-Path $script:primary_root -Leaf $script:ship_backup_directory = join-anypath $script:primary_root $script:primary_database $script:ship_share = join-anypath '\' $script:primary_server $script:share_name $script:ship_destination_directory = join-anypath $script:secondary_root $script:primary_database $script:UserName = $credential.UserName $script:backup_job_name = 'LSBackup_'+$script:primary_database $script:backup_source_directory = $script:ship_share $script:copy_job_name = 'LSCopy_'+$script:primary_server.ToUpper()+'_'+$script:primary_database $script:backup_schedule_name = 'LSBackupSchedule_'+$script:primary_database $script:copy_schedule_name = 'LSCopySchedule_'+$script:primary_server.ToUpper()+'_'+$script:primary_database $script:restore_job_name = 'LSRestore_'+$script:primary_server.ToUpper()+'_'+$script:primary_database $script:restore_schedule_name = 'LSRestoreSchedule_'+$script:primary_server.ToUpper()+'_'+$script:primary_database; $script:primary_backup_name = $script:primary_database+'_'+$ts+'.bak' $script:primary_backup = join-anypath $script:primary_root $script:primary_database $script:primary_backup_name $script:server_root = $env:computername -eq $script:primary_host ? $script:primary_root : $env:computername -eq $script:secondary_host ? $script:secondary_root : $null $script:ship_log_folder = ( $null -eq $script:server_root ) ? $pwd.path : ( join-anypath ($script:server_root ) "dbaship" ) $script:ship_log = join-anypath $script:ship_log_folder "dbaship_$($hoy)_$($script:p_id).txt" $script:standby_folder = join-anypath $script:secondary_root "standby" $script:data_folder = join-anypath $script:data_root 'DATA' $script:log_folder = join-anypath $script:log_root 'Log' $script:login_file = join-anypath $script:ship_log_folder "login_$($script:primary_server)_$($script:primary_database)_$($ts).sql" 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-shipParam{ get-shipParam -nosql write-host "`$UserName $UserName " write-host "`$primary_server $primary_server " write-host "`$primary_database $primary_database " write-host "`$primary_root $primary_root " write-host "`$secondary_server $secondary_server " write-host "`$secondary_database $secondary_database " write-host "`$secondary_root $secondary_root " write-host "`$data_root $data_root " write-host "`$log_root $log_root " write-host "`$monitor_server $monitor_server " write-host " " write-host "`$hoy $hoy " write-host "`$ts $ts " write-host "`$p_id $p_id " write-host "`$primary_host $primary_host " write-host "`$secondary_host $secondary_host " write-host "`$share_name $share_name " write-host "`$ship_backup_directory $ship_backup_directory " write-host "`$ship_share $ship_share " write-host "`$ship_destination_directory $ship_destination_directory " write-host "`$backup_job_name $backup_job_name " write-host "`$backup_source_directory $backup_source_directory " write-host "`$copy_job_name $copy_job_name " write-host "`$backup_schedule_name $backup_schedule_name " write-host "`$copy_schedule_name $copy_schedule_name " write-host "`$restore_job_name $restore_job_name " write-host "`$restore_schedule_name $restore_schedule_name " write-host "`$primary_backup_name $primary_backup_name " write-host "`$primary_backup $primary_backup " write-host "`$server_root $server_root " write-host "`$ship_log_folder $ship_log_folder " write-host "`$ship_log $ship_log " write-host "`$standby_folder $standby_folder " write-host "`$data_folder $data_folder " write-host "`$log_folder $log_folder " write-host "`$login_file $login_file " out-shipMsg -flush } function test-shipParam{ [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns","")] param( [switch]$show ) get-shipParam -nosql # check if current server is primary/secondary/neither out-shipMsg 'servername' $env:computername if( $env:computername -eq $primary_host ){ out-shipMsg "server" "primary_server" }elseif( $env:computername -eq $secondary_host ){ out-shipMsg "server" "secondary_server" }else{ out-shipMsg "server" "This server is neither primary or secondary..." out-shipMsg "" "some commands can not be run from this copmuter" out-shipMsg "" "folders can not be checked from this computer" } # check/import sql module $sqlserver = ( get-module sqlserver ) if( $sqlserver ){ out-shipMsg 'sqlserver' 'module imported' }else{ out-shipMsg 'sqlserver' 'module not found...' } # check powershell version $version = $psversiontable.psversion.tostring() out-shipMsg "PSVersion" $version if( $psversiontable.psversion.major -lt 7 ){ out-shipMsg "Powershell" "Upgrade to 7+" } # check access to share if( (test-path -path $ship_share ) ){ out-shipMsg $ship_share 'Accessible' }else{ out-shipMsg $ship_share 'Is not accessible...' } # verify primary databases exists if( $databases.count -eq 0 ){ $databases += $primary_database } out-shipMsg "databases" $databases.count $querydb = "select [param] = [name], [value] = database_id from sys.databases" $resdb = invoke-sqlcmd ` -ServerInstance $primary_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $querydb ` -Verbose foreach( $db in $databases ){ $dbexists = get-shipValue $resdb $db if( $dbexists ){ out-shipMsg "db:$db" "OK" }else{ out-shipMsg "db:$db" "not found in $primary_server..." } } # check drives in primary server if( $env:computername -eq $primary_host ){ 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 drives in secondary server if( $env:computername -eq $secondary_host ){ out-shipMsg 'secondary_root' $secondary_root if ( !(test-path $secondary_root -PathType Container) ){ out-shipMsg 'secondary_root' 'not found...' } # 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...' } } # sysadmin role out-shipMsg 'UserName' $credential.UserName $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; "@ # verify sysadmin role in primary server $res1 = invoke-sqlcmd ` -ServerInstance $primary_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query ` -Verbose $admin1 = get-shipValue $res1 $credential.UserName #-echo if( $admin1 ){ out-shipMsg 'sysadmin' 'OK Primary' }else{ out-shipMsg $credential.UserName "Is not sysadmin in $primary_server..." } # verify sysadmin role in secondary server $res2 = invoke-sqlcmd ` -ServerInstance $secondary_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query ` -Verbose $admin2 = get-shipValue $res2 $credential.UserName #-echo if( $admin2 ){ out-shipMsg 'sysadmin' 'OK Secondary' }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..." } # test if powershell session is "run as admin" if( ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator) ){ out-shipMsg 'session' 'Administrator' }else{ out-shipMsg 'session' 'Warning: some commands may require to Run as Administrator !!!' } out-shipMsg -flush # show list of calculated parameters from basic ones if( $show.IsPresent ){ show-shipParam } } # ======================================================================================= # IN PRIMARY SERVER # ======================================================================================= function get-shipConfig{ get-shipParam -nosql $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-shipFolder{ #[System.Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns","")] param( [switch]$just_primary ) get-shipParam -nosql #if( !$PSBoundParameters.ContainsKey('databases') ){ if( !$databases -or $just_primary.IsPresent ){ $databases += $primary_database } out-shipMsg 'servername' $env:computername if( !($env:computername -eq $primary_host) -and !( $env:computername -eq $secondary_host ) ){ throw "This command can only be run in $primary_server or $secondary_server" } # primary if( $env:computername -eq $primary_host ){ out-shipMsg "server" "primary_server" # check if root exist, creates it out-shipMsg 'primary_root' $primary_root if ( !(test-path $primary_root -PathType Container) ){ new-item -Path $primary_root -ItemType Directory -Force | out-null } # create database folders for log shipping trn and bak foreach( $database in $databases ){ $path = join-path $primary_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 } #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..." } out-shipMsg 'Ret' '0' -flush } # secondary if( $env:computername -eq $secondary_host ){ out-shipMsg "server" "secondary_server" # check if root exist, creates it out-shipMsg 'secondary_root' $secondary_root if ( !(test-path $secondary_root -PathType Container) ){ new-item -Path $secondary_root -ItemType Directory -Force | out-null } # create database folders for log shipping trn and bak foreach( $database in $databases ){ $path = join-path $secondary_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 } # 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 'Ret' '0' -flush } } function set-shipPrimary{ [CmdletBinding(SupportsShouldProcess)] param( [switch]$run, [switch]$bypass ) $sqlparams = get-shipParam # check if this db has been backed up $query1 = @" -- SELECT -- [param]=DB_NAME(database_id), -- [value]=CASE WHEN last_log_backup_lsn IS NULL THEN 1 ELSE 2 END -- FROM sys.database_recovery_status select [param] = DB_NAME(database_id), [value] = count(*) FROM master.sys.databases d LEFT OUTER JOIN msdb.dbo.backupset b ON b.database_name = d.name where database_id = db_id('$primary_database') and b.backup_finish_date is not null "@ $res1 = invoke-sqlcmd ` -ServerInstance $primary_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query1 ` -Verbose $backup1 = get-ShipValue $res1 "$primary_database" if( !($backup1 -gt "0") -and !$bypass.IsPresent ){ out-shipMsg -flush throw "Need to take a FULL backup first! ($primary_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 } out-shipMsg "ret" "0" -flush if( $run.IsPresent ){ $query3 = @" $sqlparams select [param]='backup_job_name', value = convert(varchar(100), '$backup_job_name' ); EXEC msdb.dbo.sp_start_job @job_name = '$backup_job_name'; "@ invoke-sqlcmd ` -ServerInstance $primary_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query3 ` -Verbose out-shipMsg "ret" "0" -flush } } function backup-shipPrimary{ param( [switch]$bypass, [switch]$wait ) $sqlparams = get-shipParam $loop = $true while($loop){ # 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 -1 found; -- not found else select 1 found; -- found "@ $res = invoke-sqlcmd ` -ServerInstance $primary_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query1 ` -Verbose $res.found if( $wait.IsPresent ){ if( $res.found -eq 1 ){ $loop = $false }else{ $ts2 = get-date -f "yyyyMMddHHmm" # wait for the backup job to finish ( the logshipping backup job not this backup ) if( ($ts2 - $ts) -ge 3 ){ $loop = $false }else{ out-shipMsg "wait $ts2 " "for $backup_job_name " Start-Sleep -Seconds 60 } } }else{ if( $res.found -eq -1 -and !$bypass.IsPresent ){ 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 out-shipMsg "backup" "primary_backup" out-shipMsg 'Ret' '0' -flush } # ======================================================================================= # IN SECONDARY SERVER # ======================================================================================= function copy-shipBackup{ param( [string]$source ) get-shipParam -nosql if( $env:COMPUTERNAME.ToUpper() -ne $secondary_host.ToUpper() ){ throw "This script must be run in [$secondary_server]" } if( [string]::IsNullOrEmpty($source) ){ $source = $ship_share+"\"+$primary_database } $destin = $ship_destination_directory $cmd = { robocopy "$source" "$destin" "*.bak" /XO /ZB /R:3 /W:30 /MT:4 /TEE /LOG+:"$ship_log" } out-shipMsg "copy" "$destin" # Enable-PSRemoting -Force $res = Invoke-Command ` -ScriptBlock $cmd ` -ArgumentList $source,$destin,$ship_log if( ( $res | out-string ).contains('ERROR') ){ out-shipMsg "ret" "1" -flush $res throw "Error copying backup from $source to $destin" } out-shipMsg "ret" "0" -flush } function restore-shipSecondary{ param( [switch]$bypass, [switch]$local, [switch]$script ) get-shipParam -nosql > null if( !$bypass.IsPresent -and ( $env:COMPUTERNAME.ToUpper() -ne $secondary_host.ToUpper() ) ){ throw "This script must be run in [$secondary_server]" } # defaults if( $local.IsPresent -and $data_folder -eq '\DATA' ){ $data_folder = 'C:\MSSQL' } if( $data_folder -eq '\DATA' ){ Throw "You must provide -data_root or -local switch" } if( $log_folder -eq '\Log' ){ $log_folder = join-AnyPath ( split-path $data_folder -Parent ) 'Log' } # find most recent backup file $bak = (Get-ChildItem -Path $ship_destination_directory -Filter "*.bak" -File | Sort-Object LastWriteTime -Descending | Select-Object -First 1 ).FullName # check if any file found if(!$bak){ throw "not backup found for $primary_database in $ship_destination_directory..." } if(!$sctipt.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 'Restore' "$bak" out-shipMsg 'Ret' '0' -flush } function set-shipSecondary{ [CmdletBinding(SupportsShouldProcess)] param( [switch]$script ) $sqlparams = get-shipParam $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 [master] database. --------------------------------------------------------------------------------------- $sqlparams 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 'Ret' '0' -flush } # ======================================================================================= # INSTALL MONITORS # ======================================================================================= function add-shipLinkedServer{ param( [Parameter(Mandatory)] [string]$monitor_server, [System.Management.Automation.PSCredential]$monitor_credential, [string[]]$instance = ("primary", "secondary" ) ) $sqlparams = get-shipParam $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='$($monitor_credential.Username)',@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 'Ret' '0' -flush } function set-shipMonitorPrimary{ [CmdletBinding(SupportsShouldProcess)] param( [Parameter(Mandatory)] [string]$monitor_server ) # add linked server if it does not exists add-shipLinkedServer -monitor_server $monitor_server -instance "primary" $sqlparams = get-shipParam # 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' ,@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 set-shipMonitorSecondary{ [CmdletBinding(SupportsShouldProcess)] param( [Parameter(Mandatory)] [string]$monitor_server, [switch]$script ) # add linked server if it does not exists add-shipLinkedServer -monitor_server $monitor_server -instance "secondary" $sqlparams = get-shipParam # 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' $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 } # ======================================================================================= # install full process # ======================================================================================= function install-shipConfig{ param( [string]$primary_server, [Parameter(Mandatory)] [string]$primary_database, [Parameter(Mandatory)] [string]$primary_root, [Parameter(Mandatory)] [string]$secondary_server, # defaults to primary_database [string]$secondary_database, [Parameter(Mandatory)] [string]$secondary_root, # [Parameter(Mandatory)] [string]$data_root, # defaults to $data_root [string]$log_root, # [string[]]$databases, # [string]$monitor_server ) set-shipParam @PSBoundParameters $secondary_database = $script:secondary_database $log_root = $script:log_root if( !$databases ){ $databases += $primary_database } foreach( $database in $databases ){ if( $env:COMPUTERNAME.ToUpper() -eq $primary_host.ToUpper() ){ add-shipFolder -just_primary set-shipPrimary -run backup-shipPrimary -wait if( ![string]::IsNullOrEmpty($monitor_server) ){ set-shipMonitorPrimary } } if( $env:COMPUTERNAME.ToUpper() -eq $secondary_host.ToUpper() ){ add-shipFolder -just_primary copy-shipBackup restore-shipSecondary if( ![string]::IsNullOrEmpty($monitor_server) ){ set-shipMonitorSecondary } } } out-shipMsg -flush } function install-shipSecondary{ } # ======================================================================================= # CLEANING # ======================================================================================= function remove-shipConfig{ [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns","")] [CmdletBinding(SupportsShouldProcess)] param( [switch]$backups, [switch]$logs ) $sqlparams = get-shipParam # 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 remove-shipJob # remove backups if( $backups.IsPresent ){ remove-shipFiles -backups -force } # remove shipping logs if( $logs.IsPresent ){ remove-shipFiles -logs -force } out-shipMsg -flush } function remove-shipJob{ [CmdletBinding(SupportsShouldProcess)] param( [switch]$nada ) $nada = $null get-shipParam > null # 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( [switch]$force, [switch]$backups, [switch]$logs ) get-shipParam -nosql > null $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 -logs ' } if( $env:COMPUTERNAME.ToUpper() -eq $primary_host.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_host.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{ $sqlparams = get-shipParam # 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 } # ======================================================================================= # SECRETS # ======================================================================================= 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" #> } # ======================================================================================= # LOGINS # ======================================================================================= function copy-shipLogin{ 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[]]$as_is, [switch]$push ) # $params2 = @{} + $PSBoundParameters $params2.remove('as_is') | Out-Null $params2.remove('push') | Out-Null #$params2 get-shipParam -nosql # show-shipParams @params } # ======================================================================================= # PUBLIC # ======================================================================================= # 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' |