Close

SQL Server DBA Tutorial 1 - How to Install SQL Server 2014 step by step in Windows System

SQL Server DBA Tutorial 2 - How to Create Database in SQL Server

SQL Server DBA Tutorial 3 - What is Database Backup How Many Types of Backups available

SQL Server DBA Tutorial 4 - How to Manually Take Full Backup of a Database in SQL Server

SQL Server DBA Tutorial 5 - How to Restore a Database From Full Backup in SQL Server

SQL Server DBA Tutorial 6 - How to Manually Take Differential backup of a Database in SQL Server

SQL Server DBA Tutorial 7 - How to Restore a Database from Differential Backup in SQL Server

SQL Server DBA Tutorial 8 - How to Manually Take Transaction Log backup of a database in SQL Server

SQL Server DBA Tutorial 9 - How to Restore a Database to Specific Time Point in Time in SQL Server

SQL Server DBA Tutorial 10 - How to Create Backup Maintenance Plan in SQL Server

SQL Server DBA Tutorial 11 - How to Schedule Auto Databases Backup in SQL Server

SQL Server DBA Tutorial 12 - How to Cleanup Old Backups in SQL Server

SQL Server DBA Tutorial 13 - How to Rename a Database in SQL Server

SQL Server DBA Tutorial 14 - Create Database Best Practices with SSMS

SQL Server DBA Tutorial 15 - How to Create Database Snapshot in SQL Server

SQL Server DBA Tutorial 16 - Manually Take Tail Log Transaction Log backup of a Database in SQL Server

SQL Server DBA Tutorial 17 - How to Attach and Detach Databases in SQL Server

SQL Server DBA Tutorial 18 - How to Script out an Entire Database in SQL Server

SQL Server DBA Tutorial 19 - How to Take Database Offline and Bring it Online in SQL Server

SQL Server DBA Tutorial 20 - How to compare these two databases and find what is the difference between these two databases

SQL Server DBA Tutorial 21 - How to configure Database Mail in SQL Server

SQL Server DBA Tutorial 22 - How to Setup the Email Notificaion For Job Failure on SQL Server

SQL Server DBA Tutorial 23 - What is replication Types of replication and when to use each type

SQL Server DBA Tutorial 24 - Why do we use Replication Provide couple scenarios

SQL Server DBA Tutorial 25 - How to Configure Distribution in SQL Server Replication

SQL Server DBA Tutorial 26 - How to Create Snapshot Replication in SQL Server

SQL Server DBA Tutorial 27 - How to Create Transactional Replication in SQL Server

SQL Server DBA Tutorial 28 - How to Create Merge Replication in SQL Server

SQL Server DBA Tutorial 29 - Overview of SQL Server Agent Configuration

SQL Server DBA Tutorial 30 - How to Create Job using SQL Server Agent

SQL Server DBA Tutorial 31 - Job Categories Overview in SQL Server Agent

SQL Server DBA Tutorial 32 - How to Create an Alert in SQL Server

SQL Server DBA Tutorial 33 - How to Create an Operator in SQL Server

🔰 SQL Server DBA Tutorial 9 - How to Restore a Database to Specific Time Point in Time in SQL Server 🔰


Point-in-time recovery allows to restore a database into a state it was in any point of time. This type of recovery is applicable only to databases that run under the full or bulk-logged recovery model.

Scenario :
Full Backup : 2:01 PM (Contains table, actor)
Differential Backup : 2:03 PM (Contains table, actor1,2)
Transacton Log Backup : 2:06 PM (Contains table, actor1,2,3,4,5)
Transacton Log Backup : 2:09 PM ------?
At : 2:08 PM (Someone delete Table actor4,5)

We will be using two ways to Restore a Database to Specific Time Point in Time in SQL Server
1. Using SQL Server management studio graphic user interface GUI Version
2. Using T-SQL script

/**T-SQL**/
USE [master]
BACKUP LOG [DVDRentalNew] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\DVDRentalNew_LogBackup.bak'
WITH NOFORMAT, NOINIT,
NAME = N'DVDRentalNew_LogBackup', NOSKIP, NOREWIND, NOUNLOAD,
NORECOVERY , STATS = 5

RESTORE DATABASE [DVDRentalNew] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\DVDRentalNew_Full_0201pm.bak'
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5

RESTORE DATABASE [DVDRentalNew] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\DVDRentalNew_Diff_0203pm.bak'
WITH FILE = 1,
NORECOVERY, NOUNLOAD, STATS = 5

RESTORE LOG [DVDRentalNew] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\DVDRentalNew_Tran_0206.trn'
WITH FILE = 1, NOUNLOAD, STATS = 5
GO

Note : Flow the Process shown in video.


😉Subscribe and like for more videos:
https://www.youtube.com/@chiragstutorial
💛Don't forget to, 💘Follow, 💝Like, 💖Share 💙&, Comment

0 Comments
Leave a message

 

Search Current Affairs by date
Other Category List