One question I get asked from a lot of DBAs [database administrators] is how can SQL Diagnostic Manager help me monitor my backups. Alright so there are a few different ways to do that, and we will start right now. The easiest way really is to go to the databases tab for a particular instance and really see at a very easy glance which databases you may be backing up more regularly than others and which backups maybe you are not ever backing up. So one thing you can do to you knows what kind of automate this is to configure alerts for this particular instance. We are going to configure an alert called days since the last backup which happens to be at the very top. Now our default threshold is ten and thirty days. You can see I have modified that and you can modify that to however you want to. So what you can also do is configure backup thresholds for the particular databases, I have done here with my master database. So if I just go to edit that you can see, I am just generating a critical alert if I have not backed up my master database in one day. So you can make these as granular or as general as you want to. I would recommend you know adjusting this default threshold to at least a more reasonable value that satisfies your service level agreements with your organization. What we can also do is apply that that threshold to a template. So if you have, you know you know templates for different environments, like tests and QA [quality assurance], or whatnot different levels of backups that you need to abide by then you can set that for those alert templates. You can also configure that for a tag of instances as well or just a number of different instances that you select.

Another thing you can do here is if a backup has not been run on a particular database or a group of databases on any particular instances you can create an alert action response to run a job that performs those backups if you still have one. So I have an example, many examples I have created here. I will create one from scratch here we will just name it back up rerun. And then you have some conditions to select here in step one. I am just gonna make this simple and add where the SQL Server instance is in a specified list. I am just gonna select my SQL Server 2012 named instance. You can, of course, select more than one instance or select a tag of instances as well. And then I am going to select where the metric is in a specified list, so I am going to select my days since last backup metric, and that is right here. And I am going to change my severity levels to warning or informational. So I am just going to uncheck informational here and make this actually just run with a warning or critical. If I click OK, now I have some actions to select, and I am just going to run a SQL Server agent job. So I am going to select my 2012 named instance. I am going to browse from my job list, and I have some SQL Safe Backup jobs here. SQL Safe Backup is IDERA’s enterprise SQL Server backup and recovery solution, so if you want more information on that you can go to IDERA.com, and you can even download a fully functional trial that lasts 14 days. So I am just going to choose to run my full backup. Choose a step to start out at, and I only have one step it just runs the backup job here. So click OK, and now I am done. So to review on my SQL Server 2012 named instance, if my day since last backup alert goes into a warning or a critical state, I am going to run my SQL Safe Backup full backup job on my SQL Server 2012 named instance. So it is that easy to really create an alert action response in the case that databases are not being backed up on a regular basis or that particular databases are not being backed up as well.

I have already created that rule here, so I am just going to cancel out. Another thing you can do is run a prescriptive analysis. So, what is a prescriptive analysis? Prescriptive analysis allows SQL Diagnostic Manager to create recommendations based off of things that may have been overlooked. For example backups here. So if we right-click on a particular instance, my SQL Server 2012 named instance, in fact, all of these items will be unchecked out of the box when you install it. So you will need to you know just choose if you want to schedule this to run and you will definitely need as a minimum requirement select at least one category. And the one category that pertains to backups and you know whether your recovery models are set you know differently than they should be is in the disaster recovery. So if we go to advanced settings, I just want to you know highlight some of those recommendations that are related to disaster recovery. All right, so let me just scroll a little bit more. So here is one where the backups should be on separate volumes than your data and log files. I do that just because it is a demo environment and I like to trigger these recommendations, so that is definitely a big no-no in a production environment. So you will want to have a separate backup, or you know just separate backup file server established for that. Databases using these simple recovery models, so you lose that point in time recovery capabilities if you like you would get with a full recovery model. So if these databases are in a production environment, you will definitely want to consider changing that to a full recovery model. Outdated backups is a huge issue as well so that relates back to our day since last backup alert configuration. Also if the recent backup has been moved or deleted this could have implications for disaster recovery processes. Also for testing backups as well, if you have automated restore set up for your different environments, test dev, staging, development, whatever that may be, that may have some implications on development as well because they are not getting a fresh refresh of production data.

Alright if you are not backing up your transactions log and a certain number of days that will alert you to that. Again pointing to point in time recovery if you are not backing up your transaction log in a reasonable amount of time you are going to experience data loss. Ao if we look at my environment here under the analyze tab, here is where you will see those results of that tab. So if we go up to the top here, I have some backups on the same volume as my data files here. If we scroll here to the bottom, it is going to give me some lower priority items that I definitely need to look at here, those having to do with some of my databases being in a simple recovery model, having outdated backups here. And one thing that I will point out here is what is nice about prescriptive analysis is that it will give you a detailed explanation of the issue here, altitude backups. You know, that could hinder disaster recovery processes. And it gives you a recommendation on what to do just ensure that your backups are up-to-date, they are added to your backup jobs or whatever backup process that you follow here.

Another thing here is if you are in full recovery mode and you have not done a T log backup in a certain number of days. Wow, eighteen hundred ninety days that is a very long time. So maybe I need to look at that. Again gives me a recommendation on why I should back up my T [transaction] log. Obviously, for a point in time recovery, so you minimize your data loss there. And it also will give you an article here on either the Microsoft knowledge base or IDERA wiki site to give you some more detailed explanation of that as well. Alright so hopefully this session has been useful for you, and I appreciate your time. Have a great day.