If you are working with Microsoft SQL Server, you may find yourself needing some sort of non-traditional backup. If that is the case you have come to the right place. Below we will detail how to backup SQL Server databases using a Powershell script. Note, this script can be extended to add email functionality by following our article Send Email With Powershell 2.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 | #################################################################### # SqlBackup.ps1 # Author: Matthew Marable # Updated: January 08, 2013 # Description: # Back up the SQL Server Databases #################################################################### $assemblylist = "Microsoft.SqlServer.ConnectionInfo", "Microsoft.SqlServer.Management.Sdk.Sfc", "Microsoft.SqlServer.SMO", "Microsoft.SqlServer.SMOExtended" foreach ($asm in $assemblylist) { [void][Reflection.Assembly]::LoadWithPartialName($asm) } # Set server connection info $conn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection" $conn.ServerInstance = "(local)" $conn.LoginSecure = $true # Set LoginSecure = $false and uncomment below to use sql auth # $conn.Login = "sql_user" # $conn.Password = "sql_pass" # Connect to server $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $conn # Set databases to backup $dbnames = @("PROD","DEVEL","EXPENSE") # Set backup location Set-Location "C:\Data\SQL Backup\" # Backup databases foreach ($db in $dbnames) { # Move old backup if (Test-Path "$db.bak") { Move-Item "$db.bak" "$db-OLD.bak" -force } $backup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup") $backup.Action = "Database" $backup.Database = $db $backup.Devices.AddDevice($db + ".bak", "File") # Starting backup process. $backup.SqlBackup($srv) } # Set script location Set-Location "C:\Admin Tools\" |