Skip to main content

Notifications

Microsoft Dynamics RMS forum

Powershell Backup Script

Posted on by Microsoft Employee

Here is a script to automate RMS backup using Powershell.  Add the script as a scheduled task to run regularly.  If the backup fails an email will be sent out with the error messages

copy lines below

<#

   This Script will backup a MSSQL Database to two folders

   Set the number of backups by changing the $BackupNumber Variable

   For example if you set $backupNumber to 3, Three backup files will be

   created before the oldest gets overwritten,

   After the backup is created the latest file will be copied to the

   second folder.  The second folder may be on a share on the network

   or a portable device.

   If the backup fails, a e-mail message will be sent with error details.

   Written by Jozsef Gitta  04-15-2014

   Contact: jgitta@jgitta.com

   Tested with Powershell 4

#>

Try

{

   #import SQL Server module

   Import-Module SQLPS -DisableNameChecking

   $instanceName = "Localhost"

   $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

   #This sets the connection to mixed-mode authentication

   $server.ConnectionContext.LoginSecure=$false;

   #This sets the login name

   $server.ConnectionContext.set_Login("sa");

   #This sets the password

   $server.ConnectionContext.set_Password("yourpassword")

   #Number of copies to keep

   $BackupNumber = 4

   # Set Database Name

   $DatabaseName = "yourdatabasename"

   # Set Backup Folders

   $BackupFolder1 = "C:\Backup\"

   $BackupFolder2 = "C:\Temp\"

   $backupFile = "$($databasename)_1.bak"

   $FullBackupFile = Join-Path $BackupFolder1 $BackupFile

   #Delete the oldest files from both folders

   if (Test-Path "$($backupfolder1)$($databasename)_$($backupNumber).bak")  { Remove-Item "$($backupfolder1)$($databasename)_$($backupNumber).bak"  }

   if (Test-Path "$($backupfolder2)$($databasename)_$($backupNumber).bak")  { Remove-Item "$($backupfolder2)$($databasename)_$($backupNumber).bak"  }

   for ($a = $BackupNumber; $a -ge 1; $a--)

   {

       if (Test-Path "$($backupfolder1)$($databasename)_$($a-1).bak")  { Rename-Item "$($backupfolder1)$($databasename)_$($a-1).bak" "$($backupfolder1)$($databasename)_$($a).bak" }

       if (Test-Path "$($backupfolder2)$($databasename)_$($a-1).bak")  { Rename-Item "$($backupfolder2)$($databasename)_$($a-1).bak" "$($backupfolder2)$($databasename)_$($a).bak" }

   }

   Backup-SqlDatabase `

   -ServerInstance $instanceName `

   -Database $databasename `

   -BackupFile $fullBackupFile `

   -Checksum `

   -Initialize `

   -BackupSetName "$databasename Full Backup" `

   -CompressionOption Off

   Copy-Item "$($backupfolder1)$($databasename)_1.bak" "$($backupfolder2)$($databasename)_1.bak"

   }

Catch  # If backup fails send email with error message.

   {

       $ErrorMessage = $_.Exception.Message

       $FailedItem = $_.Exception.ItemName

       # Email Parameters

       # For Gmail use Authentication credentials and SSL

       $secpasswd = ConvertTo-SecureString "youremailpassword" -AsPlainText -Force

       $mycreds = New-Object System.Management.Automation.PSCredential ("your@emailaddress.com", $secpasswd)

       $param = @{

           To = 'your@emailaddress.com'

           From = 'your@fromemailaddress.com'

           Subject = "Database $($DatabaseName) Backup Failed"

           body = "Database $($DatabaseName) Backup Failed <br />Error Message: $($ErrorMessage).  <br />Failed Item: $($FailedItem)"

           SmtpServer = 'smtp.gmail.com'

           BodyAsHtml = $true

           Port = 587

           UseSsl = $true

           Credential  = $mycreds

       }

   Send-MailMessage @param

}

*This post is locked for comments

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,818 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,147 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans