Asked By Amit
20-Nov-09 01:53 AM
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