SQL Server DBA Tutorial 74-Manually Take Tail Log Transaction Log backup of a Database in SQL Server

welcome to tech brothers in this video we're going to learn how to manually take tail log transactional log backup of a database in sequel server and we'll be using two method one is using sequel server management studio graphic user interface and other method would be using T sequel script keep in mind the the reason why we need to take a T log transactional log backup if your database is already corrupted and you're in process of restoring that it's always a good idea to take tail log backup of your transaction so that once you're done with restoring your full backup and differential backup and other transactional log backup then you can go ahead and restore your tail log back up and bring your database online tail log backup usually contains all the transaction that hasn't been captured during your normal transaction log backup so you don't want to you know you don't want to lose the transaction that wasn't that weren't captured in your transactional log back up and to minimum to minimal the effect of data loss you need to take at a log transactional log back up again this tail tail log backup I'm repeating myself that it is going to be when your database is already in bad shape and you're trying to restore your database and trying to minimum minimal the effect of losing the data because your database is already corrupted and you need to restore that so let's go ahead and do that this is my sequel server and these are my databases let's say that right here is my database that's corrupted and database has to be online in order to take the T log back up but after dbcc run you made sure that it's corrupted and you need to restore the previous database backup such as full log full backup and then you go ahead and do the transactional log backup and restore all the I'm sorry differential log backup and restore all the transactional log backup but you don't want to lose the transaction that hasn't been captured in your transactional log back up you're going to do that with the Tia tail log so we're gonna go ahead right-click on the database and go to the bit backup and we're going to select transactional log backup since it is a family of transaction log backup so we're gonna go ahead and select that keep in mind that when you take the tail log backup you need to select copy only backup right here and here is a your backup where it's going to store your backups so we're going to go ahead and click on media options and look at the media options right here if we have a pent to the existing backup if you do the append the other existing backup sets won't get hurt so it'll just append in the particular location that you have provided overwrite will hurt existing backup sets and it will overwrite the dis back up on the other backups that you already have on that storage so check media name backup expiration this is all about the media if you're using the tape media for example you can check this bar and give the media set name and also if you are starting your tape backup and you want it to format your tape backup and start the new backups on that you can select this option also if you wanted to verify usually T log backup you don't want to really basically you know verify because if you click on here the the up here is the option that when you wanted to do the tail log off of tail log backup of any database you need to select this because this will prevent it from not truncating because your database is already corrupted and you don't want really the the truncate transactions log it what it'll do is it backups the T log and put the database in restoring state and the reason it does that once the databases in restore mode you can go ahead and restore your full backup and then after that you can restore your differential then you can restore your other transaction log backup once you're done keep in mind when you're restoring full backup differential and transaction log you need to leave the database in no Recovery Options up here if you I'll show you real quick the recovery option when you when we go make video of restoring the transaction log back up full backup and differential so how you can leave the recovery model in non recovery because once you're done with restoring all your backups and you wanted to restore the transaction of teh log backup you can go ahead and restore the tail log back up and bring your database in recovery mode again I'll show you in next demo offer restoring the databases that where you do that so this is a using graphic user interface to take a up here the tail log back up off your database so we're going to go ahead and click OK as you can see the backup log sales order completed successfully now right here as you can see that it is gone in restoring State now it is the time for you to go ahead and restore your full backup your differential backup your other transactional log backups and once you're done with that you can go ahead and restore the tail log backup and and then we're going to restore the transactional log backup and after that we'll go ahead and do the t lock restauration and bring our database online so right click on the database since it's already in restoring stage so it's expecting us to give the path to the full backup or whatever the backup that you wanted to restore so we're going to go ahead and click on database restore the database and first we're going to go ahead and restore the full backup so we're going to look where our full backup is this is our full backup right here so we're going to go ahead and select that and as you can see it selected the full backup and we're going to go in options and this is what I was talking about that you need to leave the database in on recovery mode if you wanted to go ahead and restore your differential differential back up and also your transactional log back up it needs to remain in non recovery so you have three options restore with recovery this will bring the database online and it won't be in restore mode and you won't be able to go ahead and restore the differential and transactional log back up especially the transaction log back up differential you still can do it but we're gonna go ahead and keep non recovery just to make sure that no corruption happened while we're restoring the database and also no user can come and access the database during the restore so that's why we need to leave the database in no recovery and in restore mode once we're done with the restore then we're going to go ahead and bring the database online so we're going to select restore with no recovery you can go ahead and click on override existing database so we're gonna go ahead and click OK as you can see the full database is restored but it's still in restoring mode because we have selected with no recovery so we're gonna go ahead and now restore the differential backup with no recovery again so when I click on device go and add our differential backup right here this is our differential backup we're gonna go an option and click again with no recovery because we still have a transactional log backup to restore so we're gonna go ahead and click OK as you can see the database sales order restored so our differential backup is restored now it's time for us to go ahead and bring the transactional log back up so we're gonna go click on device and I don't have really a transaction log backup but I'm going to use our tail log backup as a lost transactional log backup just for this demo purposes you can go ahead and select your transactional log backup once you have the transactional log backup and you can go ahead and select that and restore those transactions in no recovery again but once you come to once you restored all the transaction log and you're ready for your tale log to be restored you're gonna go ahead and click on add and here's my tale log backup so I'm going to go ahead and select that and we will go an option and up here it says leave the database ready to use for rolling back and this is restored with recovery and this is what we wanted to do this will bring our database online so we're gonna go ahead and this is our tale log backup so we're going to go ahead and click OK and as you can see the database sales order restored successfully and let's see that if database is online now as you can see the database is not in restoring mode it's online so we have recovered our database so basically this is how you use the tail log if your database is already corrupt and you want it to restore from your full differential transactional log and then finally go ahead and do the T log restoration and this is using graphic user interface we're gonna go ahead and go do the using transaction log script so we're gonna go ahead and open the file the T log tale of transaction log backup right here so up here it's a same command as the transaction log log backup but up here you need to pay attention no truncate that means that this is at a log and copy only these are the two hint that you got that it is going to be at a log a transaction log backup it is not the normal transaction log backup normally we would have truncate because we do want once the backup of transaction log happened we want that space to be cleared for other transactions to happen but in this case when we are doing the tail log it has to be with no truncate and copy only so we're gonna go ahead and run this command and it will bring the database our database in restore mode so we're gonna go ahead and you can go ahead and follow the same steps to bring the database online so I have a video to how to restore the database full database and differential database using T sequel you can go ahead and use the same script to restore your full differential and transaction once you're done with the transaction you can go ahead and restore your tail log back up so we're going to go ahead and click execute and let's refresh this as you can see the sales order has gone in restoring mode so follow those steps to bring your database online using T sequel script and I pop dis house

Channel: TechBrothersIT

Views: 30,338

Rating: 4.7931032 out of 5

Keywords: Tail, Log, Transactional, Backup, Manually, SQL, Server, Developer, TSQL, DBA, Interview, 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, Transaction Log, Database (Software Genre), TechBrothersIt

Id: 6yI-uPw_qNw

Length: 11min 52sec (712 seconds)

Published: Tue Mar 03 2015