Taking tail-log backup what if the database is inaccessible

A tail-log backup is that a log backup that backs up the tail of the transaction log that have not yet backed up. In a event of database disaster, data loss cannot be prevented if tail-log backup cannot be taken. However, if we can back up the tail of the log, we may suffer minimal data or even no data loss. For more information about tail-log backup, see “Tail-Log Backups” (http://msdn.microsoft.com/en-us/library/ms179314.aspx ) in Books Online.

To be able to take tail-log backup, the following must be true:

  1. The database is in FULL or BULK-LOGGED recovery model, and at least one full backup was taken.
  2. The log file is not damaged
  3. If data files are damaged, make sure
  4. If in BULK-LOGGED recovery model, there is no minimally logged operations after the last log backup.

To take a tail log backup using the T-SQL statement, like below:

-- If the database is online and begin to restore the database after backup
BACKUP LOG [database_name] TO [backup_device] WITH NORECOVERY
-- If the database is offline, and log file may be damaged
BACKUP LOG [database_name] TO [backup_device] WITH CONTINUE_AFTER_ERROR

Real-world scenario:

In a event of database disaster, our database is inaccessible now, data files (not log file) are damaged so that if we could take tail-log backup we should prevent no data loss in this case. I will walk you through on how to do it.

  1. Create a sample database to paly with, set the recovery model to full and take the 1st full backup:
    	USE master;
    	GO
    	CREATE DATABASE TailLogBackupDemo;
    	GO
    	ALTER DATABASE TailLogBackupDemo SET recovery FULL;
    	GO   USE TailLogBackupDemo;
    	GO
    	CREATE TABLE dbo.TestTable
    	(
    		ID INT NOT NULL IDENTITY(1,1),
    		FirstName NVARCHAR(20) NOT NULL,
    		LastName NVARCHAR(20) NOT NULL
    	);
    	GO
    	BACKUP DATABASE TailLogBackupDemo
    	TO DISK = N'D:\Workspace\SQLServer\Backups\TailLogBackupDemo_Full.bak';
    
    GO
    
  2. Insert some new records in our sample table and take a transaction log backup.
    	-- Insert 5 records in our table and then peform our 1st tranasction log backup
    	INSERT INTO dbo.TestTable(FirstName, LastName) VALUES('Chunsong01', 'Feng');
    	INSERT INTO dbo.TestTable(FirstName, LastName) VALUES('Chunsong02', 'Feng');
    	INSERT INTO dbo.TestTable(FirstName, LastName) VALUES('Chunsong03', 'Feng');
    	INSERT INTO dbo.TestTable(FirstName, LastName) VALUES('Chunsong04', 'Feng');
    	INSERT INTO dbo.TestTable(FirstName, LastName) VALUES('Chunsong05', 'Feng');
    	GO
    	BACKUP LOG TailLogBackupDemo
    	TO DISK = N'D:\Workspace\SQLServer\Backups\TailLogBackupDemo_Log_1st.trn';
    	GO
    
  3. Insert another new records in our sample table.
    	-- Insert anotehr 5 records in our table and then peform our 1st tranasction log backup
    	INSERT INTO dbo.TestTable(FirstName, LastName) VALUES('Chunsong06', 'Feng');
    	INSERT INTO dbo.TestTable(FirstName, LastName) VALUES('Chunsong07', 'Feng');
    	INSERT INTO dbo.TestTable(FirstName, LastName) VALUES('Chunsong08', 'Feng');
    	INSERT INTO dbo.TestTable(FirstName, LastName) VALUES('Chunsong09', 'Feng');
    	INSERT INTO dbo.TestTable(FirstName, LastName) VALUES('Chunsong10', 'Feng');
    	GO
    
  4. Now, database disaster happens.
      1. Detach the database and delete all data files
      2. Copy log file to somewhere else
  5. Steps to take tail-log backup in this scenario.
    	USE master;
    	GO
    	-- Create a new dummy database used to backup our tail log
    	CREATE DATABASE NewTailLogBackupDemo;
    	GO
    	-- This is required since we need to take log backup
    	ALTER DATABASE NewTailLogBackupDemo SET recovery FULL;
    	GO
    	-- Set the database offline, and then delete all data and log files
    	-- And then copy original log file (in step4) rename the same name as
    	-- NewTailLogBackupDemo log file
    	ALTER DATABASE NewTailLogBackupDemo SET offline;
    	GO
    	-- Bring the database online (Although error happens here, it does not matter)
    	ALTER DATABASE NewTailLogBackupDemo SET ONLINE;
    	GO
    	-- Take the tail-log backup
    	BACKUP LOG NewTailLogBackupDemo
    	TO DISK = N'D:\Workspace\SQLServer\Backups\TailLogBackupDemo_tail_log.trn' 
    	WITH INIT, NO_TRUNCATE;
    	GO 
    	USE master;
    	GO
    	-- Drop dummy database, make sure the physical files are dropped
    	DROP DATABASE NewTailLogBackupDemo;
    	GO
    	-- With tail-log backup, we can start to restore the database
    	RESTORE DATABASE TailLogBackupDemo
    	FROM DISK = N'D:\Workspace\SQLServer\Backups\TailLogBackupDemo_Full.bak'
    	WITH REPLACE, NORECOVERY;
    	GO
    	RESTORE DATABASE TailLogBackupDemo
    	FROM DISK = N'D:\Workspace\SQLServer\Backups\TailLogBackupDemo_Log_1st.trn'
    	WITH NORECOVERY;
    	GO
    	RESTORE DATABASE TailLogBackupDemo
    	FROM DISK = N'D:\Workspace\SQLServer\Backups\TailLogBackupDemo_tail_log.trn'
    	WITH RECOVERY;
    	GO
    	-- Check if all data are recovered
    	USE TailLogBackupDemo;
    	SELECT * FROM dbo.TestTable;
    	GO
    

Now, we should see all data are recovered. Please always try our best to take tail-log backup in any disaster to avoid data loss as much as possible.

Advertisements

About Alex Feng

SQL Server DBA in Alibaba Cloud Computing
This entry was posted in Uncategorized and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s