Question: If my database has crashed on Wednesday 04:48 PM, how will you recover the database to 04:40 PM.?
Answer: You may want to restore and recover a database to a specific point in time. The first step is to take a tail log backup of the database if a t-log file is available. Now you will select the last full backup available, then you will select the last differential backup available after the last full backup and then you will restore all t-; log backups from the last differential back until 04:48 PM t-log backup. Then you will restore trail log backups until 04:40 PM using the stop at command. We can use the following process in SQL Server 2005, 2008, 2008r2 and 2012.
While restoring a database to a point within the backup you will see the following four types of operations:
- Set up script and backup database
- Restore the database to a point in time
- Clean up database
- Set up script and backup database
Backup database
Step 1: Create Database
CREATE DATABASE Testing
GO
Step 2: Ensure database is in full recovery
ALTER DATABASE Testing
SET RECOVERY FULL
GO
Step 3: Choose Database
USE Testing
GO
Step 4: Create Table
CREATE TABLE Test
(
ID INT,
[Name] CHAR(10)
)
GO
Step 5: Taking full backup
BACKUP DATABASE [Testing] TO DISK = N'D:\backup\Testing.bak'
GO
Step 6: INSERT INTO table
INSERT INTO Test
(ID,
[Name])
VALUES (1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E')
GO
Step 7: Selecting the data from Test
SELECT *
FROM Test
GO
Step 8: Taking differential backup
BACKUP DATABASE [Testing] TO DISK = N'D:\backup\Testing1.dif'
WITH DIFFERENTIAL
GO
Step 9: Insert into table
INSERT INTO Test
(ID,
[Name])
VALUES (6,'F'),(7,'I')
Step 10: Taking log backup
BACKUP LOG [Testing] TO
DISK = N'D:\backup\Testing1.trn'
GO
Step 11: Marking Time Stamp
SELECT GETDATE() BeforeTruncateTime;
-- Selecting the data from Test
SELECT *
FROM Test
GO
-- Quick Delay before Truncate
WAITFOR DELAY '00:00:01'
GO
Step 12: Truncate table
TRUNCATE TABLE Test
GO
-- Quick Delay after Truncate
WAITFOR DELAY '00:00:01'
GO
-- Marking Time Stamp
SELECT GETDATE() AfterTruncateTime;
-- Selecting the data from Test
SELECT *
FROM Test
GO
- Restore the database to a point in time
Restore database
Step 13: Choose master database
USE [master]
GO
-- Taking tail log
BACKUP LOG Testing TO
DISK = N'D:\backup\Testing2.trn'
WITH NORECOVERY
GO
Step 14: Restore full backup
RESTORE DATABASE Testing
FROM DISK = N'D:\backup\Testing.bak'
WITH
STANDBY = N'D:\backup\Testing.bak'
GO
You will see the following if your database is in standby (read-only) mode.
Step 15: Restore differential backup
RESTORE DATABASE Testing
FROM DISK = N'D:\backup\Testing1.dif'
WITH
STANDBY = N'D:\backup\Testing1.dif'
GO
Step 16: Selecting the data from Test
SELECT *
FROM testing..Test
GO
Step 17: Restore transaction backup
RESTORE LOG Testing
FROM DISK = N'D:\backup\Testing1.trn'
WITH STOPAT = '2013-09-28 04:56:26.093', -- Insert Your Time
STANDBY = N'D:\backup\Testing1.trn'
GO
Step 18: Rolling database forward
RESTORE LOG Testing
WITH RECOVERY
GO
You will see the following if your database is in recovery mode.
SELECT *
FROM testing..Test
GO
Step 19: Clean up
USE MASTER
GO
ALTER DATABASE testing
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE testing
GO