USE master
GO

CREATE DATABASE sampledatabase
GO

ALTER DATABASE sampledatabase SET RECOVERY FULL

USE sampledatabase
GO

CREATE TABLE SampleData
(
id INT PRIMARY KEY ,
col1 tinyint NOT NULL ,
col2 BIT NOT NULL ,
col3 BIT NOT NULL ,
RegistrationDate DATETIME NOT NULL
)

GO

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94495, 25, 1, 1, '02-03-2008')

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94496, 26, 1, 1, '02-04-2008')

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94497, 27, 1, 1, '02-05-2008')

BACKUP DATABASE sampledatabase TO DISK ='C:\20462\sampledata_full.bak' WITH INIT,STATS;
GO

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94498, 29, 1, 1, '02-03-2009')

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94499, 28, 1, 1, '02-04-2009')

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94500, 30, 1, 1, '02-05-2009')

SELECT * FROM sampledata

BACKUP DATABASE sampledatabase TO DISK = 'C:\20462\sampledata_diff.bak' WITH DIFFERENTIAL
GO

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94501, 29, 1, 1, '02-03-2010')

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94502, 28, 1, 1, '02-04-2010')

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94503, 30, 1, 1, '02-05-2010')

BACKUP LOG sampledatabase TO DISK ='C:\20462\sampledata_Log1.bak' WITH INIT,STATS;
GO

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94505, 29, 1, 1, '02-08-2010')

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94506, 28, 1, 1, '02-09-2010')

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94507, 30, 1, 1, '12-05-2010')

BACKUP LOG sampledatabase TO DISK ='C:\20462\sampledata_Log2.bak' WITH INIT,STATS;
GO

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94508, 29, 1, 1, '02-28-2011')

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94510, 28, 1, 1, '02-27-2011')

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94511, 30, 1, 1, '12-12-2011')

BACKUP LOG sampledatabase TO DISK ='C:\20462\sampledata_Log3.bak' WITH INIT,STATS;
GO

 select * from dbo.SampleData

INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (100000, 30, 1, 1, '12-02-2012')

SELECT TOP 5*
FROM sampledata
ORDER BY id DESC

USE master
GO

ALTER DATABASE sampledatabase SET OFFLINE WITH NO_WAIT;

--simulate the disaster by deleting the .mdf file

ALTER DATABASE sampledatabase SET ONLINE

--this will result in a nasty error since there is no database file

BACKUP LOG sampledatabase TO DISK ='C:\20462\sampledata_TailLog.bak' WITH INIT,NO_TRUNCATE;
GO

RESTORE DATABASE sampledatabase FROM DISK='C:\20462\sampledata_full.bak' WITH NORECOVERY, REPLACE
RESTORE DATABASE sampledatabase FROM DISK='C:\20462\sampledata_diff.bak' WITH NORECOVERY

RESTORE DATABASE sampledatabase FROM DISK='C:\20462\sampledata_Log1.bak' WITH NORECOVERY

RESTORE DATABASE sampledatabase FROM DISK='C:\20462\sampledata_Log2.bak' WITH NORECOVERY

RESTORE DATABASE sampledatabase FROM DISK='C:\20462\sampledata_Log3.bak' WITH NORECOVERY

RESTORE DATABASE sampledatabase FROM DISK='C:\20462\sampledata_TailLog.bak' WITH NORECOVERY

RESTORE DATABASE sampledatabase WITH RECOVERY

USE sampledatabase
GO

SELECT *
FROM sampledata
ORDER BY id DESC