Real World
As a DBA, it is very important to be able recover a damaged database that caused with many factors. It can be caused by hardware (hard disk) failure, electricity problem, software bugs, and human error. Rather than hardware error, human error is #1 factor that could make database damage e.g: delete data accidentally, drop database, wrong update data etc.
Some situations need DBA could recovery the data until specified time before the database was damaged. There is some technics to make it possible but this time what we discuss is using database transaction log. What we need to do for the recovery is restore database from full database backup + differential database backup + transaction log backup.
DBA should review the DR (Disaster Recovery) strategy periodically. The nice DR strategy is when the disaster occurred, the recovery succeed recover data as much as possible. We need to try simulate the disaster and try to recover the data. It is important to make sure that our DR strategy procedure is already correct and not miss something.
Backup Strategy
Before I explain about the recovery, it is better that we know the backup strategy.
1. Full backup every week (usually on weekend)
| BACKUP DATABASE <database name> TO <backup device> |
2. Differential backup every day
| BACKUP DATABASE <database name> TO <backup device> WITH DIFFERENTIAL |
3. Transaction Log backup every x hours / x minutes
| BACKUP LOG <database name> TO <backup device> |
Recovery Strategy
1. Restore Full Backup
2. Restore latest Differential Backup which is running after the full backup on step 1
e.g. : if full backup on Sunday Night and Diff backup everyday night , when the disaster occur on Thursday we just use the differential backup on Wednesday.
3. Restore Transaction Backup which is running after latest any database backup model
Restore log little bit difficult than just full and diff. We have to find the continuous transaction log with the latest backup. BACKUP LOG with option NO_LOG or TRUNCATE_ONLY should break the chain.
Statement for Restore from FULL and DIFFERENTIAL backup: RESTORE DATABASE <database_name> FROM <backup_device> Statement for restore TRANSACTION LOG backup: RESTORE LOG <database_name> FROM <backup_device> For further option, read SQL Server BOL. |
Note to RECOVERY / NORECOVERY option, use NORECOVERY option if there is another backup file that should be restored. Use RECOVERY if the final file backup have restored.
Requisite
Database recovery model should not be in SIMPLE mode. You can use ALTER DATABASE to change the recovery model. If you try to backup log database with SIMPLE recovery model, error should be occurred. Note if you use BULK_LOGGED recovery model, you cannot restore to the point in time if there are any bulk logged operations in transaction log.
Msg 4208, Level 16, State 1, Line 1
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
Disaster Recovery Scenario
As mention on backup strategy, we full backup every weekend, differential backup every night on weekdays and backup log every hours. Below script is illustration about the disaster where the DBA delete all data accidentally. DBA have to recovery the database with minimal loss data. We should recover the data to the point in time before the disaster happened.
Disaster Scenario
--1. Create Database DR Testing
CREATE DATABASE TestDR;
GO
--2. Active on TestDR database
USE TestDR
GO
--Recovery model should be FULL
ALTER DATABASE TestDR SET RECOVERY FULL
GO
--3. Create Table
CREATE TABLE Orders
(
Code INT PRIMARY KEY,
Timestamp DATETIME
)
GO
--4. Assume there are data already exists in this table
INSERT INTO dbo.Orders VALUES (1, '3 Jan 2009 09:00:00.000')
INSERT INTO dbo.Orders VALUES (2, '3 Jan 2009 12:00:00.000')
INSERT INTO dbo.Orders VALUES (3, '3 Jan 2009 16:00:00.000')
GO
--5. Full backup run every sunday on 4 Jan 2009 to C:\temp\TestDR_Full.bak
BACKUP DATABASE TestDR TO DISK = 'C:\temp\BK_Full_090104.bak'
WITH DESCRIPTION = 'Full Backup ON 4 Jan 2009', NAME = 'Full Backup'
GO
--6. Transactions on 5 Jan 2009 and BACKUP LOG every hours
INSERT INTO dbo.Orders VALUES (4, '5 Jan 2009 07:45:00.000')
GO
BACKUP LOG TestDR TO DISK = 'C:\temp\BK_Log_090105.bak'
WITH DESCRIPTION = 'Log Backup ON 5 Jan 2009 08:00:00.000', NAME = 'Log Backup'
GO
INSERT INTO dbo.Orders VALUES (5, '5 Jan 2009 11:30:00.000')
GO
BACKUP LOG TestDR TO DISK = 'C:\temp\BK_Log_090105.bak'
WITH DESCRIPTION = 'Log Backup ON 5 Jan 2009 12:00:00.000', NAME = 'Log Backup'
GO
--7. Backup Diff after office hours
BACKUP DATABASE TestDR TO DISK = 'C:\temp\BK_DIFF_090105.bak'
WITH DIFFERENTIAL, DESCRIPTION = 'Diff Backup ON 5 Jan 2009 22:00:00.000'
,NAME = 'Diff Backup'
--8. Transactions on 6 Jan 2009 and BACKUP LOG every hours
INSERT INTO dbo.Orders VALUES (6, '6 Jan 2009 09:35:00.000')
GO
BACKUP LOG TestDR TO DISK = 'C:\temp\BK_Log_090106.bak'
WITH DESCRIPTION = 'Log Backup ON 6 Jan 2009 10:00:00.000', NAME = 'Log Backup'
GO
INSERT INTO dbo.Orders VALUES (7, '6 Jan 2009 13:10:00.000')
GO
BACKUP LOG TestDR TO DISK = 'C:\temp\BK_Log_090106.bak'
WITH DESCRIPTION = 'Log Backup ON 6 Jan 2009 14:00:00.000', NAME = 'Log Backup'
GO
INSERT INTO dbo.Orders VALUES (7, '6 Jan 2009 14:23:00.000')
GO
--9. 14.35 AM, DBA delete all data accidentally.
DELETE FROM dbo.Orders
|
Recovery Scenario
Before we restore the database, awhile we review what we have and need to do for recovery.
1. We need latest full backup. We have full backup at Jan 4th, 2009 on BK_Full_090104.bak
2. Since disaster occur on Tuesday Jan 6th, 2009 we have to apply latest differential backup which is on Jan 5th, 2009 on BK_DIFF_090105.bak
3. Last but not least is we also need apply the transaction log backup which is on Jan 6th, 2009 on BK_LOG_090106.bak. Since we backup the transaction log twice, we need to apply every file that we backup before we set the database to RECOVERY mode. If the transaction log backup file not in the chain, it should be show the error message like below
Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 27000000017500001, which is too early to apply to the database.
A more recent log backup that includes LSN 27000000019300001 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
--1. Restore from Full Backup With NORECOVERY option RESTORE DATABASE TestDR FROM DISK = 'C:\Temp\BK_FULL_090104.bak' WITH NORECOVERY GO --2. Restore from latest Diff Backup WITH NORECOVERY option RESTORE DATABASE TestDR FROM DISK = 'C:\Temp\BK_DIFF_090105.bak' WITH NORECOVERY GO --3. Restore from Transaction Log File 1 and File 2 RESTORE LOG TestDR FROM DISK = 'C:\Temp\BK_LOG_090106.bak' WITH NORECOVERY, File = 1 GO RESTORE LOG TestDR FROM DISK = 'C:\Temp\BK_LOG_090106.bak' WITH NORECOVERY, File = 2 GO --4. Use RECOVERY option so that the database is available again RESTORE DATABASE TestDR WITH RECOVERY GO |
 Recovery scenario above assume TestDR database is not exist anymore in the server (DROP DATABASE). If you want restore as another database, use MOVE option since we need change the filename for the restored database. Using RESTORE FILELISTONLY to get information about the physical and logical name of the backup device, |
Restore HeaderOnly
If you see the recovery scenario is quite simple, isn’t it? But if in the real world I believe the scenario more complex. To analyze it is possible or not to restore until the specified time, we can use RESTORE HEADERONLY statement. Restore HeaderOnly returns a result set containing all the backup header information for all backup sets on a particular backup device. With this information, you can decide the file # that you need to be restored. There is information about what time the backup was performed. This information is needed by DBA that want restore the data until specified time.
| RESTORE HEADERONLY FROM <backup device> |
Let’s examine the FirstLSN, LastLSN, and DatabaseBackupLSN column from above scenario.
DatabaseBackupLSN contains LSN (Log Sequence Number) of the most recent full database backup. In above picture, we can see that Differential backup and Transaction Log backup contain value FirstLSN of full database backup. It means our differential backup and transaction log backup in the same chain with the full database backup. So BK_DIFF_090105.bak could be restored after we restore the full database backup.
Now we check the transaction log backup file. Note the LastLSN of the differential backup. The value should be in the range between FirstLSN and LastLSN on transaction log. We can see 27000000019300001 in the range between 27000000017600001 and 27000000019400001 in transaction log file #1. So file #1 could be restored. How about file #2. Note the FirstLSN of file #2 should be as same as LastLSN of file #1. So file #2 of transaction log could be restored also.
BACKUP LOG WITH NO_LOG could make the chain of the transaction log break. The value between files in transaction log won’t be continuous (FirstLSN file N should be same with LastLSN file N-1 where N > 1).
DBA and SysAdmin World 