SQL database Point in time recovery demonstration.

Here in this demonstration, I will show you point in time recovery of database. This approach is really helpful when someone delete records accidentally and we need to restore database before the data deletion.

Before executing backup OR restore activities on SQL server we should understand the basis of backup type and backup strategy.

A Full database backups represent the whole database at the time the backup finished.

Differential database backups contain only changes made to the database since its most recent full database backup. A differential partial backup records only the data extents that have changed in the database since the previous partial backup, known as the base for the differential.

A Backup of transaction logs that includes all log records that were not backed up in a previous log backup.

Full database backup usually taken on weekly basis mostly on weekends during off production hours, differential backups are taken on every day after production hours and transaction logs are taken after every 15 minutes depending on environmental need.

You can copy/paste following script in test SQL environment and run one by one by understanding the operation mentioned in the comment.

-- Create a database called 'BackupRestore’, make sure we have recovery mode set to 'FULL’
USE [master]
CREATE DATABASE [BackupRestore]
GO
ALTER DATABASE [BackupRestore] SET RECOVERY FULL
GO

-- Create table that holds 3 columns.  
Use [BackupRestore]
Create Table EmployeeCity
(
ID INT
IDENTITY(1,1),
NAME VARCHAR
(100),
City VARCHAR(100)
)
GO

-- Insert first three rows. 
Use [BackupRestore]
INSERT INTO [BackupRestore] Values
('Pradeep', 'Pune'),
('Darshan', 'Goa'),
('vikram','Talegon')
GO

--Take Full back up now. 
--In production this is done by backup scripts OR third party application such as VERITAS BackupExec
OR NetBackup. 
 
BACKUP DATABASE [BackupRestore]
TO DISK = N'D:\BackupRestoreTest\BackupRestore.bak'
GO 

-- Insert two more records. 
Use [BackupRestore] 
INSERT INTO EmployeeCity Values
('Ajay', 'Nashik'),
('Yogesh', 'Solapur')
GO

-- Take differential backup.
BACKUP DATABASE [BackupRestore]
TO DISK = N'D:\BackupRestoreTest\BackupRestoreDiff.Bak' WITH  DIFFERENTIAL
GO

-- Insert one more records.
USE [BackupRestore]
INSERT INTO EmployeeCity Values
('Vaishali K', 'Bengaluru')
GO
-- Take transaction log backup. 
BACKUP LOG [BackupRestore] TO  DISK = N'D:\BackupRestoreTest\BackupRestoreTran1.trn'
GO
 
-- Return all rows from table. 
USE [BackupRestore]
SELECT ID,CITY, NAME FROM EmployeeCity
GO
 
---Suppose someone delete three rows accidentally. 
DELETE FROM EmployeeCity
WHERE City in ('Bengaluru', 'Solapur','PUNE')
GO
 
USE [BackupRestore]
SELECT ID,CITY, NAME FROM EmployeeCity
GO
 
-- Before restoring from backup, a database needs to go in single user mode. 
USE [master]
ALTER DATABASE [BackupRestore] SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
 
-- Take Tail log backup of current situation. The tail-log backup captures records on the transaction log that were written since the last transaction log backup. If you're going to restore a database to the point of failure, then you need to
take a tail-log backup before you start the restore operation. 

BACKUP LOG
[BackupRestore] TO  DISK = N'D:\BackupRestoreTest\BackupRestoreTran2.trn'
WITH NORECOVERY
GO

-- Restore database using last full backup and keep it with 'NORecovery' that tells SQL Server thatyou are not finished restoring the database and that subsequent restore files will occur. While the database is in this state, the database is not yet
available, so no connections are allowed. 

USE [master]
RESTORE DATABASE [BackupRestore]
FROM DISK = N'D:\BackupRestoreTest\BackupRestore.bak'
WITH  NORECOVERY
GO

-- Restore database using last differential backup. 
RESTORE DATABASE [BackupRestore]
FROM DISK = N'D:\BackupRestoreTest\BackupRestoreDiff.Bak'
WITH NORECOVERY
GO
-- Now all the transaction logs needs to be restored those backup after differential backup those were created before deletion query. We don't need to mentioned 'NOrecovery' as there is no logs to restored and after transaction log restore
we need to make database online. 

RESTORE LOG [BackupRestore] FROM  DISK = N'D:\BackupRestoreTest\BackupRestoreTran1.trn'
-- STOPAT= '08/08/2016 12:12:55′
GO

-- Now if we execute select query then all rows will return those were present before deletion query. 
USE [BackupRestore]
SELECT ID,CITY, NAME FROM EmployeeCity
GO

-- Once you complete demonstration then drop 'BackupRestore' database. 
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'BackupRestore'
USE [master]
ALTER DATABASE [BackupRestore] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

USE [master]
DROP DATABASE [BackupRestore]

 

Please note:-

All content provided on this blog is for informational purposes only. The reason for this blog is to document the things that I have learned reading blogs/books/self about Windows Server. We do not own any responsibility for the events that may happen because of using/applying the code/concepts mentioned in this blog.
If you like above content then feel free to share it with your colleague  and friends. If you wish to improve the content the feel free to comment and contact me directly.

Advertisements

#backup, #restore, #sql-pointintimerecovery