ÿþUSE master;

DROP TABLE sampledb.dbo.sample;

CREATE TABLE sampledb.dbo.sample(msg nvarchar(100), itime datetime default getdate());



BEGIN TRANSACTION;

INSERT INTO sampledb.dbo.sample (msg) VALUES ('Transaction with the Full Backup');

COMMIT TRANSACTION;

WAITFOR DELAY '00:00:05';



BACKUP DATABASE [sampledb] TO DISK = N'C:\backups\sampledb\pitr_full.bkp' 

WITH FORMAT, INIT,  MEDIANAME = N'pitr_full',  NAME = N'pitr_full', SKIP, NOREWIND, NOUNLOAD;



BEGIN TRANSACTION

INSERT INTO sampledb.dbo.sample (msg) VALUES ('Transaction within the 1st Log Backup');

COMMIT TRANSACTION;

WAITFOR DELAY '00:00:05';



BACKUP LOG [sampledb] TO DISK = N'C:\backups\sampledb\pitr_tlog.bkp' 

WITH FORMAT, INIT,  MEDIANAME = N'pitr_txlog',  NAME = N'pitr_txlog', SKIP, NOREWIND, NOUNLOAD;



BEGIN TRANSACTION;

INSERT INTO sampledb.dbo.sample (msg) VALUES ('Transaction within the 2nd Log Backup');

COMMIT TRANSACTION;

WAITFOR DELAY '00:00:05';



BACKUP LOG [sampledb] TO DISK = N'C:\backups\sampledb\pitr_tlog.bkp' 

WITH MEDIANAME = N'pitr_txlog',  NAME = N'pitr_txlog', SKIP, NOREWIND, NOUNLOAD;



BEGIN TRANSACTION;

INSERT INTO sampledb.dbo.sample (msg) VALUES ('Point-in-time I want to recover to!');

COMMIT TRANSACTION;

WAITFOR DELAY '00:00:05';



BEGIN TRANSACTION;

INSERT INTO sampledb.dbo.sample (msg) VALUES ('Transaction within the 3rd Log Backup');

COMMIT TRANSACTION;

WAITFOR DELAY '00:00:05';



BACKUP LOG [sampledb] TO DISK = N'C:\backups\sampledb\pitr_tlog.bkp' 

WITH MEDIANAME = N'pitr_txlog',  NAME = N'pitr_txlog', SKIP, NOREWIND, NOUNLOAD;



BEGIN TRANSACTION;

INSERT INTO sampledb.dbo.sample (msg) VALUES ('After 3rd Transaction Log Backup');

COMMIT TRANSACTION;



USE msdb;

SELECT media_set_id,

       backup_set_id,

       position, 

       last_lsn,

       type,

       backup_start_date

FROM dbo.backupset where database_name='sampledb'

and name in (N'pitr_full',N'pitr_txlog');



USE sampledb;

SELECT * FROM sampledb.dbo.sample;