SQLU DBA Week – Recovering Lost Data

SQLU DBA Week – Recovering Lost Data

Welcome to day two of Administration Week 1 for SQL University. I’m sharing this week with Mike Walsh (blog|@mike_walsh). The goal is to bring you a varying approach to administration and delve into topics to different levels. If you have not already done so, be sure to check out Mike’s post for day 1: SQLU DBA Week – You Can Restore It! (right?).

Mike took a look at building a restore strategy to meet business requirements and how to be sure that your strategy does indeed meet those requirements. Today’s lesson will be on a similar topic. We’re going to dive into the topic of recovering lost data using backups.

Before we start, I want to digress for a little vocabulary lesson. “Backup” is a noun. “Back up” is a verb. You don’t “backup a database.” You “back up a database”. When you “back up” a database, it creates a “backup” of the database. There will be a quiz on this (not really, but I wish I could).

Why Do We Back Up Databases?

You back up all of your databases, right? What are the benefits of backing up your databases? What does a database backup do for you?

There are several reasons why you should back up all of your databases. for today’s lesson, we’re going to focus in on the first reason, recoverability. To be more specific recovering data that someone accidentally deleted.

• Recoverability: recover from corruption, disaster, data loss, etc.
• Availability: log shipping, database mirroring, and replication
• Portability: move the production database into non-production environments
• Maintenance: maintain the transaction log
• Job security: nobody has ever gotten fired because they had backups and didn’t need them

Recovering Lost Data

When I take over new servers for an unknown application, the first and last thing I do on the server is backups. I have standardized backup scripts that I apply. These scripts set up a schedule of weekly full backups, daily differential backups, and transaction log backups every thirty minutes. I call this CMA. It covers my backside by making sure that I have backups while I get to know the server and the application. I work toward defining the Recovery Time Objective (RTO) and Recovery Point Objective (RPO) and identifying any Service Level Agreements (SLAs) that apply to the server. I can then come up with a restore plan and customize the backup schedule to meet the needs of those plans. See SQLU DBA Week – You Can Restore It! (right?) for details on RTO, RPO, and SLAs.

The number one reason to backup your databases is because you need to be able to recover from corruption or from a disaster event. Everybody knows that they need to be protected against corruption and disasters, yet I hear at least a couple of times a week about people experiencing these events without backups. Another event people don’t often think about is how would you recover from someone accidentally deleting data?

I have relied on backups many times in my career for this scenario. I restore backups to a different server or the same server with a different name stopping at the point right before the data is deleted. Then I can manually export the missing data and insert it back into the database. Another strategy for this is to use log shipping and delay restore of the transaction logs by some period. There is a configuration setting in log shipping where you can tell it to delay restore by a set time length and it won’t restore a log file until it is at least as old as the configured time span.

It is not likely that someone will remember the exact time it was when the data was accidentally deleted. They may have a rough guess. You will probably need to play around with the restore point to get the timing right. You don’t need to restart the whole restore sequence every trial. Simply restore the log file in stand-by mode so that you can query to see if the data is present or not. When the database is in stand-by mode, it is read-only and can be queried. SQL creates and “undo” file so that it can roll back crash recovery for the database. That means it can reverse the last log file restore. I can restore the log file in stand-by mode to a point-in-time, query for the missing data, and then re-restore the same log file to a different point-in-time in stand-by mode. I can redo this several times until I find the right point. When I find the right point, I can just leave it in stand-by mode to complete the recovery, or I can recover the database at the point it is at.

The two options of the RESTORE command that you will need to know are STANDBY and STOPAT. The syntax for the two options are as below.

STANDBY = ‘<Path and Name of Undo File>’
STOPAT = ‘<Date and time you want to restore to>’

The path for the undo file must already exist. It will create the file, but not the path. The date and time to stop at must be within the time frame covered by the log backup. If you specify a time that occurs after the time frame of the log backup, it will restore to the end of the file. If you specify a time prior to the log backup, it will return an error.

Example

Let’s consider a scenario where data was accidentally deleted from the DataDeletedDB database sometime between 2:00 AM and 2:30 AM, and we need to recover the deleted data. The backup sequence is that a full backup was performed at midnight and a log backup has been performed every half hour since then. This is the path I would take to recover the data.

-- Restore the full backup with a different name to find the missing data
Restore Database DataDeletedDB_old
    From Disk = = 'C:\bak\DataDeletedDB_201103010000.bak'
    With NoRecovery,
        Move 'DataDeletedDB' To 'C:\bak\DataDeletedDB_old.mdf',
        Move 'DataDeletedDB_log' To 'C:\bak\DataDeletedDB_old_log.ldf'

-- All we know is that the data was deleted sometime between 2 AM and 2:30 AM
-- Restore all log backups up to 2 AM using NoRecovery
Restore Log DataDeletedDB_old
    From Disk = 'C:\bak\DataDeletedDB_201103010030.trn'
    With NoRecovery;
    
Restore Log DataDeletedDB_old
    From Disk = 'C:\bak\DataDeletedDB_201103010100.trn'
    With NoRecovery;
    
Restore Log DataDeletedDB_old
    From Disk = 'C:\bak\DataDeletedDB_201103010130.trn'
    With NoRecovery;
    
Restore Log DataDeletedDB_old
    From Disk = 'C:\bak\DataDeletedDB_201103010200.trn'
    With NoRecovery;

-- Restore last log backup to some point and test to see if missing data is present
Restore Log DataDeletedDB_old
    From Disk = 'C:\bak\DataDeletedDB2.trn'
    With Standby = 'c:\bak\undo.ndf',
    StopAt = '3/1/2011 2:20:00 AM';

-- Query for existence of the data.
-- For this example, we'll say that the data is not present.

-- Restore last log backup to an earlier point and test to see if missing data is present
Restore Log DataDeletedDB_old
    From Disk = 'C:\bak\DataDeletedDB2.trn'
    With Standby = 'c:\bak\undo.ndf',
    StopAt = '3/1/2011 2:15:00 AM';

-- Query for existence of the data.
-- For this example, we'll say that the data is present.

-- Restore last log backup to an later point and test to see if missing data is present
Restore Log DataDeletedDB_old
    From Disk = 'C:\bak\DataDeletedDB2.trn'
    With Standby = 'c:\bak\undo.ndf',
    StopAt = '3/1/2011 2:18:00 AM';

-- Query for existence of the data.
-- For this example, we'll say that the data is not present.

-- Restore last log backup to an earlier point and test to see if missing data is present
Restore Log DataDeletedDB_old
    From Disk = 'C:\bak\DataDeletedDB2.trn'
    With Standby = 'c:\bak\undo.ndf',
    StopAt = '3/1/2011 2:17:00 AM';

-- Query for existence of the data.
-- For this example, we'll say that the data is present.
-- This may be close enough or we can start moving in increments of seconds to get even closer