Microsoft SQL Server 2012 SP1 Feature Pack
Obtain the SharedManagementObjects MSI-file and the PowershellTools MSI file, I downloaded the x64 version, but if you have x86 version of SQL Server 2012, use that instead. In addition, there are equivalent packages for Microsoft SQL Server 2008 (R2), Google is your friend here.. Here is the script to perform a backup. I have created a function or cmdlet called Backup-Database. There is also a function called PreLoad-SmoAssemblies, which I have not used, but this can be used to PreLoad the entire SMO Library if you want an example of how to do this.
function PreLoad-SmoAssemblies(){ $smoAssemblies = "Microsoft.SqlServer.Management.Common", "Microsoft.SqlServer.Smo", "Microsoft.SqlServer.Dmf ", "Microsoft.SqlServer.Instapi ", "Microsoft.SqlServer.SqlWmiManagement ", "Microsoft.SqlServer.ConnectionInfo ", "Microsoft.SqlServer.SmoExtended ", "Microsoft.SqlServer.SqlTDiagM ", "Microsoft.SqlServer.SString ", "Microsoft.SqlServer.Management.RegisteredServers ", "Microsoft.SqlServer.Management.Sdk.Sfc ", "Microsoft.SqlServer.SqlEnum ", "Microsoft.SqlServer.RegSvrEnum ", "Microsoft.SqlServer.WmiEnum ", "Microsoft.SqlServer.ServiceBrokerEnum ", "Microsoft.SqlServer.ConnectionInfoExtended ", "Microsoft.SqlServer.Management.Collector ", "Microsoft.SqlServer.Management.CollectorEnum", "Microsoft.SqlServer.Management.Dac", "Microsoft.SqlServer.Management.DacEnum", "Microsoft.SqlServer.Management.Utility"; foreach ($assembly in $smoAssemblies){ [void][System.Reflection.Assembly]::LoadWithPartialName($assembly); } } function Backup-Database($dbinstance, $dbname, $saveToLocation = "C:\backups\") { [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null $dbserver = New-Object Microsoft.SqlServer.Management.Smo.Server $dbinstance $bkup = New-Object Microsoft.SqlServer.Management.Smo.Backup $bkup.Database = $dbname $date = Get-Date $date = $date -replace "\.", "-" $date = $date -replace ":", "-" $date = $date -replace " ", "-" $file = $saveToLocation + $dbname + "_" + $date + ".bak" $bkup.Devices.AddDevice($file, [Microsoft.SqlServer.Management.Smo.DeviceType]::File) $bkup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database $bkup.SqlBackup($dbinstance) Write-Host "Backup of database $database performed at $(Get-Date) to file location $saveToLocation" trap [Exception] { Write-Host $_.Exception.Message break } } $dbinstanceToUse = $env:COMPUTERNAME + "\SQLEXPRESS" $dbnameToUse = "TestDatabase" Backup-Database $dbinstanceToUse $dbnameToUseThe function or cmdlet Backup-Database takes three parameters. The $dbinstance is the computer name - db instance name to use for the $dbserver inside the function. The $dbname is the name of the database, while $saveToLocation is the location where to save the .bak backup file. Each .bak file will get a name of $dbname concatenated with a date stamp with the .bak extension in the end of the file name. The file location can be specified, but will default to c:\backups. Obviously, you want to either specify this or make sure that the location c:\backup exists first. The script can be adjusted by using Test-Path to make sure that the folder exists first.
This shows how Powershell can be used for a wide variety of tasks, such as performing a backup of a SQL server database.