SQL Server DBA and TSQL Training - 90 Hours (By Daniel AG - US based DBA)

you are very prompt in prompting me I appreciate that why don't you come and attend my next patch okay I'm looking for somebody like you you know all right thank you very much all right that recording has been started okay so my first question is why or what is a back backup and what is a restore process in any our DBMS backing government you are basically saving a copy of it and then restore is if something goes wrong you're making the save I mean available through the safe to copy like you know okay but why we want to do this why we want to create a premise like a copy of the database and then save it somewhere in secure location and then if needed you can replace it from that saved copy now the question is why do you want to do that I know like person any kind of disaster if something happens to database is corrupted or you know somebody accidentally dropped something to recover the database may be like power failure so I like the word I like that like the fact that you use the word disaster so basically backup and be normally backup the databases to to in to make sure that we can recover the database in case there is a disaster so what are the different different disaster scenarios you can think about we'll be unitary Yomi maybe I you sir Aurore okay you sir network failure mmm this corruption this corruption okay I'll hardware hardware failure we can simply categorize generalize it as hardware failure so normally the disaster can be categorized into two one hardware failure and logical errors what is logical logic error is nothing but user errors accidentally if you delete a order record from a table that is also a disaster so when a disaster happens most of the time almost all the companies or the employers are in u.s. after Sept especially after September 11th has a well documented disaster recovery plan I hope all you I mean the company you are working with also have a will tester disaster recovery plan so backup and restore is one of the major component in the disaster recovery plan please mute your microphone if you are not speaking appreciate that okay so back up and restore is nothing but a a prominent member in your disaster recovery plan as one of the student explained backup is nothing but a process which will create a copy of your database not an exact copy but it's you know it pretend like it you are creating a copy of the database and then keep it then you need to keep it in a secured location or is when Isis secure location I'm I'm talking about as lustily location which is available to you then even when a disaster happens okay and then yeah when the disaster happens then you need to recover the database from the backup created in that secured location so this is what all plan of backup and restore so this is a very generalized situation I just explained but every when each company's situation is different so we cannot have a generalized disaster recovery plan for our database the disaster recovery plan is completely or for your databases completely depends on what your requirement is that is one of the most important dimension you need to consider when you when you when you are designing your disaster recovery plan what you are trying to achieve in that dimension one of the key attribute in that dimension is how much data you can lose if there is a disaster happens there is a very good chance that there is some data loss I mean that is a well as accepted fact if you cannot afford a data loss then insert disaster recovery solution you need to go for what anybody if you if based on your business your business cannot even lose a single byte of data in case there is a disaster then instead of a disaster recovery plan what you will go of it not doing or maybe Daniel yes nothing so open clustering is one way to implement that so through clustering what you are implementing right high availability solution so a high available so disaster recovery and high availability solution are two different things this asset recovery solution tells what are the steps necessary step should be taken when there is a disaster so that you can recover the data you lost okay so the core the database you lost but know better how well-planned it is thus the recovery most of the time will will face um in some details now the question is how much data you can lose well that is a that is a decision come from from the business side of your company please meet a microphone again if you're not speaking so based on that decision how much data you can lose you can plan or customize this backup and restore operation okay so so that is a general general interaction to a backup and restore scenario the backups there are so many different type of backups available in sequel server one is the most well the different the most prominent one is the full backup then we have differential backup after that we have transaction log backups or full backup differential backup and transaction log backup are the three main backups available in sequel so in addition to this there are three more backups one is partial backup thus the the next one is file group backup and then we have copy early backup so there are six different type of backups available in sequence over and we will go through one by one but the major one is a full backup differential backup and transaction log backup similarly if you look into the restore process there are a couple of different ways you can restore it one is the normal restore the second one is piecemeal restore then page restore file group restore and then we will also have something called database snapshots so there are a couple of other ways you can restore the database so let's get started with the backup operation so to explain the backup I mean even before we actually see the steps needed to backup the database let me show you a scenario here so you have a database and your backup plan is in such a way that every day 1:00 a.m. in the morning you will go for a you will create a full backup I will explain what a full backup is in a minute and then at 10:43 a.m. database crashed okay so 1:00 a.m. every day 1:00 a.m. in the morning you create a every day at work every day 1:00 a.m. in the morning you create a full backup of your database ok and then assume that today at 10:43 a.m. your database crashed what steps you will take when you see that the database is patched you will try to find out verb the backup created at 1:00 a.m. in the morning and then you will restore that to the database so that you can recover the database or with me so far no but what is the drawback of this if you restore the backup created at 1:00 a.m. in the morning what is the drawback with that well whatever data changes happen between 1:00 a.m. and 10:43 a.m. is gone am i correct you know maybe you really don't I'm that action log well we don't have a transaction log we only have our backup the backup strategy is create a full backup every day at 1:00 a.m. we are not creating any other backups so whatever changes happen between because when you restore a database backup created at 1:00 a.m. and later if you use that backup to restore the database what you are actually doing you are making the database to a 1:00 a.m. state so what all the data changes happen in between will be deleted I mean not deleted it's gone it's not about deleting but what when you create a full backup there is a bak file get created and that bak file contains all MDF file nd F file and LD F files and the extension of the bak file is dot bak but if you cannot lose that kind of data based on your company policy you cannot come in here you are losing pretty much like nine hours and forty three hours worth of data and if you cannot lose that kind of information based on your company policy well you need to customize you need to add more steps into this backup process the next step you can implement is something called differential backup you can set up a differential backup every 30 minutes so you after the full backup every 30 minutes what you are doing every 30 minutes you are doing a differential backup so what I can say is 1:30 a.m. there is a differential backup right 2:00 a.m. there is a differential backup keep on going like that so when is the last differential backup will be created here 10:30 so there are no what's that now because in this particular day the last differential backup created he said 10:30 a.m. in the morning then 10:43 the database crashed are you with me so far yes what is the in the differential backup in the full backup the bak file contains the backup file or the file with the bak extension contains all the MDF and EF and L LD f files but what is there in the differential backup when you create a differential backup still the file extension is going to be bak and what is there in the VHF in that particular bak file it's the difference between the 1 a.m. back up and then the 10:30 back I mean if it's 1:30 means the atom is that correct so when you good yes good ok so when you create a full backup you are creating an exact copy of your database right that's a full that's why we call it as full back but when you create a differential backup it contains all changes between now and the last full backup very very important so 10:30 a.m. differential backup contains whatever changes happen between 10:30 and the last full backup which is at 1:00 a.m. am I correct what is he'll back up it's full backups or bolero how about the back differential backup I taken also this automatically taken at 10:30 a.m. what information will be there it filed that file that bak file contains all the data changes between 10:30 a.m. and the last full backup which is at 1 o'clock so whatever data changes between 10:30 a.m. and 1:00 a.m. will be the in the differential back got my point yes so can I say differential is a cumulative backup yes or no yes so differential backups are always cumulative backups because it always contains whatever data change happened now between now and the last full backup so Daniel the last the last differential backup would be the accumulator one right that's what you're saying yes okay back up differential backup created at 2:00 a.m. contains what one honey yeah 1:32 so keep on going like that okay so 1030 would have 10 o'clock 1 correct yes would it have anything everything from 1:30 although it will can apply no you do it adjust everything from 1:00 a.m. to 1:30 a.m. ok at 2:00 a.m. right not to actually what is the definition of years all changes between now now means 10:30 right and the last full backup back when did you create the last full backup now one eight one o'clock yes so whatever change happened between 1:30 a.m. and 1:00 o'clock we build or in this highlight a differential back ok got my point okay Daniel yes I'm sorry so the 10:30 a.m. what the differential backup that contains everything would happen between 1:00 a.m. and then up to 10:30 right correct yeah thank you Dan but what is there in that bak file I mean now we know it whatever changes happen between now and the last full backup but does that bak file contains all the m DF n DF l and l DF files or it contains something else expand I mean do pages we can change I guess in a differential backup file it only contains all debt up all I would say generalize all pages which has been changed after the last full backup very important point the differential backup can't a backup file contains it does not contain MDF file or MDF file or Lda file it contains all the pages which has been changed after the last full backup got my point yes now I need some feedback okay all right so now the database crashed at 10:43 what steps we can take to recover it you can first thing you can do is restore the the database using two one right and then then one one am full backup right that is step number one you need to take so when you restore the one am full backup you are bringing the database to a one am state am i right then on top of that you need to restore you need to restore from the until backup 10:30 a.m. diff backup open back so in that way you are bringing the database to ten thirty hundred you are bringing the database to a 10:30 a.m. state how much damage data you are loosing it at this point fifteen minutes okay thirteen minutes of data every mean lost but in here so now expiry CLE of that by adding a 30 minutes differential backup you are really bringing the data loss to just 13 minutes comparing to a nine hours and 43 minutes without the differential backup okay give me a second couple of messages in the chat window let me take a look that is correct Kelvin yes I will I am going to explain that okay now canal know that every time when a differential backup is created you its system will automatically create a backup file with a timestamp so the timestamp will make sure that it will not overwrite again and again so that is the first question also does that mean we lost data between 10:20 and 10:40 3 right now well yes I mean not 1020 because the last differential backup created at 10:30 so whatever data change happened between 10:30 and 10 43 is gone is the differential backup configure example yes you can configure all the different type of backups you can configure you can automate the full backup differential backup and the transaction log back up using scheduled jobs which we'll see later okay canal okay so now so at this point we are losing 13 minutes of data right but you're my company cannot even afford that my management says that is too much data we cannot do that we cannot go with that we need to find a solution to minimize the the data loss to probably less than five minutes well what you can two options you what you can do is you can run the differential backup every five minutes well that is not recommended because when you try to run the differential backup what now the drawback of the differential backup is while the backup is taken it is putting a a some kind of lock on the page level and you don't want to do that too much differential backup is bad so what is the next option you have what you can do is you can introduce a new type of backup which is called as transaction log backup what is a transaction log backup well in the transaction log backup what exactly happening is it will create a copy of what is there in the transaction log and store it as a backup file it's very simple it just create a copy of what is there in the transaction log and storage separate that is what called as a transaction log backup so transaction log backup can be run very frequently because it is not involved or it doesn't generate it gained any kind of locking issues in the database so you transaction log backup can be run very frequently so since my management said we cannot afford to lose more than five minutes of data I decided to run transaction log every five minutes so 11:05 there is a transaction log running so then one of 105 there is one running one or 10 one other running keep on two or five will keep on running that every five minutes there is a transactional log running so if you look at the transaction log created before the database Christ you can see that there is a transaction log created at the body now every five minutes I said every five minutes so there is one creator at 10:35 a.m. right yeah but then there is one more a 1040 or zero then 1040 there is another one created you're going to be after Yunus in this race for that yes and that is well within our limit because my man even said it cannot afford more than five minutes of data but that means three minutes is well within the limit right please meet a microphone if you are not speaking Calvin said all of the latest committed transactions whether we get into that in a minute so what file get created when you do a transaction log back up the transaction log back up extension is TRN for the full backup and differential one it is bak but for the transaction log it is a TR N and what is there in the dot TR and file the dot TR and file contains whatever whatever is there in the transaction log at the time you are creating the transaction log backup Turner can emit a microphone well sorry I put Minnesotan but I am using my iPhone on it for the problems okay sorry about that okay well no problem but I know you are eating some snacks that make me hungry okay all right so so the dot TR and file contains whatever there what whatever was there at the time of the backup in the transaction log so if you look at the data or the content in the back of file in the full backup you it contains MDF MDF and LDF files in the differential one it only contains 8 kilobyte pages which has been modified after you create the full backup in the transaction log nothing but a copy of the transaction log at that point of time but the most important thing you need to remember about that transaction log back apiece after each so each Tran backup transaction log will be truncated do you remember that after each so since the transaction log is truncated after each backup do you think that transaction log backup is cumulative what is there let me ask you this what is there in the transaction log backup created at 10:35 a.m. come on guys what is all the changes all the businesses no it doesn't contain any data changes it contains transaction log entries final transaction log entries corresponding to changes happen between 30 and 10:35 and what is there in the transaction log backup created at 10:40 is the committed changes Daniel or and it's not the committed changes whatever there are in the transaction log at that point of time if the usual committed then doesn't matter does it manual only they did only the data from diff from temporary to temporally not we I don't want to call it as a data I would say data means we normally talk about each Club ID data pages the best way we can explain is the ye few with the whatever if you ask me what is there in the transaction log backup creator 10:40 I will say all the entries made between 10:35 and 10:40 am I correct all the entries in the transaction log which has been made after 10:35 and before 10:40 yes what you said 35 will 10:35 a.m. 5 will be deleted right as soon as 10:40 is created not the file window you cannot never delete a transaction log file while the database is operation it will delete the entries in the transaction logs yeah that's good enough right then you don't have any data in the file that is correct well there are entries made between 10:30 and 10:35 and that is there here but when I create the transaction log at 10:35 concession of backup at 10:35 all the entries in that transaction log is gone after that am i right yes absolutely important or not 10:40 up when I create the transaction log 8 backup at 10:35 it will copy whatever is the entries in the transaction log at that point of time okay created dot TR and file and keep it there and then it will delete those entries from the transaction log so just after the transaction log backup creator 10:35 the transaction log is completely empty got my point but still but the or in long sandy right yes No originally yes I mean yes then but you are the sequel engine is keep on writing more and more entries into the transaction lock so the moment you clear out the existing transaction log the next second there will be new entries coming in right because there are transaction keep on going so when you create the transaction log backup at ten thirty forty well it contains that B that dot TR and file contains all the entries made into the transaction log after 10:35 and now it will create a dot TRN which copy the data there and then it will wipe out whatever the existing records in the transaction log and it moves on so unlike the differential backup transaction log backups are not cumulative oh I got two here okay that's what I'm getting confused okay in that in the differential you have the differences between 1 and then the 10th 1030 but with the transaction log you don't have like that right yes high-five me yes correct that's a maintenance then you need to do with the EGR and filed and that is the that is the overall point I mean oral idea what this discussion okay okay thank you Daniel then I got confused you see I did the 1040 and plunge it's a long wake up it will contain the last not 10:30 a.m. transaction like in 1040 1040 okay then 40 years 10:40 Granderson log wake up contains each entry whatever intern Jason loved wake up happen at 10:35 between 10:30 no no the 10th of 40 transaction log backup contains whatever entries made into the transaction log between 10:40 and after 10:35 a.m. so it's between the wake up then and it's something you have fun to run original transaction log any changes it won't make up that one what you mean by original transaction log gosh pain 35 is a make up copy right 10:35 the researcher and Jesslyn black make up is a copy of the original wake up no no no general because we have a transaction log backup created at 10:30 to somewhere here because we are doing it every I mean I did not clearly mention this but every five minutes the reset transaction log backup am I correct so 10:30 a.m. also the reset transaction so 10:30 a.m. both happens you have a transaction log backup as well as a differential backup got my point no I did not and yeah I did not get then 10:25 you have another transaction log backup at 10:25 give me a second I will explain that in a minute ok ok so now tell me what is there in the transaction log backup created at 10:30 a.m. what information will be there whatever entry is in 1025 transition love wake up it will contain in 10:30 okay entries in the in okay in the transaction log log between 10:30 and I am going to fly the last Tran backup am I correct yeah and you can find figure out when is the last time by which is 10:25 how about 10:35 what entry will be the the transaction log created at 10:35 we can gain Holly indentify yes if that is the case what entry will be there in the transaction log created at 10:40 between 1040 and 1035 right yeah is it clear now most bill I'm thinking my question is that okay if we schedule very first and it's and log back up it will contain all the entries from the transaction log original transaction log and then let original tangles and log is going to be truncated yes all right and now 10:30 and we schedule every five minute wake up the 10:30 we have tangles and log back up after there 10:35 it will contain only the entry from the 10:30 but watch happens before the original Anderson log is again freed out no no there is there is no og there is only one transaction log right so when a transaction log backup is created what exactly happening it will create it will create a copy of whatever entries in the transaction look at that point of time okay and then squee and save it as a tRNA file with a TR an extension and save it somewhere that is not a transaction log it is just a copy or not not yet just a backup of it then the only one the original transaction log file will be truncated that means all the entries in the transaction log will be deleted oh okay and and deleted at that point but again there are more transaction happening in the database so more and more entries will be added on to that transaction log so what I am trying to say here is there is no concept of multiple turn there's only one transaction log and we every time when a transaction log backup creative whatever the entries is there at that point in that transaction log will be backed up and once the backup is done everything will be deleted from the transaction log okay so taken a probability will be more clear to you when we actually do the restore process mm-hmm if you don't understand even after the restore process you can tell me at that time okay I did know it okay so when then I have a question yes go ahead the differential backup like at 10:30 it's going to be only one file or it's like because it's accumulative right so from it started it to one o clock in the night gave it up in our differential backup so today morning temp again going they have only one file which is it will be it will be much before a backup it will be differential these are different files okay okay okay but even if we have multiple differential backup files we only use the file created at 10:30 a.m. for the restore purpose unless you want to restore it into 8 2 a.m. let's say something drastically went wrong some accidental changes happen after 2:00 a.m. and you want to restore it back to 2:00 a.m. state instead of 10:30 a.m. you go for a differential backup created at 2:00 a.m. in the morning ok ok so we really should call it because with the latest file that is the differential backup taekman 10:30 a.m. I cannot say that go only until 2 o'clock in the night no you cannot do that if you want to do that well you need to do you need to use the appropriate differential backup which is the one created at 2:00 a.m. okay the other question is like for the transaction log backup very first time when it when we take the backup from this time - five minutes that is the Prime Minister interval we specify right so very first time we take that kind of data or pull transaction log whatever is available at that point of time are you asking me when we schedule this or so usually like - when a transaction like we can specify that take the back of every five minutes no you cannot do that with the transaction log you need to create a separate job for that all these operation creating a full backup what one am creating differential backup at every 30 minutes creating transaction log backup every five minutes are scheduled jobs in sequence over yes so that part yeah so very first time I am creating a new SQL job for transaction log backup and the very first time I specified like take for every five minutes so the job starts at first time at for example 10:30 in the morning so it takes the file the trm file is going to have the data from 10:25 to 10:30 or like whatever is the very first event that scheduled job runs when the first time first time when the scheduled job runs again whatever does not matter with what entries is there in the transaction log because maybe the transaction male lock may contain the last 24 hours of entrance doesn't matter whatever entries in the transaction log at that time of the transaction log backup will be there in the transaction log backup created so that means there is going to have all the 24 hours from there right the first time it will if the transaction log contains entries in the last 24 hours when it is trying to do the first transaction log backup well that transaction log contains all those entries ok got it ok ok and they again like even if very morally simple then all these concepts no no if the recovery model is simple art runs you cannot create a transaction log on a database which is recovery model simple yeah so it should be pull basically it has to be either full or bulk log oh man action log then is disabled for a database which is seen as simple recovery model we will see that later okay got it thank you no problem okay so let's come back to yes okay is this timing what we said is it like pretty much optional or do we have any even the transaction log backup I need him like we give breakup like five minutes each or like 30 minutes yeah it all depends on how much data you can lose why we went with a differential one because we our management said I mean it can lose only up to five minutes of data right so if if it cannot afford more than five minutes of data couple of option you can create a full backup every five minutes which you don't want to create do that then the next option is a differential backup so that you are creating a 30 minutes incremental backups right then but you don't want to run that very often like five minutes so you will go for a transaction log back up again it all depends on how much data you want to lose is that concept clear to you yeah yeah yeah yeah Larry said service level agreement yes SLA based it's it will be mentioned in the SLA how much data can maximum data can be lost based on the setup you are making that's correct Larry okay now let's come back here so now we want to add the transaction log back up into this restore chain so as we are see the transaction logs are not cumulative so we cannot just use the last transaction log back up we need to use all the transaction log backups needed here so can you tell me after restoring the database using the 10:30 a.m. differential backup what should I do restore all this available to our JSON log all available transaction locks all the packets onto the to get to transaction logs at 10:35 and then body yes you need not have to go and restore the 10:25 or 10:30 because you already made the database - at 10:30 a.m. State am I correct so now only thing you need to do is okay so give me a second all right so now you need to restore the database using 10:35 a.m. Tran backup right and by doing that you are bringing the database to a 10:35 a.m. State am I correct hello then on top of that you need to apply the transaction log backup created at 10:40 a.m. and bringing the database to a 10:40 a.m. state that's it that the last backup can be created so that last or entry in the the restore chain so how much data you lose here Ramin right yes you only using three minutes worth of data here in this setup and in the transaction log backup so restore when you restore the full backup what is happening when you restore the full back of what exactly happening behind the scene this will be with you it won't blow yeah but how it's happening what well to understand how what is happening you need to look into what is there in the bak file it contains a copy of MDF kind of a copy of MDF in DF and LD a file and the existing database MDF LDF an MDF file will be replaced by what is there in DES okay so that is pretty and then what okay that is once that is done well it will apply the differential backup when you apply when you restore the database using the differential backup which is something we did here in this particular step what actually happened behind the scene con guys looking all the 10 10 pages yes what is there in the differential backup the differential backup contains all the pages which has been changed by after the last full backup right so the differential bak file contains all the 8 kilobyte pages which has been modified so when you restore the database using the differential one what it does it will replace the existing those existing 8 club ID pages with the one available in the bak file am I correct hello did you get my point oh can you explain it again yes what is there in the bak file that one the differential log differential backup log back a file what is the in this particular file diff backup created at 10:30 a.m. all the content pages between 10:00 to 10:00 and 10:30 correct so when I restore the database using the differential backup creator at 10:30 a.m. what actually happening behind the scene making the changes applying facing the change pages after the 1 o'clock engine well yes how how it works how its making the changes it is replacing yes it is replacing the pages that is correct correct okay so but how about when you restore a transaction love back up when you restoring the full back up you are replacing the MDF LDF air india file when you restoring the differential one you are replacing that the pages which has been changed now the question is what is happening behind the scene when you try to restore the transaction log back of file it will append all the changes it will append all the Jim but how does it do that yeah but how it sadly have been in the full back of e so replacing the M in the data file and the log file in the differential V sword replacing the data files there are pages itself how we to append the records in using the transaction when you restore the transaction log backup well if you look at the transaction log backup what is there in the transaction log backup file it is just having the changes correct made off to the differential backup correct it has entries about what are the changes happened in the database at during that particular time am I correct yes so what it does is it will replay each and every sequel engine try to replay each and every entry in the transaction log backup so if if it is trying to replay all the entries in the transaction log backup created at 10:35 a.m. what exactly it is doing step-by-step it is moving the database from a 10:30 a.m. stage to a 10:35 a.m. State then you apply the 10:40 turn Section log back up so from 1035 its move it is applying it is replaying each and every entry in the transaction log back up creator at 10:42 bring the database to a 10:40 a.m. state so when you restore a transaction log back up you our sequel engine deleting sequel engine disease simply replay each and every step in that transaction log packet did you get my point now can you explain one more time Daniel yes after the different here like after the differential when you try to restore the transaction log backup file creator at 10:35 what the system will do is system will take a look at what is there in that transaction log backup file created at 10:35 and try to replay every step listed in that when you reap when the sequel engine replay every steps in the transaction log it is recreating all the data changes happen between 10:30 and 10:35 am I correct yeah then when you restore the transaction log creator at 10:40 the system will replay each and every entry in the transaction log create backup create at 10:40 which contains and so it is recreating or recreating or reuptake the data changes happen between 10:35 and 10:40 so when you restore a transaction log it is not replacing anything it is actually replaying the data changes happen in that particular in that duration of time okay because of this you can say even if you are restoring the 10:40 a.m. transaction log backup you can specifically tell the system hey Don restore the database to 10:40 stop it at stop it at 10:40 1037 and even 45 second you can say that this is what we called as anybody point a point in time we go recovery the only reason why it is possible because it is replaying each and every entry in the transaction log so if you say ok recover everything till 10:30 7:45 it will not execute any entries in the transaction log with a timestamp more than 10 37 and 45 seconds hey Daniel then I like this point in time recovering whatever entry bar after 10:30 7 it will dis decided yes for example well the database never get crashed I mean there is nothing wrong with the database how about this situation accidentally my programmer deleted all the records from a particular table at 10:38 a.m. so 10:38 a.m. there is an accidental delete operation happen and he reported that to me at 10:43 ok you reported that that to me at 10:43 so 10:43 well I decided that you know I will bring the database into a single user mode because I don't want anybody to keep on adding data here I will put it into a single user mode so that I have the only access nobody else can make a connection to this database then I verify that he made that mistake probably after 1838 so what I will do is recover everything to 1037 and 59 seconds got my point so I am recovering the database to a 1037 and 59 seconds but what we are losing here we are loosing whatever changes happen between this time 10:30 let's say 10:40 and sir 1038 and 10:43 did you understand this No I have gotten Daniel yes once you restore using all the backups those backup spoilers terribly useful or is going to be invalid no it is still be useful yes so if you want to do restore some other instance using the same wakeup we can do it correct if it is an accident will delete sometimes you know instead of restoring the production database you restore all these things into a temporary I mean a development database and then by that way you can see those data which has been deleted by the user white and then copy it over to the production database if it is possible so Daniel I have one question so one thing is at least in our environment like we have triggers like something got inserted particular tables so then depending when the tikar happens we increment the version counter in the table so an application rate bit version inflammation and apparently change it tries to update some information we're in the same danger and then in the same database yeah in the same database okay if you replay a technique from the transaction log in the backup then again the triggers everything is going to fire and everything right now the trigger will not file it is when you replay yes I mean the transaction log doesn't have any entry about the trigger firing or anything like that but it has entry about the data change caused by that trigger something like for example is the update trigger or interrupter so the transaction log contains entry about that update statement yes so that is going to replay then automatically the trigger is going to be executed no no no when I say replayed dot that doesn't mean that it is going to fire the DML DDL statements no I never do that the transaction log entries contains what data got changed and then it will replay them don't assume that when I say replay system is not going to replay the DML or DDL statement now okay the company creates a small table in indoor garden human is then that will also be recovered yes using the contact there will be an entry in the transaction log so but if there is any let's say you are a database in a bulk a logged recovery model and then if there is any mass insert happens somewhere in between this chain of backups that cannot be recovered Daniel Allen custom yes if me if you are doing restoring from production to development database or face database and if or there are records created in development or test database but not in production so if you restore the database from production to other environment do we need to recreate the ticker yes so we applicator we have to keep break up of each and every object before we do this tour yes so what normally what we will do is we will keep a 1-1 single script containing all these objects need to be created right and then after every time you restore your development environment the from a backup coming from a production you simply run the script and script will go and create all these objects in one charge so then one other question like I want to double check my understanding so relate to the transactional log thing like for example at 10:40 and 45 we took transaction log backups and they got here in 95 yeah 1010 for example okay 10:30 and 10:35 and then forwarding and 1040 also in between like 1038 somebody by mistake remove something and then we are going to replay only small part of the point recovery thing using the transaction log back up taken at 10:40 right correct so then when we do that we are going to like for example we ran only for one minute from 88 to 39 no no you cannot do that you cannot you cannot say you cannot say replay everything between 1036 and 1037 no no you cannot you always need to start from the start of the transaction log backup but you can specifically say when it will get is that what you are asking me okay okay yeah that is why if that is the case for example I am telling from 35 to 38 okay so then they am I going to lose the data from 39 and 40 just two minutes yes I am NOT going to you are going to lose that oh okay okay but it is not going to run in run any command instructions right so whatever happened before that should be there I am sorry whatever I'm sorry what did he say so whatever for example between 55 and 10:40 already the database is updated and everything is working fine right so that means even between 10 38 in 10:40 the database is updating lot of things and it's fine and now we know that only for a particular minute there is a problem and not particular minutes from certified to potat we ran the transaction like backup but it should not affect the already updated data or what data between 38 and 40 right no no you cannot do that you can always start replaying the transaction log file from this beginning of the transaction log but and then you can specifically say when it should end but you cannot give a begin and end start and end time yeah that part I understood now I'm sorry then it will not clear things so I am specifying from 35 like the start is 35 to 38 okay so it is running all the instructions button that are there for those three minutes ah so but it is not running the instructions that are there during 39 and 40 that those two minutes all right that is correct so that means it's not affecting it's not changing anything that happened between 39 the minute thirty ten thirty ninth and ten forty minutes those two minutes it's not touching anything it is not recovering not touching it it did it I would say it is not recovering whatever changes happen in a bit after 10:30 8:00 a.m. yeah so yeah so whatever previously happened it is not changing or it's not doing anything bit it is not recovering it's not recovering so that means that data is gone we need data gerontology no no the database I mean will be restored the database well we when the when we restore the full backup we are bringing the database into yet ten am sorry one AM state that means whatever data was available at 1:00 a.m. is there after I restore the full backup right the moment you start near restore process whatever data you had at the time of the crash is gone okay okay so that part I understood like for example 10:40 I don't have any clash until 10:00 party everything is working fine yes but then in the more than forty everything was working fine but the moment you even if the data everything is hundred-percent drive but the moment you start the restore process whatever the data available at 10:43 a.m. in your database is gone okay it's gone I mean it is not recoverable anymore then by step by step we are rebuilding that database right to the earliest or the latest possible version of that database yeah it is pretty much like you know destroying completely destroying a building and then you are trying to rebuild one level at a time - to the max you can go yes that's what I understood my question is like we are not destroying anything everything is like that until 10:40 current time eastern party until 10:40 there is no problem everything is what you find but then 1037 somebody came to me and told me like okay by mistake I removed the data from one table only for in that particular minute then I am NOT going to use any backup file just I want to use the transaction log to get the data back no you have no case I'm good to go I cannot do that no you cannot do that okay okay okay got it darling thank you okay now one question if I restore the 1040 am transaction log prior to 10:45 what will happen so 10:35 if I try to restore the 1040 am transaction log prior to restoring the 10:35 a.m. transaction log what will happen you will get an error okay why anything is out of sequence yes the error clear member clearly says Emerson illicit lock sequence number is out of sequence why why that happens problem because we are in terms like 1041 before the 10:35 yes so what is the lock sequence of lock sequence number the system now let's say after creating the differential backup the latest lock sequence number is this in the transaction log okay the latest Ellison is l SN is five six seven six seven after the differential one when we restore the next backup system is expecting which Ellison electrum greater than bit member not just simply greater than it is going to be what lock sequence number it has to be in a sequence right so it has to be e and in the case of 10:40 may be the locks and then this locks occur so in this case the lock sequence number run from five six seven - probably let's say seven 0-9 right yes that is the range of LSN in this particular transaction log backup and what Ellison I expect in the backup creator at 10:40 it's supposed to be a little bit of nine not nine once again yes something like that so if you try to restore the 10:40 a.m. transaction log back up prior to 10:35 what happened is well after the differential backup the last lock sequence number is five six seven six seven and the next one you are trying to introduce to the sequel engine is seven thousand ten so there is a so the either the log sequence number is not in sequence am i right so the moment it ca is log sequence number which is not in sequence it will yell at you saying that the lock sequence number is not in order or in sequential but there is the matter Danya is it what is me by a clip chain broken yes it is called as the lock sequence number chain broken yes how would you fix that how much you well there is no way you can fix it you need to provide the appropriate backup with the sequential lock sequence number I mean there is no other way you can fix it if the lock what's number chain is broken I mean there is no way you can fix it I mean that means it's not about the fix you are missing some of the files in that backup chain that is what it means the reason I ask is like one of this is one other question like the interview question they asked me you know yes how would you fix like it like I've changed broken yes I mean there is no way you can fix it you need to provide because there is no way you can manually go and make a change in the lock sequence number it's all taken care by the system so the only way you can fix it is give the appropriate backup with the next clock sequence number because if you have a backup chain like this system should have created the backup with sequential lock sequence number mean the sequencer the back up with the sequence but when you restore it you are not restoring it the appropriate sequence it is created that's the problem that is when it is called as proc so so but Daniel this there will be a maximum limit right it some four LS number then it has to round like it has to come back to zero something right ah that I'm not sure Larry are you aware about a maximum number for the locks I don't think there's a maximum number available for lock sequence I'm not only just we said when we do the full backup again it reset I think is we said when we do the full backup attack right miss bring the news to you want one we do the full backup that it really is stuff the new chain right yes idiot that is true Yates yeah I remember that yes when you restore sorry when you create a full backup it will reset the lock sequence number to zero because it is mr. it is starting a new chain as Larry mentioned it is the start of a new chain okay okay okay sounds good thank you also Daniel I mentioned that it's good idea when we have a database crash the first thing we want to do is that we back up the tail or the log so yes I don't know I mean we will be covering that but not at this point what Larry mentioned is I mean at this point I mean we can we are sure that we can recover everything till 10:40 but there is something called tail log backup which is a technique you can use to even recover anything the data to till the last moment before the crash that means that say the database bash at 4:30 10:43 you can using the tail log backup you can move ahead and restore the database to 1042 and even 59 seconds we will talk about the tail of backup after we see how this backup and restore can be done using point and click method and using this correct okay so let's take a break here it's 10:23 come back at 10 2009 and then we will see how you can actually do this point of the backup and restore all right down one one thing one thing Daniel yes so what's happening is I'm using my scope and once I started using phone I'm not able to access the session from my lap no laptop so high I sent via email with another email address so can you give you access to that also if possible I'll give me a second okay I can do that give me a second Daniel regarding your seventh or a wood oh I forgot man I'm sorry you I know you are going to kick me I completely forgot I am going to do that after traceless I am so sorry hey Daniel and also is it ok it can be Kenny call restore tomorrow I mean if the whole class agrees with the restore yeah you mean the rubble yeah I mean we are not going to cover the restore we are probably today and even tomorrow we are going to cover the backups ok Daniel this is you know yes yeah to deck the Daniel I find your videos only class after class 17 I see 21 or something like the 20 but you did not know you you did not download any of those I get down during class 17 that's my last video ok so which one you are missing I don't know after class 17 water all day ok I deleted that assuming that you guys all delete I mean download act ok I'm sorry I just I was not here yesterday and day before us today ok I can upload that that's not a problem and then you know that the password is now set for the jungle why yes there is no password but I am going to set it because I see lot of downloads happening I don't blame any of you but so I will put a new password and then I will let everybody know okay and there is a material also I just see only class I'd lost downloaded in class 17 well whatever material I applauded it's all there only only the video you deleted it yes only the video said in all the materials research paper everything is still there okay then you need to upload it last 18 and 19 okay I will I'm sorry I was not here from past few days no not a problem yesterday well here but I don't know I was unable to log on your meeting I shoot an email actually to you yes I saw that yeah I don't know it it took me a long time actually why yeah I mean I saw but eventually you got connected right no actually what happened was I was connected using my another laptop the internet was not connected properly its I so I closed it and I started using my another laptop but then it's not allowing me to login what what what yeah because you try to connect using one laptop and then you need to wait 20 minutes to if you want to try over there 30 minutes actually more really normally it's okay I approve I approve your ravi you are up yo station has been approved oh thank you okay you will be getting an email from goto meeting pretty soon okay I got it thank you and Janardan I got a new registration I mean request from you with another gate ignore that it just ignore it that well data that that is actually approved for you approved it it doesn't matter because what oh yes said it keep asking me so I put some details I want to put some details so I thought I can get an access but later it say that you need to get an uproar so then later after 30 35 minutes or 40 minute search right when we login with the same eyes are you doing right away or you can do it later also that's not a problem okay which one which one the videos you can upload it later yeah I will upload that later okay yeah shoot me an email once you upload it so I can okay go to Android okay all right Eric I believe why you made two connections that will trigger some hyukoh if one of the connection you are not using you can disconnect Targa are you talking to me mind I am doing it Daniel okay all right because I see two connections coming from you know okay I think you disconnected one okay all right so let's take a look at how we can do the backup and restore well before that do you have any questions maybe I think this is a right I mean normally I discuss the tail log back up after I show you all the different scripts you need to use to back up every sir maybe since we have all this excel ready probably I can also explain what is at a log back so using this method I mean don't forget about this 1038 kind of stuff okay so now using this method you are pretty sure that you can restore the database to it 1040 am state that means you are losing ten three minutes worth of data but as a database administrator when you see your database got crashed don't get panic okay the first thing you need to do is go and get a large cup of coffee okay because you may wonder need that then what do you have to first thing is don't go and restored start restoring the database that is what one of the missing mistake most of the DBA sneaked they get panic and start restoring the database now don't do that you need to try for something called a log backup so let's explain what ate a log back apiece so after the database crashed at 10:43 let's say at 10:43 and 32nd or let's say 10:45 a.m. what you will do is you will try to create something called dead log back what is it a log backup tale log backup is nothing but a transaction log backup it's a transaction log backup but why it is called as at a log backup anybody tale log backup is normally taken before you restore a database and the reason why that it is called as stay logged backup because it contains it contains the tale of transaction log backups or transactional log file what do you mean by tale of transaction log bat a transaction log file it contains all the last entries available in the transaction log file in this particular backup in this particular transaction log backup it contains whatever is there in the transaction log at the time of the crash do you understand what I play log back apiece tail log back up is nothing but a transaction log you manually created this is not an automated process okay it's a manual transaction log backup created by the DBA and that transaction log backup contains the tail or all the last entries in the transaction log because of that it is called as stayed log back up so we created ok let us say it 1044 I mean I don't want you to get confused make it as 1044 so then what you will do is so but you also need to understand that not every time you can create at a log backup if you want to create a log backup then the transaction log for that particular database will be intact let's say the data file one of the data for the primary data file got corrupted that means that database will be offline but again the drive which contains the key transaction log for that database is still up and running him that that's the case you can create at a log backup in our case somebody deleted all the rows from a table at 10:38 a.m. so there is nothing went wrong with the structure of the database the database is up and running so naturally you can create a tail log of that so it is worth to try I mean again if your database is crashed or something went wrong don't go and start the restore plus the moment you start the restore process your opportunity to create a tail log backup is gone so even before you try to do this this particular step you need to try whether you can create Italy log backup so case let's assume that we could able to create the tale log back up okay so there is a tale log back up which is a transaction log back up contains the last entries in the transaction log in this particular backup then you restore the full backup restore the differential backup created at 10:30 the transaction log restore the transaction log create at 10:35 and again restore the transaction log a backup created at 10:40 now what so one question Daniel retain log backup for example in this case if you're going to get have the data from 1042 1044 or we don't know it get the data from 1042 10:43 a.m. in the transaction log file yeah then what if you yes ok great thank you then you will try to restore the tale log backup created at 1044 am not I would say tail log backup so let's say your database crashed exactly at 10:43 you can say replay everything up to 42 and 50 seconds so you can see that with the help of the tail log backup you can bring the database to 8 10 42 and 50 seconds so how much worth of data you are losing here 10 seconds need 10 seconds worth of data you are losing again it doesn't work all the time okay but it is what to try to create a tail log backup the only time you can create it is before you start restoring the moment you start restoring your opportunities gone then liquid on which can you repeat what cases we cannot hear create a log backup where you measure a few things well I mean if you're you're you're the the motherboard if your hard disk is gone sorry the motherboard of your secret server is gone so something like that it is not a possibility but for example I mean if there is some accidental data change happen at 1041 yeah or or let's say you are the the disk the primary disk containing sorry the disk containing the primary file group is corrupted in that case the database is not available anymore it's crashed yeah but assume that for the same database the transaction log file may be in another drawing right here so in that case you can able to create a backup of that tranch even if the database is not available online still you can create a transaction log backup of that particular database using the script you cannot do that using the point and click method but you need a script to create that so it is always a good idea to try to create the tail log back up so that you can recover the data to the very last minute okay not it thank you any questions Supriya everything is clear for you I'm trying to understand Daniel it's totally new to me so long getting all the things confused yes a little bit not little bit like a lot yes the only way I can help you in this case is you need to review the videos so um do that yes um again this is the most important concept in us as a DBA so you must understand this you need to I mean book stock I mean lot of books talk about what are the different ways you can backup and restore but you need to get a complete picture so that you can you can decide what need to be done when there is a disaster okay I'll try to review one in as many as I can get okay all right so um how about champ spotty I know us you are interactive about Rajini and Cham I did not hear anything from you everything is in life is good I almost Daniel okay can I ask you this Daniel back to basics again yeah all we're talking about is make database crash I know how it works in the real world but how about the whole of hardware failure like yeah even if the entire hardware is failed I mean the hardware failure uh well if you have a server with its own standalone hard disk which we never see in a production environment in production environment even if you see II Drive F Drive but those are not local drives right it is coming from the sand it is actually a volume coming from the sand exposed to a a machine as a drive or F so with respect to that if a hardware failure is a I would say is not a clear definition what actually happened if the motherboard of your physical server burned out that is a hardware failure if the NIC card is gone that is a hardware failure okay but so let's say some of the hardware failure happened nikhat is gone or the motherboard is gone so only option is you need to bring a new hardware okay and then then what you need to create a new instance of the database there so not the database new instance of the sequel server because the other instance is gone then once it is created you need to restore the databases using the backup you created and stored somewhere in the network path or in the tape drive normally these backups will be stored somewhere in the network drive or in the tape drive you don't normally keep it in your local hardware because if the hardware is gone and if you store the backup in the hardware etcetera that same machine itself you lost your backup - so you never do that we always keep it in the tape drive or in the local I mean in that network share drive so if your hardware is failed you might be ending up with installing the sequel I mean bring the new hardware and then go for a brand new installation of sequel server once the brand new that the instance is installed then you need to restore the database from the backup and then there is a tedious job after that that scenario I mean you restore the database well the database will be up and running but not all the users we you need to address a headache called orphaned records which we will seal off sorry orphaned user which we will be talking later okay yeah yeah yeah thank you yes Calvin I did give a researcher I did not give an official email about it I told you the next the search paper will be about anybody remember well in fact I mean II am probably I shouldn't send email on to search paper because even if I did not send any email actually a lot of people who turned that to search paper on indexes what was the topic for the research paper for the index everybody Oh back up no no the index or in the hood what was it intercept index the hinge index what intersect or something index intersection in the index on index in the second and industries going yes I mean if you want I can send you an email but it is very clear you need to investigate more on what is an index intersect and what is an index join you can either google it or as I mentioned you need to go through chapter four six and seven four six and eight right on the e-book the performance ebook you already yeah you already covered that in the past actually yeah yeah I mean now somebody know I just briefly covered what it is but it is a good idea to go through that that particular section two more to get more insight into what that one is shoo-shoo yeah okay I have a correction to make yes ah the last LSN Nampo aha in this case we have seven zero zero zero nine is equal to I mean it's going to be the fast a lesson of the second transition yes it has the best yes I mean fold up for the better understanding I said seven ten but yes in fact that is true because the last a lesson of this file will be the first Anderson of the next file you are right okay thank you alright so I believe we don't have that type again yeah Kelvin yes you solely so such paper five I did not put any research paper six but in fact the research paper six is about index intersection and indexed oh don't worry about it you still have some time so you can I mean if you want I can add it there to give you an idea what I'm talking about and the seventh research paper will be on rate technology and that I will give it out up probably tomorrow and you can do it on this weekend all right so we don't have the time to actually do a practice on how to do backups so we are going to start with that from tomorrow class onwards so tomorrow we will be covering all the different type of backups and then day after tomorrow we will be covering up day after tomorrow and probably one more day we needed to cover all the different type of restocks any questions

Channel: EmpireDataSystems

Views: 54,904

Rating: 4.7362638 out of 5

Keywords: SQL Training, SQL Server Training, DBA Training, sql tutorial, sql server tutorial, learn sql server, sql server course, SQL Server 2019 Training, learn sql server 2019, sql server 2019 course, sql server dba training, sql server training, learn sql, sql server 2019 classes, sql server classes

Id: bSp6p9GwTvg

Length: 105min 31sec (6331 seconds)

Published: Sun Mar 18 2012