Backing up the MS SQL (including Express) databases with Powershell script

In free edition of the Microsoft SQL Server - Express - you have no possibility to schedule backups. But you can set up backups with the help of Powershell scripts and Windows Task Scheduler.
Take a look at the script - that's my way to SQL backups. The script does not delete old backups, but please read my article Deleting old files and folders in Powershell, it will help. And also - Running Powershell script in scheduled task.
Please notice highlighted lines - there you will have to set your SQL-instance and path to backups.
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo'); [void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.Sdk.Sfc'); [void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO'); # Requiered for SQL Server 2008 (SMO 10.0). [void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended'); $Server = "server\instance"; # SQL Server Instance. $Dest = "F:\backup\"; # Backup path $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Server; # If missing set default backup directory. If ($Dest -eq "") { $Dest = $server.Settings.BackupDirectory + "\" }; Write-Output ("Started at: " + (Get-Date -format yyyy-MM-dd-HH:mm:ss)); # Full-backup for every database foreach ($db in $srv.Databases) { If($db.Name -ne "tempdb") # Non need to backup TempDB { $timestamp = Get-Date -format yyyy-MM-dd-HH-mm-ss; $backup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup"); $backup.Action = "Database"; $backup.Database = $db.Name; $backup.Devices.AddDevice($Dest + $db.Name + "_full_" + $timestamp + ".bak", "File"); $backup.BackupSetDescription = "Full backup of " + $db.Name + " " + $timestamp; $backup.Incremental = 0; # Starting full backup process. $backup.SqlBackup($srv); # For db with recovery mode <> simple: Log backup. If ($db.RecoveryModel -ne 3) { $timestamp = Get-Date -format yyyy-MM-dd-HH-mm-ss; $backup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup"); $backup.Action = "Log"; $backup.Database = $db.Name; $backup.Devices.AddDevice($Dest + $db.Name + "_log_" + $timestamp + ".trn", "File"); $backup.BackupSetDescription = "Log backup of " + $db.Name + " " + $timestamp; #Specify that the log must be truncated after the backup is complete. $backup.LogTruncation = "Truncate"; # Starting log backup process $backup.SqlBackup($srv); }; }; }; Write-Output ("Finished at: " + (Get-Date -format yyyy-MM-dd-HH:mm:ss));
script (en), powershell (en), sql (en)
- Hits: 7528