Of late I was working a lot in administering SharePoint enterprise products developed by my Dev. team. Often I landed up with lot of repetitive work back and froth, especially with databases. I stepped back and went to my SQL Basis, I explored various options to perform my daily routine Database tasks but found PowerShell as most flexible and easier way to automate various tedious/time consuming tasks in a fairly simple way.
I got started with very basic Database operations – Backup, Restore, Create, Remove etc., and I am continuing at this point of time in exploring different Cmdlets offered by PowerShell and in fact now I am a huge fan for the same.
In the present post, I want to share a simple script which backs up a Database along with its transaction log. In future I am going to share all the related posts – Restore, Multiple Databases Backup, Create Multiple Databases etc., first lets get into the some serious PowerShell code.
#Loading required assemblies
[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');
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended');
#Setting up arguments to variables
$Server = $args[0];
$Database = $args[1];
$Dest = $args[2];
#Handle Errors (if in case)
Trap
{
$err = $_.Exception;
while ( $err.InnerException )
{
$err = $err.InnerException;
write-output ("Oops!!! There is a Problem -> " + $err.Message);
}
return;
}
#If arguments are insufficient
if($args.count -ne 3)
{
write-output ("Usage:");
write-output ('Syntax : "ServerName" "Dbname" "Destination dir"');
write-output ('Example : "Rami-Server" "SampleDB" "C:\DatabaseBackups\"');
return;
};
#Setting up servername
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Server;
#Check for Database name existance
$found = $false;
foreach ($db in $srv.Databases)
{
if ($db.Name -eq $Database)
{
$found = $true;
}
}
if(!$found)
{
write-output ("Invalid Database name");
return;
}
#Setting backup path when destination directory is not specified
$defaultPath = $srv.Settings.BackupDirectory + "\";
If ($Dest -eq "")
{
$Dest = $defaultPath;
Write-Output ("Destination folder defaulted to : " + $srv.Settings.BackupDirectory);
}
#check for path existance
if(!(Test-Path $Dest))
{
$Dest = $defaultPath;
Write-Output ("Destination folder doesn't exist, so path defaulted to : " + $srv.Settings.BackupDirectory);
}
Write-Output ("BackUp Started at: " + (Get-Date -format yyyy-MM-dd-HH:mm:ss));
#Setting up database
$db = $srv.Databases.Item($Database);
# Set BackUp Properties
$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 ("BackUp Finished at: " + (Get-Date -format yyyy-MM-dd-HH:mm:ss));
Code was self explanatory, but I would like to narrate the common steps which I took in constructing this code –
- Above presented script takes 3 parameters as inputs – "ServerName", "DatabaseName" and "Backup Destination directory".
- Then we perform some sanity checks for the inputs.
- Finally we perform Database backup using Microsoft.SqlServer.Management.Smo.Backup (SQL Server Management Object – collection of objects that are designed for programming all aspects of managing Microsoft SQL Server), and then corresponding Transaction log backup.
- In case of any error in the complete flow, there is Trap block which catches the exception and notifies PowerShell Console.
Following Screenshots shows you the execution results and backup files –
Execution in Console
Result in Folder
GOOD TO HAVE – Using PowerShell Console to execute scripts works, but Windows PowerShell Integrated Scripting Environment (ISE) would be more apt for developing/debugging PowerShell Scripts. Check below image –
This is a great example showing SQL Backups in Powershell and trapping errors. Best regards!