Restore tail-log backups - SQL Server Tutorial

- [Instructor] In the middle of a crisis, where you fear the database is lost, and you need to get it back up and running as quickly as possible, it's easy to jump right into the Recovery Phase and start restoring the backups that you've been carefully creating. But there is an intermediate step that you should investigate before you start, and it can possibly restore the database to the exact point at which it failed, regardless of when the last Transaction Log Backup was made. The process is called restoring a Tail Log Backup. Let's see how it works by creating a new database, I'll call mine TailLogDB, and then we'll switch into it. Now, the first thing I want to do is create a table, and add in some data. I'm going to create a table called People, that just includes a couple of columns. Then, I'm going to create a Stored Procedure called InsertPeople, and it's just going to simply run through a loop, and insert 100 records into this table. I'll execute the Stored Procedure, and then finally I'll select everything from the People table, just to verify that it worked. In the Results window, I could take a look on the very bottom, and see that this table now includes 100 rows of data. Let's go ahead and scroll down, and we'll create a backup of this database. I'll backup the database, TailLogDB, and I'll send it to disk. The file path will be to a folder that I've already created on my C drive called TempDatabases, and the name of the backup will be TailLogDB_FULL.bak. Now I have a backup that includes these 100 records that we just added. Next, I'm going to execute that Stored Procedure again, to add in an additional 100 records, and then once again I'll select everything from the People table, just to verify that that worked. Now on the bottom I can see that I have a total of 200 rows in this database. Now let's say that our database has suffered a failure. In order to simulate this, I'm going to come over to the Object Explorer window, right click on Databases and choose Refresh. Then, I'll right click on the TailLogDB file, I'll go to Tasks, and I'll choose Take Offline. I'll place a check mark in the Drop All Active Connections box, and then press OK, to take the database offline. If I right click on Databases, and choose Refresh again, you'll see that message right here. Next, let's go ahead and take a look at our file system. First, I'll take a look at the TempDatabases folder, and we can see that I have the backup file that we just saved. Then, on the C drive, I'll go into Program Files, Microsoft SQL Server, the name of my instance, which for me is this one right here, MSSQL, and then finally the DATA folder. Here we can see we have the mdf for the data file, and the ldf, or the log file, for the Tail Log Database that we've been working with. Now, if you're following best practices, and keeping your data and log files on different physical drives, then it's entirely possible that a drive failure could take out the data file, and leave you with only the Transaction Log. We can simulate this by simply deleting the mdf file from the hard drive. I'll right click on it, and choose Delete. Then, I'll switch back into Management Studio, to see how we can recover from this disaster. First, I'll come over to the TailLogDB file, right click on it, point to Tasks, and then choose Bring Online, to attempt to bring the database back online. It's going to tell me that it was completed with errors, and when I say Close, and then right click on it, and choose Refresh, you'll notice that its status changes to Recovery Pending. Now, before I start to attempt the Restore procedure, I want to make sure that I backup that Tail Log, in order to capture the second instance of the 100 records that we added to the database. Let's go ahead and scroll down on our script a little bit, and we'll see how to do that. First, I'll switch into the Master Database. Then, I'll backup the log file from the Tail Log Database. We're going to send it to disk as well, in fact we'll put it in the same location, I'll call it TailLogDB.log. If I just highlight these two lines and execute them, you'll notice that I get an error message saying that the Backup Log is terminating abnormally. In order to get around this, I'll specify WITH CONTINUE_AFTER_ERROR. This will force SQL Server to store the log file, even though it's generating this error. I'll go ahead and highlight these lines, and execute it. This time I get the message that the Backup Log was successfully processed. Then, I can go ahead and scroll down, and start the Restore procedure. I'm still in the Master Database, so I will restore the database TailLogDB, from the full backup that we created a moment ago. The next line, WITH NORECOVERY, stipulates that the Restore process will not attempt to undo, or roll back any uncommitted transactions. In other words, if a modification to the data had begun, but not entirely finished when the database failure occurred, there would be a record of that in the Transaction Log. Typically, during a Restore, SQL Server will attempt to roll back any of these changes that were only partially completed. We don't want this to happen, in this case, so that's why we're going to specify the NORECOVERY keyword. I'll highlight all of these lines and execute them, to start the Restore. At this point, we've restored the database to the point when the backup was taken. This is after we've inserted the first 100 records, but before we inserted the second 100 records. In order to get the full completed record set, I'm going to restore the log file as well. We'll restore the log TailLogDB, from the disk file that we exported a moment ago. Then I can scroll down and verify the results. We'll just run a simple SELECT statement to select everything from the People table. If I look down at the bottom, I can see that I'm back to the full set of 200 records that were added to the database. This is the 100 that were taken before the backup, and the 100 that were added after the backup occurred. And we're able to get these from the Tail Log Database Restore. Finally, I'll scroll down a little bit, and we'll go ahead and drop the Tail Log Database, to clean up the server instance. So the Tail Log of the transaction contains everything that occurred after the last Transaction Log, or the Full Backup has occurred. Depending on the type of failure the database experienced, it's possible that the Transaction Log still contains salvageable information that can be beneficial in a Database Restore scenario. It's always worth remembering to try and preserve this information, even after a disaster has already struck.