Why? Why? Why?
I was in one week holidays, guessed what happened when I came back to work. I found out that our backup disk was full, so SCCM refuse to do site backup, SQL refuse to do SQL full backup. Although I should have SCCM alert if site backup failed, but I do not know why I haven’t received a single email from SCCM monitoring alert. So I decided make SQL backup notification alert.
I believe there are many ways how you setup the SQL Alert, I am not specialist of SQL, so I am just telling how I did it, may not be the best, but it worked. 🙂
- Setup database mail. There is already some other post related to that. Example Pinal Dave wrote an article how to make it in SQL Server 2008, it is same in SQL Server 2012 or 2014. Of course, you should put your own information on those step, like smtp server name. I only use Step 1) Create Profile and Account from the article, and sent test email to myself, confirmed it works.
SQL SERVER – 2008 – Configure Database Mail – Send Email From SQL Database
- How ever, I made small chages in the “manage Profile Security” step. I put the Profile as Public, and I choose “Default Profile”–> Yes.
- Choose “SQL Serve Agent”, right-click “Operators”–>”New Operator…”
Name: Send Email to myself
E-mail name: firstname.lastname@example.org (put the email address of the person who actually reads emails)
Why do we need site backup, and also need SQL Full back..because double backup for safety reason? 🙂 Site backup only backup the site database, that’s why it call “site backup”. If you use custom reports, or if you have any other database on the same server, you should run a SQL full backup, and of course do also the site database SQL full backup.
Again, we use google, search key word “SCCM sql full backup”, you will find probably the first link written by Steve Thompson [MVP] SQL Server Backup Recommendations for Configuration Manager , just remember select all the database you needed, site database, ReportServer database, ReportServer temp database, MDT database.
- Select Report Options Step, I choose “E-mail report”, and choose your Email Operator from the drop-down lista, the one your created from my earlier post in Step 3
If you use reporting server, I would also suggest change the ReportServer Database recovery mode –> “Simple“, before you do the backup maintanece plan.
After I create the Backup Maintanace plan, I add two more step in it.
- Open your Backup Maintanace plan, choose Toolbox from the right panel, add two “Notify Operator Task” in your maintanace plan
- one is for susseced notification, another one is for failed notification
You must remember backup the ReportServer database encryption key if you are using SCCM reports.
- Open Reporting Services Configuration Manager.
- From the left panel, choose “Encryption Keys”
- From the right panel, choose “Backup”
- Then choose where do you want to save the file.
- Put a password there, then save the file.
- Forget the password. (Just joking, hope you remember the password later.)