SQL Server DBA Tutorial 80-How to Restore a Database to Specific Time Point in Time in SQL Server

welcome to tech brothers in this video we're going to learn how to restore a database to a specific time also known as point in time in sequel server and we'll be using two method to do that One is using sequel server management studio graphic user interface and other is using T sequel script and I have written down a scenario scenario is that you have a full backup that you have taken at 6:03 p.m. and it had your database status was it contains stable customer and then after that you went head and added another table and took the differential backup so the change that happened in your database between full backup and differential backup is added another table so we took the differential backup at 6:05 which has two tables customer 1 and customer 2 then after that I have scheduled transactional log backup which happened at 6:06 and in real life scenario in real organization this scenario is there but the timing I just put it out there for demo purposes just how to restore point in time so transaction log 606 table hat 1 2 3 4 5 this database has 5 tables someone went ahead at 609 deleted the table of 4 & 5 basically 607 right here so and it was detected and somebody told the DBA that you know the delete happened at 6 or 7 and DBA right away knew that okay good good safe because I have a transaction log backup at 6:06 which means that all tables in the database were present at that time so what I'm going to do is go ahead and restore my database from 603 to 606 and that way I will recover my database to its good state which means that it has 1 2 3 4 5 and we're saving changes would happen from 603 to 6:06 so this is the scenario we're going to go ahead and implement that scenario I'm going to go ahead and use the first method up here using sequel server management studio graphic user interface and then we're going to perform the same thing using T sequel script so here's my server tech brothers sequel server sequel prod is my instance and here's the database and database in question right now we're doing is sales orders database so let's say that somebody deleted that database so we're going to perform these three steps right here perform manually for backup manually differential backup and then manually transactional log backup now keep in mind when we do the full backup restore we need to have our database in no recovery mode because we still have differential and transaction log backup to be restored so it's a differential backup is the same thing that when you do the differential backup you're going to do the no recovery mode so that you can go ahead and do the transaction log backup keep in mind that this whole thing can be done in one step but I'm going to do it in two steps and to recover the database but there you know you might have a situation where you have to do it in two step but the whole thing can be done in one step so we're going to go ahead and right click on the database and restore the database first what we're going to do is click on device and find our full backup and up here the full backup right here is 6:03 it happened at 6:03 we're going to go ahead and select that and then we're going to go ahead and click on our differential backup which happened at 6:05 we're going to go ahead and click that basically let me show you first the tables right now we have customer two and three and we're missing four and five tables so we're going to restore once I restore is completed we should see customer two three four and five table so I'm going to go ahead and perform the same thing click on device add your full backup right here and then add your differential backup 6:05 click ok as you can see full backup differential backup and if you go to option we are not going to restore with recovery since our differential transaction log backup still needs to be restored so we're going to go ahead and restore with no recovery and we're going to go ahead and overwrite existing database so as you can see we're restoring the full backup differential backup and then we're going to go ahead and do the transaction click OK all right we got an error let's look at the error as it shows that database is in use so we're going to go in option and click on close connection so we're going to go ahead and click OK now so it should close all the connection and restore our database so as you can see that database is in restore mode still it's waiting for us to bring it online and the way that we wanted to bring it online we wanted to go ahead and restore the last transaction log backup where we have the tables that really were deleted during differential and transactional log backup so I'm going to go ahead and restore transaction log backup and I'm going to click on device add and here is our transaction log backup right here click OK I'm going to up here we have restore with recovery that means that once the restore is completed our database should come online and we should have all those tables in there so we're going to go ahead and look at general option we're good with that so we're going to go ahead and click OK and our database is restored as you can see it's out of the restore mode so we're going to go ahead and refresh and look at the tables as you can see our 4 & 5 table are back online so we're going to go ahead and delete these tables again so that we can perform using T sequel so I have a script right here go to file and we have point in time restore as you can see right here is stay taking the tail log back up and then after that is is restoring the fall back up in no recovery differential back up in no recovery as I said that you could have added your transactions right there in step one and it would have restored everything but I just wanted to show you that how you do it in no recovery and still have database in restore mode and go ahead and get your transaction restored and bring the database online so up here we're going to go ahead and use this script alright as you can see that database restore is completed and we're going to go ahead and see that if our tables are back in there as you can see our tables are back in our database so basically this is how you restore in time if you know your transactions are intact and you know the time that when something disaster happened to your particular database you can use your full database you can use your differential a log differential backup and you can use transaction log backup and restore your database to a good state and I hope this video helps and that we went through this particular scenario and we executed using sequel server management studio and we executed using T sequel script and again I hope this video helps

Channel: TechBrothersIT

Views: 44,392

Keywords: Point in Time Restore, Backup, Restore, SQL, Server, Developer, DBA, Scripts, DBA Tips, SQL Developer, SQL Server Interview Questions, TSQL Interview Questions and Answer, 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, TechBrothersIT, Microsoft SQL Server (Software), Database Administrator (Job Title)

Id: _zN5Izf4wzk

Length: 9min 11sec (551 seconds)

Published: Thu Mar 05 2015