A backup process can be really simple or very complex depending on your requirements. In this post I’m going to show how you can backup your sql server databases by using PowerShell. If you don’t want to use this approach you can of course use sql server agent and t-sql or something like that.

The backup script is executed in PowerShell like this:

\Backup.ps1 -serverName "WIN-1GA63L4PFK6\SQLEXPRESS" -backupDirectory "C:\Backup\Backups" -daysToStoreBackups 7 

If you need to schedule your backups you can use windows built in scheduler. For simplicity you could wrap the execution of the backup script within a bat-script. Just create a new scheduled task and set the action to execute the bat script:

Please note that you need to choose “run whether user is logged on or not” under the “general”-tab to allow the task to execute when you are not logged on at the server. Also, make sure that the user that is executing the script has permissions to read/write to your specified backup folders.


powershell C:\Backup\Tools\Backup.ps1 -serverName "WIN-1GA63L4PFK6\SQLEXPRESS" -backupDirectory "C:\Backup\Backups" -daysToStoreBackups 7 >> C:\Backup\Logs\%date%.log  

The last thing ( >> C:\Backup\Logs\%date%.log ) just pipes the script output to a file. You can ignore this part if you don’t care about logging.

The backup script is backing up all databases except the system database and writes the backup files to the local disk. After all backups are completed the script removes all backups older than the number of days you specified for the “daysToStoreBackups”-parameter.

In a production environment you would probably want to move your backups to a safe offsite repository too. This can either be as simple as copying the backups to a file share or much more complex depending on your infrastructure. More on this topic in a later post.

The backup script (Backup.ps1)


[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

$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $serverName
$dbs = $server.Databases
foreach ($database in $dbs | where { $_.IsSystemObject -eq $False })  
    $dbName = $database.Name

    $timestamp = Get-Date -format yyyy-MM-dd-HHmmss
    $targetPath = $backupDirectory + "\" + $dbName + "_" + $timestamp + ".bak"

    $smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
    $smoBackup.Action = "Database"
    $smoBackup.BackupSetDescription = "Full Backup of " + $dbName
    $smoBackup.BackupSetName = $dbName + " Backup"
    $smoBackup.Database = $dbName
    $smoBackup.MediaDescription = "Disk"
    $smoBackup.Devices.AddDevice($targetPath, "File")

    "backed up $dbName ($serverName) to $targetPath"

Get-ChildItem "$backupDirectory\*.bak" |? { $_.lastwritetime -le (Get-Date).AddDays(-$daysToStoreBackups)} |% {Remove-Item $_ -force }  
"removed all previous backups older than $daysToStoreBackups days"

And yes, you must allow PowerShell to execute remote scripts by:

PS C:\> Set-ExecutionPolicy RemoteSigned

Execution Policy Change  
The execution policy helps protect you from scripts that you do not trust. Changing the execution policy might expose  
you to the security risks described in the about_Execution_Policies help topic at Do you want to change the execution policy?  
[Y] Yes  [N] No  [S] Suspend  [?] Help (default is "Y"): y
comments powered by Disqus