Tail-log Backup without the Primary Data File

While I was reading a post, written by Vinod Kumar (blog | twitter) about Files and FileGroups with SQL Server, I found an interesting quiz question: “Do we need the primary data file available to backup your transaction log after a crash?“

The answer to the above question is No, if the transaction-log file is not damaged, then we can initiate a log backup to backup the data till the time of the crash, since last log backup. To support my answer, I have created the following test script. In the first part of the script, I have created a test database named “TestDB” and change its recovery model to “Full”. The recovery model of the model database on my SQL 2008 R2 Express instance is set as Simple, so I manually change the recovery model of TestDB to Full.

use [master]
go
if exists (select 1 from sys.databases where name = 'TestDB')
drop database TestDB;
go
-- Create database TestDB
use [master]
go
create database [TestDB] ON Primary
(name = 'TestDB_Data', filename = 'C:\projects\demodb\TestDB_Data.MDF',
size = 10MB, filegrowth = 10MB)
LOG ON
(name = 'TestDB_Log', filename = 'C:\projects\demodb\TestDB_Log.MDF',
size = 10MB, filegrowth = 10MB);
go
-- Changing the Recovery Model to full
use [master]
alter database [TestDB] set recovery full;
go

Since I have created the database, its time to initiate a full backup of the database. Next I will add a demo tables with two columns and inserted some data into the table. After verifying the data in the table, initiate the first transaction-log backup.

-- Initiate a Full backup of the database
use [master]
Backup database [TestDB] to disk = 'C:\projects\TestDB.bak'
go
-- Create a table in the database
use [TestDB]
create table dbo.demotable1 (
demoid int identity(1,1),
demodate datetime default getdate()
);
go
-- Insert base data
use [TestDB]
insert into dbo.demotable1 default values;
go 10
-- verify base data
select * from TestDB.dbo.demotable1;
go
-- Initiate the 1st T-log backup
use [master]
backup log [TestDB] to disk = 'c:\projects\testDB1.trn';
go

Since the first log backup is completed, lets add some additional data into the table in the TestDB database. After inserting the additional row into the table, I will verify the same.

-- Insert some additional data
use [TestDB]
insert into dbo.demotable1 default values;
go 15
-- verify all the data in the database
select COUNT(*) from TestDB.dbo.demotable1;
go
/*(total = 25, till log backup it was 10)*/

Now I am going to crash the TestDB database. I am going to shutdown my SQL Server Express instance and manually delete the data file of the TestDB database. Once the file is deleted, restart the SQL Server instance, and try to access data from the database. The following error will occur: “Database ‘TestDB’ cannot be opened due to inaccessible files or insufficient memory or disk space“

-- try to access the table, will get error as data file is missing
use [TestDB]
select COUNT(*) from TestDB.dbo.demotable1;
go

As I am unable to access the database, I need to backup the transaction log to retrieve the data, which was inserted into the database after the last transaction-log backup, to minimize the data loss. The syntax to initiate the tail-log backup is as follows:

-- Initiate the tail log backup
use [master]
BACKUP log [testDB] to disk = 'C:\Projects\TestDB_Tail_Log.trn'
with No_Truncate, Norecovery;
go
/* This complete the tail log backup without the primary data file */

If the transaction-log file of the database is not damaged, then the above command will create the log backup (also known as tail-log backup). This completes the answer to the given question; however, a backup is never successful, if it can’t be restored. So I am going to test, whether I can restore the database to the same point, when the crash occurred, using the available backups. The next part of the script will restore the database and verify the data in the database after the restoration is completed.

use [master]
Restore database [TestDB]
from disk = 'C:\projects\TestDB.bak'
with replace, norecovery;
go
Restore Log [TestDB]
from disk = 'c:\projects\testDB1.trn'
with replace, norecovery;
go
Restore Log [TestDB]
from disk = 'c:\projects\TestDB_Tail_Log.trn'
with recovery;
go
-- Verify the data
Select count(*) from TestDB.dbo.demotable1;
go

The database is recovered successfully using the available backups and the complete data is also recovered. If we place the data file and the t-log files in different drives, in case, if the drive containing the data file is damaged, then we can retrive the data if we are able to backup the t-log. However, if the drive containing the t-log files is crashed, then we are going to face some data loss.

The complete script to reproduce the above scenario is mentioned below:

/* Create Tail-log backup after a crash, if the primary data file is missing
------------------------------------------------------------------------------
@skganguly
*/
use [master]
go
if exists (select 1 from sys.databases where name = 'TestDB')
drop database TestDB;
go
-- Create database TestDB
use [master]
go
create database [TestDB] ON Primary
(name = 'TestDB_Data', filename = 'C:\projects\demodb\TestDB_Data.MDF',
size = 10MB, filegrowth = 10MB)
LOG ON
(name = 'TestDB_Log', filename = 'C:\projects\demodb\TestDB_Log.MDF',
size = 10MB, filegrowth = 10MB);
go
-- Changing the Recovery Model to full
use [master]
alter database [TestDB] set recovery full;
go
-- Initiate a Full backup of the database
use [master]
Backup database [TestDB] to disk = 'C:\projects\TestDB.bak'
go
-- Create a table in the database
use [TestDB]
create table dbo.demotable1 (
demoid int identity(1,1),
demodate datetime default getdate()
);
go
-- Insert base data
use [TestDB]
insert into dbo.demotable1 default values;
go 10
-- verify base data
select * from TestDB.dbo.demotable1;
go
-- Initiate the 1st T-log backup
use [master]
backup log [TestDB] to disk = 'c:\projects\testDB1.trn';
go
-- Insert some additional data
use [TestDB]
insert into dbo.demotable1 default values;
go 15
-- verify all the data in the database
select COUNT(*) from TestDB.dbo.demotable1;
go
/*(total = 25, till log backup it was 10)*/
-- initiate the crash, for this demo, I've shutdown the server and
-- delete the data file manually, to verify whether the tail-log backup is
-- possible or not if the data file is not available
-- try to access the table
use [TestDB]
select COUNT(*) from TestDB.dbo.demotable1;
go
-- Database 'TestDB' cannot be opened due to inaccessible files
-- or insufficient memory or disk space
/* Initiate the tail log backup*/
use [master]
BACKUP log [testDB] to disk = 'C:\Projects\TestDB_Tail_Log.trn'
with No_Truncate, Norecovery;
go
/* This complete the tail log backup without the primary data file */
/* The script will not be successful, if we can't restore the tail log.
Thus, we are going to restore the database and verify the data before cleanup */
use [master]
Restore database [TestDB]
from disk = 'C:\projects\TestDB.bak'
with replace, norecovery;
go
Restore Log [TestDB]
from disk = 'c:\projects\testDB1.trn'
with replace, norecovery;
go
Restore Log [TestDB]
from disk = 'c:\projects\TestDB_Tail_Log.trn'
with recovery;
go
-- Verify the data
Select count(*) from TestDB.dbo.demotable1;
go
-- clean up the database
use [master]
drop database [TestDB]
go

=======================================
Thanks To :- http://sudeeptaganguly.wordpress.com/2011/04/15/taillogbackupwithoutdatafile/