dbaship.psm1
|
<#
.SYNOPSIS Provides basic scripts to build a logshipping process .DESCRIPTION This commands provides an easy way to install logshipping remove-module dbaship get-module dbaship import-module .\dbaship #> function get-shipHelp{ get-help -name .\dbaship.psm1 } function out-shipMsg{ <# .SYNOPSIS For internal use of dbaShip module only #> param( [string]$param, [string]$value, [switch]$flush ) if( !$script:show ){ $script:show = @() } if( $param ){ $script:show += [pscustomobject]@{ param = $param # ($param+(" "*50)).Substring(0,35) value = $value # ($value+(" "*100)).Substring(0,100) } } if( $flush.IsPresent ){ $show | format-table -AutoSize if( $script:show ){ $script:show = @() } } } 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 } <# .SYNOPSIS For internal use of dbaShip module only #> function get-shipParams{ [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns","")] param( [System.Management.Automation.PSCredential]$credential, [string]$primary_server, [string]$primary_database, [string]$primary_copy_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_paste_root, [switch]$nosql ) $script:ship_backup_directory = join-path $primary_copy_root $primary_database $script:share_name = Split-Path $primary_copy_root -Leaf $script:ship_share = join-path "\\$($primary_server)" $share_name $script:ship_destination_directory = join-path $secondary_paste_root $primary_database $script:UserName = $credential.UserName if ( [string]::IsNullOrEmpty($secondary_database) ){ $script:secondary_database2 = $primary_database }else{ $script:secondary_database2 = $secondary_database } # load sqlserver module if( !$nosql.IsPresent -and !(get-module sqlserver) ){ import-module sqlserver } $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:hoy = get-date -f "yyyyMMdd" $script:ts = get-date -f "yyyyMMddHHmm" $script:primary_backup = $primary_copy_root+'\'+$primary_database+'\'+$primary_database+'_'+$ts+'.bak' $script:ship_log = join-path ( $env:computername -eq $primary_server ? $primary_copy_root : $secondary_paste_root ) "dbaship_$($hoy).txt" if( $nosql.IsPresent ){ return; } $sqlparams = @" -- $ts if db_name()<>'master' use master; set nocount on; select [param]='@ts', value=convert(varchar(500),'$ts' ); "@ return $sqlparams } # ======================================================================================= <# .SYNOPSIS Adds the folder tree structure for all databases in the logshipping operation Must be run once in primary server and once in secondary server get-help get-shipFolders -detailed for -database parameter information get-help get-shipParams -detailed for information about all other parameters .PARAMETER database A csv of databases $databases = "db1","db2","db3" .EXAMPLE $databases = "db1","db2","db3" add-shipFolders @params -databases $databases get-help get-shipParams -detailed for information about @params #> function add-shipFolders{ [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns","")] param( [System.Management.Automation.PSCredential]$credential, [string]$primary_server, [string]$primary_database, [string]$primary_copy_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_paste_root, [string[]]$databases, [switch]$grant, [switch]$verbose ) get-shipParams @PSBoundParameters | out-null if( !$PSBoundParameters.ContainsKey('databases') ){ $databases += $primary_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" $copy_root = $primary_copy_root $server = $primary_server }else{ out-shipMsg "server" "secondary_server" $copy_root = $secondary_paste_root $server = $secondary_server } #check if root exist if ( !(test-path $copy_root -PathType Container) ){ out-shipMsg 'copy_root' $copy_root new-item -Path $copy_root -ItemType Directory -Force | out-null } # create database folders for log shipping trn and bak foreach( $database in $databases ){ $path = join-path $copy_root $database out-shipMsg $database $path # create folder new-item -Path $path -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..." } 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..." } } } out-shipMsg -flush } function Install-shipPrimary{ param( [System.Management.Automation.PSCredential]$credential, [string]$primary_server, [string]$primary_database, [string]$primary_copy_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_paste_root, [switch]$verbose, [switch]$script ) $sqlparams = get-shipParams @PSBoundParameters $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 = 4320 ,@backup_compression = 1 ,@monitor_server = N'$primary_monitor' ,@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;' ,@backup_threshold = 60 ,@threshold_alert_enabled = 1 ,@history_retention_period = 5760 ,@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_database2' ) -- 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_database2' ,@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_copy_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_paste_root, # [switch]$verbose, [switch]$script ) $sqlparams = get-shipParams @PSBoundParameters $query = @" $sqlparams BACKUP DATABASE [$primary_database] TO DISK = N'$primary_backup' WITH COPY_ONLY, FORMAT, INIT, -- MEDIADESCRIPTION = N'MediaSetDescription', -- MEDIANAME = N'MediaSetName', NAME = N'$primary_database-Full Database Backup copy_only', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 5 "@ if( $script.IsPresent ){ write-output $query }else{ invoke-sqlcmd ` -ServerInstance $primary_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query ` -Verbose } } function copy-shipBackup{ param( [System.Management.Automation.PSCredential]$credential, [string]$primary_server, [string]$primary_database, [string]$primary_copy_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_paste_root, # [string]$source, [switch]$verbose, [switch]$script ) get-shipParams @PSBoundParameters > null 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+:"$log" /NP } 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 } # -ComputerName $secondary_server `-Credential $credential ` } function restore-shipSecondary{ param( [System.Management.Automation.PSCredential]$credential, [string]$primary_server, [string]$primary_database, [string]$primary_copy_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_paste_root, # [string]$data_root, [string]$log_root, # [switch]$verbose, [switch]$script, [switch]$bypass ) get-shipParams @PSBoundParameters > null 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( !$PSBoundParameters.ContainsKey('data_root') ){ $data_root = "C:\MSSQL" } if( !$PSBoundParameters.ContainsKey('log_root') ){ $log_root = $data_root } $data_folder = join-path $data_root 'DATA' $log_folder = join-path $log_root 'Log' 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\$primary_database.mdf', MOVE N'$($primary_database)_log' TO N'$log_folder\$($primary_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_database2.mdf''' when f.type_desc='rows' and file_id > 1 and file_id<100 then '$($secondary_database2)_'+right('0'+convert(varchar(2),file_id),2)+'.ndf''' when f.type_desc='log' then '$($secondary_database2)_log.ldf''' else '$($secondary_database2)_'+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_database2] 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_copy_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_paste_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_database2' ) 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 ,@monitor_server = N'$secondary_monitor' -- ,@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;' ,@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 = 0 ,@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_database2' ) EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database @secondary_database = N'$secondary_database2' ,@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 = 5760 ,@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 } } function Install-shipSecondaryMonitor{ param( [System.Management.Automation.PSCredential]$credential, [string]$primary_server, [string]$primary_database, [string]$primary_copy_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_paste_root, # [switch]$verbose, [switch]$script ) $sqlparams = get-shipParams @PSBoundParameters $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_database2' 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 } 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'; "@ } $query2 = @" $sqlparams $secondary_id_src EXEC msdb.dbo.sp_processlogshippingmonitorsecondary @mode = 1 ,@secondary_server = N'$secondary_server' ,@secondary_database = N'$secondary_database2' ,@secondary_id = @secondary_id ,@primary_server = N'$primary_server' ,@primary_database = N'$primary_database' ,@restore_threshold = 45 ,@threshold_alert = 14420 ,@threshold_alert_enabled = 1 ,@history_retention_period = 14420 ,@monitor_server = N'$secondary_monitor' ,@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]='Ret', value='0'; "@ if( $script.IsPresent ){ write-output $query2 }else{ if( $secondary_id ){ invoke-sqlcmd ` -ServerInstance $primary_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query2 ` -Verbose #foreach( $row in $res2 ){ # write-output "$( ($row.param+(" "*50)).Substring(0,35) )$($row.value)" #} }else{ throw "Error: Could not find [secondary_id] in $secondary_server for $primary_database ..." } } } # ======================================================================================= function remove-shipObjects{ [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns","")] [CmdletBinding(SupportsShouldProcess)] param( [System.Management.Automation.PSCredential]$credential, [string]$primary_server, [string]$primary_database, [string]$primary_copy_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_paste_root, # [switch]$reverse ) if( $reverse.IsPresent ){ $temp_server = $primary_server $primary_server = $secondary_server $secondary_server = $temp_server } $sqlparams = get-shipParams @PSBoundParameters # 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_database2' ; exec sp_delete_log_shipping_primary_database @database = N'$primary_database' ; "@ 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_database2' ; exec sp_delete_log_shipping_secondary_primary @primary_server = N'$primary_server' ,@primary_database = N'$primary_database' "@ 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' ; "@ if( $PSCmdlet.ShouldProcess($query, 'delete') ){ invoke-sqlcmd ` -ServerInstance $primary_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query ` -Verbose } } function clear-shipHistory{ param( [System.Management.Automation.PSCredential]$credential, [string]$primary_server, [string]$primary_database, [string]$primary_copy_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_paste_root, [switch]$verbose, [switch]$reverse ) if( $reverse.IsPresent ){ $temp_server = $primary_server $primary_server = $secondary_server $secondary_server = $temp_server } $sqlparams = get-shipParams @PSBoundParameters # 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_database2' ; "@ 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_database2' ; 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_database2' ; "@ invoke-sqlcmd ` -ServerInstance $secondary_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query ` -Verbose } function remove-shipJobs{ [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns","")] [CmdletBinding(SupportsShouldProcess)] param( [System.Management.Automation.PSCredential]$credential, [string]$primary_server, [string]$primary_database, [string]$primary_copy_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_paste_root, # [switch]$reverse ) if( $reverse.IsPresent ){ $temp_server = $primary_server $primary_server = $secondary_server $secondary_server = $temp_server } get-shipParams @PSBoundParameters > 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' ; exec msdb.dbo.sp_delete_job @job_name = N'$backup_job_name' ; "@ 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'; exec msdb.dbo.sp_delete_job @job_name = N'$copy_job_name' ; go select [param]='@restore_job_name', value= '$restore_job_name' ; exec msdb.dbo.sp_delete_job @job_name = N'$restore_job_name' ; "@ if( $PSCmdlet.ShouldProcess($query, 'remove') ){ invoke-sqlcmd ` -ServerInstance $secondary_server ` -Database master ` -Credential $credential ` -TrustServerCertificate ` -Query $query ` -Verbose } } function remove-shipBackups{ [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns","")] [CmdletBinding(SupportsShouldProcess)] param( [System.Management.Automation.PSCredential]$credential, [string]$primary_server, [string]$primary_database, [string]$primary_copy_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_paste_root, [switch]$force ) get-shipParams @PSBoundParameters -nosql > null if( (!$force.IsPresent) -and $env:COMPUTERNAME.ToUpper() -ne $secondary_server.ToUpper() ){ throw "This script must be run in [$secondary_server]" } out-shipMsg '$secondary_paste_root' $secondary_paste_root $baks = (Get-ChildItem -Path $secondary_paste_root -Filter "*.bak" -Recurse -File ).FullName foreach( $bak in $baks ){ out-shipMsg 'removed' $bak if( $PSCmdlet.ShouldProcess($bak, 'remove') ){ if( $force.IsPresent ){ remove-item -path $bak -Force }else{ remove-item -path $bak -ErrorAction Continue } } } out-shipMsg -flush } function test-shipParams{ [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseSingularNouns","")] param( [System.Management.Automation.PSCredential]$credential, [string]$primary_server, [string]$primary_database, [string]$primary_copy_root, [string]$secondary_server, [string]$secondary_database, [string]$secondary_paste_root, # [string]$data_root, [string]$log_root, # [switch]$verbose, [switch]$force ) get-shipParams @PSBoundParameters -nosql > null out-shipMsg 'servername' $env:computername if( $env:computername -eq $primary_server ){ out-shipMsg "server" "primary_server" #$copy_root = $primary_copy_root $server = $primary_server }else{ out-shipMsg "server" "secondary_server" #$copy_root = $secondary_paste_root $server = $secondary_server } $sqlserver = ( get-module sqlserver ) if( $sqlserver ){ out-shipMsg 'sqlserver' 'OK' }else{ out-shipMsg 'sqlserver' '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_copy_root' $primary_copy_root if ( !(test-path $primary_copy_root -PathType Container) ){ out-shipMsg 'primary_copy_root' 'not found...' } # check share $shareExists = Get-SmbShare | Where-Object { $_.Path -eq $primary_copy_root } 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( $env:computername -eq $secondary_server ){ out-shipMsg 'secondary_paste_root' $secondary_paste_root if ( !(test-path $secondary_paste_root -PathType Container) ){ out-shipMsg 'secondary_paste_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 <# out-shipMsg "`$agent" $agent if( !$agent ){ throw "Error: Service account not found..." } #> out-shipMsg -flush } |