Thursday, February 16, 2012

Backing Up SQL DBs Using PowerShell

My next project requires that I create several SQL Mirrors, and instead of backing up my databases with SQL, I thought I would try it in PowerShell.  The majority of the script is from Edwin Sarmiento's blog http://www.mssqltips.com/sqlservertip/1862/backup-sql-server-databases-with-a-windows-powershell-script/  (Excellent Post)
$bkdir = "\\serverName\Shared\Temp" # Set Backup Path! (optional "C:\Temp")

if ((test-path $bkdir) -eq $false ) # Verify folder else create it...
  {
     [IO.Directory]::CreateDirectory($bkdir) 
  }

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | out-null
$s = new-object ("Microsoft.SqlServer.Management.Smo.Server") $instance

$dbs = $s.Databases
foreach ($db in $dbs) 
  {
     if(($db.Name -ne "tempdb") -and ($db.Name -ne "master") -and ($db.Name -ne "model") -and ($db.Name -ne "msdb")) 
          {
               $dbname = $db.Name
               $dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")
               $dbBackup.Action = "Database"
               $dbBackup.Database = $dbname
               $dbBackup.Devices.AddDevice($bkdir + "\" + $dbname + ".bak", "File")
               $dbBackup.SqlBackup($s)
               write-host($db.name + " has been backed up.")
          }
  }
If you are saving to a network location, the SQL SA account and the person running the script need to have read/write permissions to the location.

Update: (03/05/2012)
Added save location verification else create folder.

2 comments:

  1. Great to hear that the PowerShell article was helpful to you.

    When you said SQL mirrors, are you referring to SQL Server database mirroring?

    ReplyDelete
  2. Yes, sorry for the slang... I was referring to SQL Server database mirroring.

    ReplyDelete