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

[email protected] Chirag's SQL Server DBA Tutorial https://www.chirags.in
*****************************************************************************************
* 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/channel/UCLdaO4-i_gxQMi87JwjKOwA
?Don't forget to, ?Follow, ?Like, ?Share ?&, Comment

Tutorial Link :
https://www.chirags.in/tutorials/microsoft_sql_server_database_tutorials

Thanks & Regards,
Chitt Ranjan Mahto "Chirag"
_________________________________________________________________________________________
Note: All scripts used in this demo will be available in our website.
Link will be available in description.

#DBATutorial
#DBATutorialLearning
#DBAEducation
#DBATutorialFree
#DatabaseTutorial
#SQLServerDatabaseTutorial
#SQLServerDBATutorial
#MicrosoftSQLServerDatabaseTutorial
#ChiragsDBATutorial
#ChiragsDatabaseTutorial
#ChiragsSQLServerDatabaseTutorial
#ChiragsSQLServerDBATutorial
#Chirags.inSQLServerDBATutorial
#ChiragsMicrosoftSQLServerDatabaseTutorial
#CreateDatabaseinSQLServer
#CreateDatabasewithT-SQL
#chiragstutorial
#databasetutorial
#sqlserver
#sqlservertutorial
#sqlservermanagementstudio
#sql_server
#sqlservertutorial
#chiragstutorial
#databasetutorial
#sqlqueries
#sqldba
#database
#databasemanagement