In this fast moving world, data is the heart and soul of any enterprise. It has become an essential task for organizations around the world to protect their data. Database Administrators have a tough job to implement database backup and disaster recovery plans. Backing up your databases can protect an organization against the accidental loss of data, database corruption, hardware/operating system crashes or any natural disasters. Unfortunately if you don’t have proper database backups then you are left with nothing to fall back on. As a DBA you need to make sure that the databases are backed up regularly and the backup tapes are stored in a secure location. SQL Server Database Administrators have a tough job at their hand when they decide to restore a corrupted SQL Server uses transaction logs to achieve point in time recovery. In this article you will see the steps which are needed to be performed by the DBA to achieve point in time recovery for a database.
Point in Time Recovery in SQL Server
In SQL Server, point in time recovery means restoring the SQL Server Database using Transaction logs to a specified date and time. Once you have completed the point in time recovery, your SQL Server database will be in the same state as it was during the time which was specified by the DBA while restoring the database using transaction log backup. This is a very tedious process to recover a database to a specific time as DBA needs to be very sure of the exact time when the disaster had struck. In order to achieve a point in time recovery your database should be in FULL database recovery model. To know more details related to the different database recovery models available in SQL Server, you can refer to the article titled “Database Recovery Models in SQL Server”. In case of any disaster the first thing that DBA should do is to take the tail log backup of the database. This will help you to ensure that all the changed records after the last successful backup are available while you perform the point in time database recovery.
How to Perform a Point in Time Recovery
Let us see an example to know how you can perform a point in time recovery for a database. The initial step will be to create a database named PointInTimeRecovery. Once the database is created successfully you will be creating a tabled named Product and then you will be populating the table with the data available in AdventureWorks databases Production.Product table. The data will be populated in four steps and we will be taking FULL and Transaction log backups at regular intervals. In the end the Product table will be dropped and thereafter a tail-log backup is taken. Our aim is to restore the database using full and transaction log backups which we have taken and perform a point in time recovery of the database to the time just before the table was dropped.
/* Create [PointInTimeRecovery] Database */
USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N’PointInTimeRecovery’)
DROP DATABASE [PointInTimeRecovery]
GO
USE [master]
GO
CREATE DATABASE [PointInTimeRecovery] ON PRIMARY
(
NAME = N’PointInTimeRecovery’,
FILENAME = N’C:DatabaseFilesPointInTimeRecovery.mdf’ ,
SIZE = 10240KB ,
MAXSIZE = 100MB,
FILEGROWTH = 1MB
)
LOG ON
(
NAME = N’PointInTimeRecovery_Log’,
FILENAME = N’C:DatabaseFilesPointInTimeRecovery_Log.ldf’ ,
SIZE = 5120KB ,
MAXSIZE = 50MB ,
FILEGROWTH = 512KB
)
GO
The next step will be to create a Product table with in the PointInTimeRecovery database.
/* Create Product Table */
USE [PointInTimeRecovery]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Product]’) AND type in (N’U’))
DROP TABLE [dbo].[Product]
GO
CREATE TABLE [dbo].[Product](
[ProductID] [int] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[ProductNumber] [nvarchar](25) NOT NULL,
[MakeFlag] [bit] NOT NULL,
[FinishedGoodsFlag] [bit] NOT NULL,
[Color] [nvarchar](15) NULL,
[SafetyStockLevel] [smallint] NOT NULL,
[ReorderPoint] [smallint] NOT NULL,
[StandardCost] [money] NOT NULL,
[ListPrice] [money] NOT NULL,
[Size] [nvarchar](5) NULL,
[SizeUnitMeasureCode] [nchar](3) NULL,
[WeightUnitMeasureCode] [nchar](3) NULL,
[Weight] [decimal](8, 2) NULL,
[DaysToManufacture] [int] NOT NULL,
[ProductLine] [nchar](2) NULL,
[Class] [nchar](2) NULL,
[Style] [nchar](2) NULL,
[ProductSubcategoryID] [int] NULL,
[ProductModelID] [int] NULL,
[SellStartDate] [datetime] NOT NULL,
[SellEndDate] [datetime] NULL,
[DiscontinuedDate] [datetime] NULL,
[rowguid] [uniqueidentifier] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
GO