Eu tenho dois servidores em um grupo de trabalho e preciso transferir meus backups do SQL Server 2008 da unidade local de um para uma unidade de rede em outro. Idealmente, eu gostaria de ter o SQL chamando o script Powershell depois de fazer o backup. Alguém tem um script que faz isso? Existe uma maneira melhor?
Eu tentei ter o SQL Backup diretamente na unidade de rede, mas parece que não consigo fazer isso sem que eles estejam no mesmo domínio.
O Powershell certamente pode fazer isso, mas acho o ROBOCOPY mais fácil, basta usar a opção / MIR para purgar automaticamente os mais antigos depois de criar um plano de limpeza de manutenção no SQL Server para se livrar de backups por mais de X dias e você sempre terá cópias na unidade netowrk;
Você pode configurar a cópia como uma tarefa agendada do Windows ou como uma tarefa do SQL Agent por meio de um comando do sistema operacional.
Eu escrevi este roteiro para mim mesmo. Ele cria backup direto em compartilhamento de rede com a minha própria formatação de nome de arquivo confortável. É executado a partir do agendador de janelas.
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
Eu uso o Powershell para invocar o RichCopy . Se você dividir seus arquivos grandes em vários fragmentos com um utilitário RAR, o RichCopy pode realmente transportar bits.
Outra abordagem é usar o módulo PowerShell v2 BITS, embora eu não tenha tentado ainda. Comece com "import-module filetransfer" e "get-help add-filetransfer –example"
Quanto ao SQL chamando o script, tente chamar tudo explicitamente:
% windir% \ system32 \ windowspowershell \ v1.0 \ powershell.exe -nologo -command "& {C: \ caminho \ para \ Script.ps1}"