利用Log還原數據庫到某一時間點

/*利用log還原到時間點*/

-- 第一部分_創建數據庫PrimaryDB01
use master;
go
create database PrimaryDB01
on primary
    (
       name =PrimaryDB01_data01,
       filename='c:\PrimaryDB01_data01.mdf'
       ),
     (
        name =PrimaryDB01_data02,
        filename='c:\SQL2008\PrimaryDB01_data02.ndf'
        )
       
   LOG ON
 (
        name = PrimaryDB01_log01,
        filename ='c:\PrimaryDB01_log01.ldf'
        ),
      (
        name =PrimaryDB_log02,
        filename ='c:\SQL2008\PrimaryDB01_log02.ldf'
        )
   Go
 
-- 第二部分 
   use PrimaryDB01;
   go 
  create table tab01(id int)  --創建表tab01,插入數據
  go
  insert into tab01 values(1);
  insert into tab01 values(2);
  insert into tab01 values(3);
  
  create table tab02(name varchar(25));--創建表tab02,插入數據
  insert into tab02 values('a');
  insert into tab02 values('b');
  insert into tab02 values('c');
 
--第三部分
 
 use PrimaryDB01;
 go
     -- 數據庫PrimaryDB01進行full backup
 backup database PrimaryDB01 to disk='c:\PrimaryDB01_data.bak' with format;
 

 --文件備份后 對數據庫數據進行災難模擬處理
 
 
--第四部分

***********************************************************

/*1 模擬丟失數據文件，還原后數據庫數據的丟失情況*/
***********************************************************

  use PrimaryDB01;
  GO
  create table tab03(id int);
  insert into tab03 values(1);
 
  --刪除了數據文件PrimaryDB01_data02.ndf
 
  --恢復數據文件
  --1 首先需要備份當前的日志
  backup log PrimaryDB01 to disk ='c:\PrimaryDB01_log.bak' with format,no_truncate;

***************************************************************************

By default, when you perform. a transaction log backup from the SQL Server Enterprise Manager Backup Database dialog box, the Remove inactive entries from transaction log checkbox under the Options tab is selected so the backup should be performed without the NO_TRUNCATE option. However, if you perform. the backup and you do not click the Options tab, the server issues the backup log statement with the NO_TRUNCATE option so the entries for completed transactions are not removed from the transaction log upon completion of the backup. If you click the Options tab before you perform. the backup, the server issues the backup log statement without the NO_TRUNCATE option as expected.

***************************************************************************

  use master;
go
  --2 利用fullbackup 恢復被破壞的文件
  restore database PrimaryDB01 from disk='c:\PrimaryDB01_data.bak' with norecovery;
  
  --3 利用備份的日志文件還原到破壞點
  restore database PrimaryDB01 from disk='c:\PrimaryDB01_log.bak' with  recovery;
      --------------查詢數據庫的數據丟失情況
      select *from tab03;--------------結果顯示數據沒有丟失、正確利用了Log備份文件

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24867586/viewspace-710854/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24867586/viewspace-710854/