SQL Server DBA Tutorial 75-How to Create Backup Maintenance Plan in SQL Server

country tech brothers in this video we're going to learn how to create backup maintenance plan in sequel server using management sequence every management studio so here is my server right here here's my sequel server tech Brothers and sequel server prod is sequel prod is my instance so in order to create a backup maintenance plan you need to click on management and go to the maintenance plan if you have already maintenance plan there would be a plus sign here and you can go ahead and view the maintenance plan since we don't have a maintenance plan plan we're going to go ahead and right click on the maintenance and click on new plan we need to name the plan so we're going to go ahead backup underscore all DB so this is our maintenance plan since this maintenance plan is going to be backing up all the databases including system databases and user databases so we're going to go ahead and click OK as you can see right here this is the maintenance plan and there's a sub plan up here we're going to go ahead and click on to box up here if it doesn't appear the toolbox doesn't appear you can click on View and click on toolbox right here and it will bring the toolbox right there so right now it's on hide auto hide so you don't have to click on auto hide and you can click on this little auto-hide button and then it will go ahead and stay on there so we're going to go ahead in this video we're going to do backup database tasks so we're going to go ahead and drag that to this space once you dragged the backup database tasks in this space right here as you can see right here the X button that means that this backup task is not configured so we created the maintenance plan and now we got the backup database tasks from the toolbox we're going to go ahead and configure this backup database task once it's configured you will see you you will see that this X bar is gone and it is converted to green that means it's ready to be used so we're going to go ahead and configure it in order to configure it you need to double click on the backup database task and it'll appear all the setting it'll show in this little window backup database task so connection up here is our local connection general information is that what kind of backup that you're going to create this plan for you have a full backup differential backup and log back up in this video we're going to do full backup so we're going to go ahead and click on that and up here we have a selection of the database now if you click on the selection you get following selection all databases only the system databases which is master model and ms DB and all other all user databases this will exclude master model ms DB and 10 DB but also you have an option if you wanted just specific databases you don't want all user databases you don't want system databases you don't want up here this option you can go ahead and select what databases that you wanted to back up in this maintenance plan so we're going to go ahead and click on all databases since this video is about backing up all your databases on a particular sequence server instance so we're going to go ahead and click on all databases keep in mind this is very important ignore databases what the state is not online if your database is offline and you have not clicked on this your maintenance plan is going to fail so keep in mind that if you have the backup up here the database offline any database that is often in your database list you need to click this option so that will go ahead and ignore and not create an error in your BAC backup maintenance plan so we're going to go ahead and click all databases I don't have any database that is offline so it doesn't really matter for me but I'm going to click it anyway we're going to go ahead and click OK now up here back up to we have two options in sequence over 2012 2014 there are two options to disk and URL URL involves your as your storage so we're not going to cover in this video we'll put another video to back up to a URL using as your account but right here you have a disk tape and URL we're going to in this video we're going to back back up all these databases on the disk so we're good with that click on destination up here if you wanted to create subdirectory of each database and once the subject directory is created all the databases will go and create a database directory such as master or all use use database and it will put in this following location you have an option right here if you wanted to put it somewhere else you can click on right here and change the path of your databases this is the path that where your database is going to be so if you wanted to create a backup for every database and create a subdirectory you can click that I'm not going to create a subdirectory I'm ok with the databases that you know just be in main folder not subdirectory sometimes it's a good option to create a subdirectory because it'll make it more manageable for anybody any DBA to do that so a backup extension is vak right here so let's look at the options right here options are just like a manually creating the database we have the compression if you wanted to use the sequel server default compression that's fine if you don't want compression you have an option not to select the compression again I wanted to quickly mention that if your database is compressed and from 100 gig to its backup is 40 gig that doesn't mean that it's going to take less time to restore it's going to take the same time as 100 gig database so you have option backup set will expire you have option in days and and and also the date copy only backup usually we in normal maintenance plan we don't do that this is some of the ad hoc if you wanted to do the copy only backup the verify backup integrity sometimes it's a good option to do it in production but keep in mind if you click on verify backup integrity it's going to take some time - in order to complete your backup task if you want to do do the backup encryption you can select that if you're sending your backup off-site somewhere to a vendor or even Microsoft you would like to encrypt your database backup so that when it goes over the network it won't be hacked so you have that option and we're ready to click OK and create a maintenance plans plan once this maintenance plan is created it is going to create a job for us in sequel server agent so we're going to go ahead and click OK and as you can see that little X button is gone up here some of the options that this maintenance plan is going to run as sequel server agent service account which is right here so you need to make sure the agent account the account that you're using you have an option to change the account right here if that account is changed or even the sequel server agent you need to make sure that that particular account has a write permission where you're storing your database backups if that's if that's not the case then you're going to run into issue of permission issue that it cannot write to the particular storage where you wanted to add your backups so we're going to go ahead and keep in mind it's very important to save all once you click on save that's when it's going to create sequel server agent job so we're going to go ahead and save all alright our maintenance plan is saved and in this maintenance plan we have configured full backup of all the databases on her sequel server instance so let's close this and take a look on sequel server agent as you can see right here is our backup and the score all job is created so you can go ahead and manually run this job you can also execute your maintenance plan if you refresh it we'll get an option right here you can execute your maintenance plan what back behind the scene it will do it'll kick off the job on sequence server agent so we're going to click on the jobs right here you can go ahead and start the job so basically this is how you create a full backup maintenance plan and go through some options I hope this video helps

Channel: TechBrothersIT

Views: 50,189

Rating: 4.8846154 out of 5

Keywords: Backup, Maintenance, Plan, SQL, Server, Developer, TSQL, DBA, Interview, Scripts, DBA Tips, SQL Developer, SQL Server Interview Questions, SQL Server 2014, SQL SErver Adavance Tutorial, SQL Server Tutorial for beginners, SQL Server 2014 DBA, SQL Server DBA tutorial for Beginners, SQL Data Adminstoration Tutorial Step by Step, Database Administrator (Job Title), TechBrothersIt, Programming

Id: jb2gsNinIb0

Length: 9min 8sec (548 seconds)

Published: Tue Mar 03 2015