Backup a Database using PowerShell

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 –

  1. Above presented script takes 3 parameters as inputs – "ServerName", "DatabaseName" and "Backup Destination directory".
  2. Then we perform some sanity checks for the inputs.
  3. 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.
  4. 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

Powershell_Execution

Result in Folder

Powershell_results

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 –

image

You may also like...

  • Aljaz Turk

    This is a great example showing SQL Backups in Powershell and trapping errors. Best regards!

  • Rahul

    How about using new cmdlets like “Backup-Sqldatabase” to shorten the code ?

  • RamiVemula

    @Rahul – I am not aware of any of such cmdlet. Please provide me a link for more information.