Freigeben über

Backing Up All SQL Server Databases Using PowerShell

  • Artikel

One of the many great features in Microsoft SQL Server is the ability to perform a particular task numerous ways.  While watching Technet Webcast: Deploying PTC Windchill Using Microsoft SQL Server the speaker at approximately 31 minutes in discusses a bunch of different ways to backup a Microsoft SQL Server database:

  • Native SQL Server Tools
  • T-SQL Scripting
  • 3rd Party Backup Tools
  • PowerShell

The flexibility and ease of use on how Microsoft SQL Server allows you to perform even simple tasks like a full database backup makes it such a user friendly database platform.  In this post we're going to look at using PowerShell to back up all the databases on a SQL Server Instance.  The great thing about PowerShell is that more of your colleagues as a DBA (Network Administrators, Server Administrators, etc) are adopting PowerShell as their scripting language of choice so the ability ramp up and understand what both the left and right hand are doing is much easier then before.  PowerShell is integrated into WMI and .NET but more importantly (for the SQL Server DBA) PowerShell is integrated right into the SQL Server Management Studio.

SQL PowerShell (SQLPS.exe) is a minishell setup for SQL Server which allows you to access object models like SMO (Server Management Objects), PBM (Policy Based Management), DC (Data Collector), UCP (Utility Control Point), and others directly from the SQLPS console.  SQL PowerShell integrates directly with the SQL Server Management Studio and you can utilize the SQL Server Agent to execute PowerShell scripts as a scheduled task.  Below is a script to backup all the databases on a SQL Server instance using Powershell.

 $ServerName = "DEMOLAB1"
$BackupDirectory = "F:\Backups\"
$BackupDate = get-date -format yyyyMMdd_HHmmss 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$ServerSMO = new-object ("Microsoft.SqlServer.Management.Smo.Server") $ServerName 
$DatabaseList = $ServerSMO.Databases
foreach ($Database in $DatabaseList)
{
if($Database.Name -ne "tempdb") 
{
$DatabaseName = $Database.Name

$DatabaseBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")
$DatabaseBackup.Action = "Database"
$DatabaseBackup.Database = $DatabaseName
$DatabaseBackup.Devices.AddDevice($BackupDirectory + "\" + $DatabaseName + "_" + $BackupDate + ".BAK", "File")
$DatabaseBackup.SqlBackup($ServerSMO)
}
}

The more you work with PowerShell the more you'll want to.  The power (pun intended) of this scripting language is huge and if you're a DBA and you're still using VBScript to do what you can't do with T-SQL then in short you're doing it wrong but more importantly you're missing out on a great opportunity to utilize this amazing tool.

Comments

  • Anonymous
    February 10, 2013
    I keep getting the following error: Exception calling "SqlBackup" with "1" argument(s): "Backup failed for Server 'CCSVR02SQLEXPRESS'. " At H:SQLBK.ps1:26 char:26
  • $DatabaseBackup.SqlBackup <<<< ($ServerSMO)    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException    + FullyQualifiedErrorId : DotNetMethodException