Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I've got two servers on a workgroup and I need to transfer my SQL Server 2008 Backups from the local drive of one to a network drive on another. Ideally, I'd like to have SQL call the Powershell script after it does the backup. Anyone have a script that does this? Is there a better way?

I tried to have SQL Backup straight to the network drive but it doesn't seem I can do that without them being on the same domain.

share|improve this question

3 Answers 3

Powershell can certainly do that, but I find ROBOCOPY easier, just use the /MIR option to automatically purge older ones after creating a Maintenance cleanup plan on the SQL server to keep get rid of backups more than X days, and you'll always have copies on the netowrk drive;

You can set up the copy as a Windows Scheduled Task, or as a SQL Agent task via an Operating System command.

share|improve this answer

I wrote this script for myself. It creates backup right into network share with my own comfortable file name formatting. It executes from windows scheduler.

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
share|improve this answer

I use Powershell to invoke RichCopy. If you break your big files up into multiple chunks with a RAR utility, RichCopy can really haul bits.

Another approach is to use the PowerShell v2 BITS module, although I've not tried it yet. Start with "import-module filetransfer" and "get-help add-filetransfer –example"

As for SQL calling the script, try having it call everything explicitly:

%windir%\system32\windowspowershell\v1.0\powershell.exe -nologo -command "&{C:\path\to\Script.ps1}"

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.