触发Powershell命令将SQL备份复制到networking驱动器

Powershell当然可以做到这一点，但我发现ROBOCOPY更容易，只需使用/ MIR选项在SQL服务器上创build一个维护清除计划后自动清除较旧的计划，以保持备份超过X天，在Netowrk驱动器上复制;

您可以将副本设置为Windows计划任务，或通过操作系统命令将其设置为SQL代理任务。

我为自己写了这个脚本。 它创build备份到我自己舒适的文件名格式的networking共享。 它从Windows调度程序执行。

function Backup-Database { [CmdletBinding()] param( $Server, [String[]]$Database, [String]$Path, [Switch]$CreateSubfolder, [Switch]$Check, [Switch]$CopyOnly, [Switch]$BackupDatabase = $true, [Switch]$BackupTransactionLog, [Switch]$NoInnerVerbose, [Switch]$Differential ) begin { $AllDatabases = @() } process { #All database names into 1 list $AllDatabases += $Database } end { $DefaultProgressPreference = $ProgressPreference if (-not $Path.EndsWith('\')) { $Path = $Path + '\' } #$Differential = $false $RetainDays = 0 if ($Differential) { $RetainDays = 31 } if ($NoInnerVerbose) { $Verbose = $false } else { $Verbose = ($VerbosePreference -eq 'Continue') } Import-Module SQLPS -Verbose:$false -WarningAction SilentlyContinue $Activity = "Backing up databases" # Some cmdlets cannot specify timeouts, this is workout Write-Verbose "Connecting to $Server" Write-Progress -Activity $Activity -CurrentOperation "Connecting to $Server" $ServerInstance = New-Object Microsoft.SqlServer.Management.Smo.Server $Server $ServerInstance.ConnectionContext.StatementTimeout = 0 for ($index = 0; $index -lt $AllDatabases.Count; $index++) { $TotalProgress = $index / $AllDatabases.Count * 100 $DB = $AllDatabases[$index] $DBTitle = [Char]::ToUpperInvariant($DB[0]) + $DB.Substring(1) $Operation = "Backing up $DBTitle database" #Write-Verbose $Operation if ($Check) { Write-Progress -Activity $Activity -CurrentOperation $Operation -Status 'Checking database integrity' -PercentComplete $TotalProgress Write-Verbose "Checking $DB database integrity" Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $DB -Query 'DBCC CHECKDB($(dbname))' -Variable "dbname='$DB'" -Verbose:$Verbose -QueryTimeout 65535 } if ($BackupTransactionLog) { Write-Progress -Activity $Activity -CurrentOperation $Operation -Status 'Backing up transaction log' -PercentComplete $TotalProgress $FileName = $Path if ($CreateSubfolder) { $FileName = Join-Path $FileName $DBTitle if (-not (Test-Path $FileName)) { Push-Location New-Item $FileName -ItemType Container -Force | Out-Null Pop-Location } } $FileName = Join-Path $FileName "$($DBTitle)_$(Get-Date -format 'yyyy-MM-dd_HH-mm')_log.trn" Write-Verbose "Backing up transaction log to $FileName" $ProgressPreference = 'SilentlyContinue' Backup-SqlDatabase -Database $DB -BackupFile $FileName -InputObject $ServerInstance -BackupAction Log -Checksum:$Check -CompressionOption On -CopyOnly:$CopyOnly -RetainDays $RetainDays -LogTruncationType Truncate -Verbose:$Verbose $ProgressPreference = $DefaultProgressPreference if ($Check) { Write-Progress -Activity $Activity -CurrentOperation $Operation -Status 'Checking transaction log backup integrity' -PercentComplete $TotalProgress $FileNumber = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query 'select position from msdb..backupset where database_name=$(dbname) and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=$(dbname))' -Variable "dbname='$DB'" -Verbose:$Verbose | select -ExpandProperty position $Position = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query 'RESTORE HEADERONLY FROM DISK=$(filename)' -Variable "filename='$FileName'" -Verbose:$Verbose | select -Last 1 -ExpandProperty Position if ($FileNumber -ne $null -and $FileNumber -eq $Position) { Write-Verbose "Verifying backup file '$FileName', position $FileNumber" Invoke-Sqlcmd -ServerInstance $ServerInstance -Query 'RESTORE VERIFYONLY FROM DISK=$(filename) WITH FILE=$(position)' -Variable "filename='$FileName'","position=$FileNumber" -QueryTimeout 65535 -Verbose:$Verbose } else { Write-Error "Transaction log verify failed. Backup information for database $DB not found or incorrect (query position $FileNumber, file position $Position)" -Category InvalidResult } } } if ($BackupDatabase) { Write-Progress -Activity $Activity -CurrentOperation $Operation -Status 'Backing up database' -PercentComplete $TotalProgress $FileName = $Path if ($CreateSubfolder) { $FileName = Join-Path $FileName $DBTitle if (-not (Test-Path $FileName)) { Push-Location New-Item $FileName -ItemType Container -Force | Out-Null Pop-Location } } if ($Differential) { $part = 'diff' } else { $part = 'full' } $FileName = Join-Path $FileName "$($DBTitle)_$(Get-Date -format 'yyyy-MM-dd_HH-mm')_$part.bak" Write-Verbose "Backing up $DB database to $FileName" $ProgressPreference = 'SilentlyContinue' Backup-SqlDatabase -Database $DB -BackupFile $FileName -InputObject $ServerInstance -BackupAction Database -Checksum:$Check -CompressionOption On -Incremental:$Differential -CopyOnly:$CopyOnly -RetainDays $RetainDays -Verbose:$Verbose $ProgressPreference = $DefaultProgressPreference #Backing up (Database: 'accounting' ; Server: 'Serv1C' ; Action = 'Database') . if ($Check) { Write-Progress -Activity $Activity -CurrentOperation $Operation -Status 'Checking database backup integrity' -PercentComplete $TotalProgress $FileNumber = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query 'select position from msdb..backupset where database_name=$(dbname) and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=$(dbname))' -Variable "dbname='$DB'" -Verbose:$Verbose | select -ExpandProperty position $Position = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query 'RESTORE HEADERONLY FROM DISK=$(filename)' -Variable "filename='$FileName'" -Verbose:$Verbose | select -Last 1 -ExpandProperty Position if ($FileNumber -ne $null -and $FileNumber -eq $Position) { Write-Verbose "Verifying backup file '$FileName', position $FileNumber" Invoke-Sqlcmd -ServerInstance $ServerInstance -Query 'RESTORE VERIFYONLY FROM DISK=$(filename) WITH FILE=$(position)' -Variable "filename='$FileName'","position=$FileNumber" -QueryTimeout 65535 -Verbose:$Verbose } else { Write-Error "Database backup verify failed. Backup information for database $DB not found or incorrect (query position $FileNumber, file position $Position)" -Category InvalidResult } } } } Write-Progress -Activity $Activity -Completed } } $Server = 'Serv1C' $Databases = 'accounting3','zup','accounting','pult','accounting_ip','accounting_u','accounting_u2' $Differential = $true $TransactionLog = $true $Check = $false $BackupPath = '\\MAIN\Backup\SQL' Backup-Database -Server $Server -Database $Databases -Path $BackupPath -CreateSubfolder -Verbose -NoInnerVerbose -Differential:$Differential -BackupTransactionLog:$TransactionLog -Check:$Check 

我使用Powershell来调用RichCopy 。 如果使用RAR实用程序将大文件分成多个块，RichCopy可能会占用很多位。

另一种方法是使用PowerShell v2 BITS模块，尽pipe我还没有尝试过。 从“import-module filetransfer”和“get-help add-filetransfer -example”开始

至于SQL调用脚本，请尝试让它明确地调用一切：

％windir％\ system32 \ windowspowershell \ v1.0 \ powershell.exe -nologo -command“＆{C：\ path \ to \ Script.ps1}”