2 Restore Database (1) Point In Time Recovery

 

We can only do the point in time recovery under Full or Bulk insert mode.

1. -- Setup the test ENV
2. ALTER DATABASE AdventureWorks2008R2 SET RECOVERY FULL;
3. -- Backup the database
4. BACKUP DATABASE [AdventureWorks_new] TO  DISK ='C:\dump\AdventureWorks_new_20111029.db' with format
5. -- Create The test Table
6. CREATE TABLE TB_1
7.          (number INT IDENTITY(1,1) NOT NULL,
8.           name varchar(50) NOT NULL,
9.           updateTIme datetime default getdate(),       
10.          )
11. --- Insert Test data
12. DECLARE @count INT
13. SET @count = 0
14. WHILE (@count < 40)
15. BEGIN
16.    Insert into TB_1 (name)values('SUMMER')
17.    -- wait for 10 second
18.     WAITFOR DELAY '00:00:10'
19.    SET @count = (@count + 1)
20. END
21. -- BACKUP TRAN LOG
22. BACKUP LOG [AdventureWorks_new] TO  DISK ='C:\dump\tran\AdventureWorks_new.1.log' with format
23.  
24. -- Slect the data
25. select * from TB_1 where TB_1.number = 5;
26.  
27. -- Delete Data
28. delete TB_1 where TB_1.number =5;

The row number 5 is gone after line 28 delete statement. Lets try to restore it.

First, we take another tran log backup

1. -- BACKUP TRAN LOG
2.   BACKUP LOG [AdventureWorks_new] TODISK ='C:\dump\tran\AdventureWorks_new.2.log' with format;

Then, lets restore with below orders:

• Full backup with no recovery
• Tran log 1 with no recovery and time stamp
• Tran log 2 with no recovery and time stamp

1. -- Restore the FULL DB BACKUP with norecovery
2. -- KIll all the connection and start the restore
3. use master;
4.   RESTORE DATABASE [AdventureWorks_new] fromDisk ='C:\dump\AdventureWorks_new_20111029.db' with NORECOVERY;

Since it is NORECOVERY, the database shows as Restoring…

1. -- Restore the 1 tran dump with norecovery
2. RESTORE LOG [AdventureWorks_new] from  Disk = 'C:\dump\tran\AdventureWorks_new.1.log'
3. with NORECOVERY , STOPAT = '2011-10-28 22:32:01.133';
4. -- Restore the 2 tran dump till the time it is being deleted and
5. RESTORE LOG [AdventureWorks_new] from  Disk = 'C:\dump\tran\AdventureWorks_new.2.log'
6. with NORECOVERY , STOPAT = '2011-10-28 22:32:01.133';
7. --- RESTORE DB
8. RESTORE DATABASE [AdventureWorks_new] WITH RECOVERY;

STOPAT must follow by the time stamp where you want SQL Server stop applying the transaction log. Any log before that time (Including that time will not apply to the database)

In the production environment , if row being delete by accidently, it may not be a good idea to do this type of restore. As the database is not available during the time of restore. Most importantly, you will take a risk what if restore fail and you are losing the entire db. It is the best you can restore the database in the test environment and recover from there.