Subtitles of the Movie

In talking about data retention, I would like to talk about some of the tools that are included with SQL. Now, in this example, I am going to use a backup plan as part of data retention. I'm gonna choose to show you it has a backup plan because it's a compressed file and I'd like to introduce to you the Maintenance Plan within SQL that allows us other options rather than just a straight backup. Now, here's where your own policies and procedures come into play. Many of us use a third-party solution for our backups. When it comes to data retention, we typically have two different aspects that we're thinking in terms of. One is the backup for disaster recovery. One is our actual data storage for the life cycle of the database itself. Very often, those are in different places and what's stored within the copies is actually different information. Here is where you actually have to define your procedure. Your data retention by year so that it can be accessed readily in the case of perhaps litigation or government regulations or is your actual copy just one large database. It's all of the information from the beginning. So you can see that you really have to decide how you're going to be working with your data, what's the best solution in your actual situation. Let's introduce the Maintenance Plan inside of SQL though. First, I've just gone into SQL, under Management, there is the Maintenance Plan itself. I'm going to do a right click and simply go into the Maintenance Plan Wizard. The wizard will just give it to me in a user friendly format. I'll show you doing it without the wizard in a moment. All I have to do is choose the server I wanna go into, put in my authentication and then simply go Next. Now, notice the selections you get here. I can shrink the database to make sure it's taking up the least amount of space. I can make sure that I clean up any history. I can rebuild or reorganize my indexes. I can even check the integrity of the database itself. The idea here is that if we're using this for data retention, we wanted to be as clean as possible. Now, I can choose each of the tasks that I'd like. In this case, I'll go into the Full Back Up and simply go to the next screen. When I go in, it tells me the tasks and the order of the tasks in which they'll be executed, and then on the following screen, what I'm actually doing is choosing all of my databases or just one individual database. Once I choose the database, now you'll notice it gives the standard questions. Do you want it to be backed up to a Disk? Does it go into Tape? Do you want a backup file for every database if you chose more than one and what folder would you like them to be in? Is our extension going to be the default for backup? Do you wanna verify your integrity? These are simply the questions that you can ask now to go ahead and store your backup. Now, the nice part about using the Maintenance Plan is we can segregate each of the databases. Remember also, you can take the time to shrink your databases, rebuild your indexes. That's why we call it maintenance. Now, keep in mind that I went in to the actual wizard. If I go back to Maintenance Plan and just go into the new Maintenance Plan without choosing the wizard, say OK, I am back at the Maintenance Plan screen. It looks a little bit different though. In this case, I once again have the name. I can put in a description and I can schedule it. I can also choose my Connections and the Logging for the plan itself. Off to the lower left-hand side is where you'll notice the same tasks. Once again, I can go in and ask it to rebuild an index. I can do some cleanup. Notice also, it actually gives you the explanation of what you'll be choosing. Going further down, you'll notice that the last of the tasks is for your update. If I go into backup and choose the backup plan itself, once again you'll notice that it's going to ask me to go in and choose the backup. Here's the difference though. See how we have a little square that has appeared on the screen. It's going to automatically create a graphical representation of the steps we're choosing for this backup. The nice part is it actually creates your documentation for you automatically. Should I choose to put in a second task, you'll notice it also places that task. I can then choose the order in which they will be performed. If I double click on an individual box, here is where it's going to ask me for the specifics in what I'm actually backing up in the database itself. So the idea behind working outside of the wizard is we can create the steps but we also get the ability to put the diagram together for ourselves also. This gives us the actual representation of the steps that we're working on. Whichever methods that you use, the idea here is to really think through what you're doing. I chose to show you the data retention in the Maintenance Plan. It's also another viable solution for your backup, but of course, third party solutions are awesome also.