SQL Server : Point – in – Time Recovery


How to perform point-in-time-recovery?

— Check Recovery Model

SELECT name,recovery_model_desc
FROM sys.databases
— Change Recovery Model

USE [master]
GO
ALTER DATABASE [AdventureWorks] SET RECOVERY FULL WITH NO_WAIT

/*After problem has occurred and you need a point in time recovery
1. You must check that your database is in full recovery model and valid FULL backup is already taken.
2. Create a transaction log backup by using graphical interface or just executing following tsql.
3.Restore your full database backup BUT with RESTORE WITH NORECROVERY option
BUT up to your desired time. In this example we will restore our Transaction Log file up to 3:30PM.
*/

— Take Full Backup

BACKUP DATABASE [AdventureWorks] TO DISK = N’E:\EmergencyFullBackup.bak’
WITH INIT,STATS = 10
GO

— Take TLog Backup

BACKUP LOG [AdventureWorks] TO DISK = N’E:\EmergencyLogBackup.trn’ WITH
NOFORMAT, NOINIT, NAME = N’AdventureWorks-Transaction Log  Backup’, SKIP,
NOREWIND, NOUNLOAD, STATS = 10
GO

— Restore FullBackup with NORECOVERY Option

 RESTORE DATABASE AdventureWorks2008R2
 FROM DISK = ‘E:\EmergencyFullBackup.bak’
 WITH NORECOVERY;
GO

— Restore TLog Backup upto your desired time

RESTORE LOG [AdventureWorks]
FROM DISK = ‘E:\EmergencyLogBackup.trn’
WITH RECOVERY,
STOPAT = ‘Jan 03, 2011 03:30:00 PM’
GO

Thanks,

Sushil

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: