Powershell Script to Backup all SQL Server Databases on a Server

Tags: SQL Server, PowerShell

I came across a great little script this morning that backed up all of the user databases on a server, in responce to that I said that I had a PoswerShell script that did the same... and here it is for those that are interested.

Direct link to Post: TSQL Script to Backup all user Databases with time Stamped backup file.

$BackupPath = "C:\SQLServer\Backups"
$ServerName = "Localhost"
$DatabaseServer = "localhost\Developer"

# little helper function to ping the remote server.
function Get-ServerOnline ([string] $server)
{
    $serverStatus = (new-object System.Net.NetworkInformation.Ping).Send($server).Status -eq "Success"
    $dt = get-date -format yyyyMMddHHmmss
    Write-Host "$dt - Server Online: $serverStatus"
    return $serverStatus
}

# Load the SQL Server SMO Libraries
$assemblyInfo = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
if ($assemblyInfo.GetName().Version.Major -ge 10)
{
    # sql server version is 2008 or later, also load these other assemblies
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | Out-Null
}

$dt = get-date -format yyyyMMddHHmmss
Write-Host "$dt - Connecting to Database Server $ServerName"

if (get-serveronline($ServerName))
{
    $s = new-object (“Microsoft.SqlServer.Management.Smo.Server”) $DatabaseServer

    $dbs = $s.Databases

    foreach ($db in $dbs)
    {
        if($db.Name -ne “tempdb”) # We don’t want to backup the tempdb database
        {       
            $DatabaseName = $db.Name
           
            if (!(Test-Path -path $BackupPath\$DatabaseName\))
            {
                $dt = get-date -format yyyyMMddHHmmss
                Write-Host "$dt - Creating the Backup Directory for $DatabaseName"
                New-Item $BackupPath\$DatabaseName\ -type directory | Out-Null
            }
           
            $dt = get-date -format yyyyMMddHHmmss
            Write-Host "$dt - Starting the Backup of the Database $DatabaseName"
           
            $dt = get-date -format yyyyMMddHHmm # We use this to make a file name based on the timestamp
            $dbBackup = new-object (“Microsoft.SqlServer.Management.Smo.Backup”)
            $dbBackup.Action = “Database”
            $dbBackup.Database = $DatabaseName
            $dbBackup.Devices.AddDevice($BackupPath +"\" + $DatabaseName + “\” + $DatabaseName + “_Database_” + $dt + “.bak”, “File”)
            $dbBackup.SqlBackup($s)
        }
    }
   
    $dt = get-date -format yyyyMMddHHmmss
    Write-Host "$dt - Database Backup Complete"
}

3 Comments

Add a Comment