The Next step is to populate the Product table in the PointInTimeRecovery database with the data available in AdventureWorks databases Production.Product table. Full and transaction log database backups will be taken at periodic intervals in the below mentioned steps. In the below scripts we will be taking compressed database backups utilizing the Database Compression Feature of SQL Server 2008. To know more details related to the Database Compression Feature of SQL Server 2008 you can refer to the article titled “How to configure and Use Database Backup Compression in SQL Server 2008”.
/* Insert ProductID’s in the range of 800 AND 849 in Product Table */
INSERT INTO dbo.Product
SELECT * FROM AdventureWorks.Production.Product where ProductID Between 800 AND 849
GO
/* Full Database Backup */
BACKUP DATABASE PointInTimeRecovery
TO DISK = N’C:DatabaseBackupsPointInTimeRecovery_FULL.BAK’
WITH COMPRESSION
GO
/* Insert ProductID’s in the range of ProductID 850 AND 899 in Product Table */
INSERT INTO dbo.Product
SELECT * FROM AdventureWorks.Production.Product where ProductID Between 850 AND 899
GO
/* First Transaction Log Backup */
BACKUP LOG PointInTimeRecovery
TO DISK = N’C:DatabaseBackupsPointInTimeRecovery_LOG1.TRN’
WITH COMPRESSION
GO
/* Insert ProductID’s in the range of ProductID 900 AND 949 in Product Table */
INSERT INTO dbo.Product
SELECT * FROM AdventureWorks.Production.Product where ProductID Between 900 AND 949
GO
/* Second Transaction Log Backup */
BACKUP LOG PointInTimeRecovery
TO DISK = N’C:DatabaseBackupsPointInTimeRecovery_LOG2.TRN’
WITH COMPRESSION
GO
/* Insert ProductID’s in the range of ProductID 950 AND 999 in Product Table */
INSERT INTO dbo.Product
SELECT * FROM AdventureWorks.Production.Product where ProductID Between 950 AND 999
GO
/* Product Table Dropped by the User*/
DROP TABLE dbo.Product
GO
/* Tail Log Backup */
BACKUP LOG PointInTimeRecovery
TO DISK = N’C:DatabaseBackupsPointInTimeRecovery_TAIL_LOG.TRN’
WITH NO_TRUNCATE, COMPRESSION
GO
The Timestamp at which different above mentioned activities happened in PointInTimeRecovery database is captured in the below table for your reference. This will help you understand at what time database backups were performed and when you are performing a point in time recovery then what should be the timestamp till
which the database should be restored.
As per the above table the disaster happened at 10/22/2008 00:06:52 AM timestamp when the Product table was dropped by the user. So in order to achieve the point in time recovery we need to recover PointInTimeRecovery database to a timestamp 10/22/2008 00:06:50 AM.