Restore the data to the specified point-in-time processing example

Restore the data to the specified point-in-time processing example 
 

  - 
  Create a test database 
 

 
  CREATE 
 
  DATABASE 
  Db 

 
  GO 
 



 
  - 
  Backup of the database 
 

 
  BACKUP 
 
  DATABASE 
  Db 
  TO 
 
  DISK 
  = 
  ' 
  c :/ db.bak 
  ' 
 
  WITH 
  FORMAT 

 
  GO 
 



 
  - 
  Create a test table 
 

 
  CREATE 
 
  TABLE 
  Db.dbo.TB_test (ID 
  int 
  ) 



 
  - 
  Delay of 1 second before the next operation (SQL Server time accuracy up to a hundredth of a second, do not delay, may be restored to the point in time of the operation failed) 
 

 
  WAITFOR 
  DELAY 
  ' 
  00:00:01 
  ' 
 

 
  GO 
 



 
  - 
  Suppose we accidently delete the table Db.dbo.TB_test 
 

 
  DROP 
 
  TABLE 
  Db.dbo.TB_test 



 
  - 
  Time to save the deleted table 
 

 
  SELECT 
  dt 
  = 
  GETDATE 
  () 
  INTO 
  # 

 
  GO 
 



 
  - 
  After the delete operation should not delete the table Db.dbo.TB_test 
 

 
 

 
  - 
  The following table demonstrates how to recover accidentally deleted Db.dbo.TB_test 
 

 
 

 
  - 
  First, back up the transaction log (using transaction logs can be restored to the specified point in time) 
 

 
  BACKUP 
 
  LOG 
  Db 
  TO 
 
  DISK 
  = 
  ' 
  c :/ db_log.bak 
  ' 
 
  WITH 
  FORMAT 

 
  GO 
 



 
  - 
  Next, we must first restore the full backup (restore log must restore the full backup) 
 

 
  RESTORE 
 
  DATABASE 
  Db 
  FROM 
 
  DISK 
  = 
  ' 
  c :/ db.bak 
  ' 
 
  WITH 
 
  REPLACE 
  , The NORECOVERY 

 
  GO 
 



 
  - 
  Restoring the transaction log to a delete operation (the time corresponding to the above deletion time and a little earlier than the delete time 
 

 
  DECLARE 
 
  @ Dt 
 
  datetime 
 

 
  SELECT 
 
  @ Dt 
  = 
  DATEADD 
  (Ms, 
  - 
  20 
  , Dt) 
  FROM 
  # 
  - 
  Get a little earlier than the time table is deleted 
 

 
  RESTORE 
 
  LOG 
  Db 
  FROM 
 
  DISK 
  = 
  ' 
  c :/ db_log.bak 
  ' 
 
  WITH 
  RECOVERY, STOPAT 
  = 
  @ Dt 
 

 
  GO 
 



 
  - 
  Query, see whether recovery 
 

 
  SELECT 
 
  * 
 
  FROM 
  Db.dbo.TB_test 



 
  / * 
  - Results: 

  ID 

  ----------- 



  (The number of rows affected 0 rows) 

  - 
  * / 
 



 
  - 
  The test is successful 
 

 
  GO 
 



 
  - 
  Last delete the test environment we do 
 

 
  DROP 
 
  DATABASE 
  Db 

 
  DROP 
 
  TABLE 
  #