Tail-log backup not happening

Hi,

I have a production database where tail log backup is not happening. In
order to understand the tail log backup concept better, I
reproduced/simulated the situation as mentioned below, but still tail log
backup was not working. I think I might be missing something in this small
example:

-- I created a test database (database is in full recovery mode)
CREATE DATABASE Test
GO

USE Test
GO

-- I created a test table called employee

CREATE TABLE dbo.Employee(
EmpID int Primary Key NOT NULL,
EmpName nvarchar(100) NOT NULL,
EmpEmail nvarchar(100) NOT NULL)


-- I inserted a record
INSERT INTO dbo.Employee
values (1, N'Amit1', N'test@email.com')
GO

-- add a backup device

EXEC sp_addumpdevice 'disk', 'mydiskdump', 'C:\TestBackup.bak'

-- took a full back up

BACKUP DATABASE test to mydiskdump

-- added another record

INSERT INTO dbo.Employee
values (2, N'Amit2', N'test@email.com')
GO


-- took a differential backup

BACKUP DATABASE test to mydiskdump
with differential


-- added another record

INSERT INTO dbo.Employee
values (3, N'Amit3', N'test@email.com')
GO

-- took a transaction log backup

BACKUP LOG Test TO mydiskdump

-- add another record

INSERT INTO dbo.Employee
values (4, N'Amit4', N'test@email.com')
GO


--- now I shut down sql server
--- then I simulated a crash (simply corrupted the mdf file using a hex
editor), so my log file is undamaged.
-- started sql server again.
-- the database is in suspect mode now

---------------------
-- took tail log backup
----------------------

BACKUP LOG Test TO mydiskdump
WITH NO_TRUNCATE

-- I m using with no_truncate beucase the mdf file is damaged. I hope this
is the right step. without No_truncate, I get error.


-- now when I try to restore the database from the backup using sql server
management studio, my back up device shows me 1 full backup, 1 differential
backup, & 1 transaction log backup. it is not showing the tail-log backup.
when I restore all of it, I get all the records, except the last record (the
4th record).

if I am missing something here, please assist. Thanks.

Regards
Amit

Hi Friends,My issue seems to be getting resolved. Thanks.RegardsAmit

Hi Friends,

My issue seems to be getting resolved. Thanks.

Regards
Amit

The tool does not show you the backups done using NO_TRUNCATE (which was

The tool does not show you the backups done using NO_TRUNCATE (which was the
right option). So you understood the concept, but was confused by a somewhat

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Hi Tibor,Thanks for the information. Your are right.

Hi Tibor,

Thanks for the information. Your are right. The restore works with the
script. Thanks for your help here.

Regards
Amit