SQLSERVER恢復測試

SQLSERVER備份恢復測試：

一、基於資料庫的備份的恢復：

1、查詢資料庫物件：

select b.name from sysusers a,sysobjects b
where a.uid=b.uid
and a.name='TEST'
and xtype='U'
order by b.name

a_test
T
test

2、備份資料庫：

backup database test to disk='c:amazon est_full.bak'

Processed 120 pages for database 'test', file 'TEST_Data' on file 1.
Processed 8 pages for database 'test', file 'TEST_Data2' on file 1.
Processed 1 pages for database 'test', file 'TEST_Log' on file 1.
BACKUP DATABASE successfully processed 129 pages in 0.602 seconds (1.743 MB/sec).

3、建立測試表test_incr：

create table test_incr(id int,mc varchar(60))

The command(s) completed successfully.

4、查詢資料庫物件：

select b.name from sysusers a,sysobjects b
where a.uid=b.uid
and a.name='TEST'
and xtype='U'
order by b.name

a_test
T
test
test_incr

5、增量備份：

backup database test to disk = 'c:amazon est_incr.bak' with init,differential,stats=10,noformat

33 percent backed up.
66 percent backed up.
83 percent backed up.
99 percent backed up.
Processed 40 pages for database 'test', file 'TEST_Data' on file 1.
Processed 8 pages for database 'test', file 'TEST_Data2' on file 1.
100 percent backed up.
Processed 1 pages for database 'test', file 'TEST_Log' on file 1.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 49 pages in 0.388 seconds (1.016 MB/sec).

6、建立資料庫物件test_log：

create table test_log(id int,mc varchar(60))
insert into test_log values (1,'111111')

select b.name from sysusers a,sysobjects b
where a.uid=b.uid
and a.name='TEST'
and xtype='U'
order by b.name

a_test
T
test
test_incr
test_log

7、備份日誌：

backup log test to disk = 'c:amazon est_log.bak'

Processed 83 pages for database 'test', file 'TEST_Log' on file 1.
BACKUP LOG successfully processed 83 pages in 0.257 seconds (2.629 MB/sec).

8、刪除資料庫：

DROP DATABASE TEST

9、恢復全庫備份with norecovery：

restore database test from disk = 'c:amazon est_FULL.bak' with norecovery

Processed 120 pages for database 'test', file 'TEST_Data' on file 1.
Processed 8 pages for database 'test', file 'TEST_Data2' on file 1.
Processed 1 pages for database 'test', file 'TEST_Log' on file 1.
RESTORE DATABASE successfully processed 129 pages in 0.622 seconds (1.687 MB/sec).

10、恢復增量備份with norecovery：

restore database test from disk = 'c:amazon est_INCR.bak' with norecovery

Processed 40 pages for database 'test', file 'TEST_Data' on file 1.
Processed 8 pages for database 'test', file 'TEST_Data2' on file 1.
Processed 1 pages for database 'test', file 'TEST_Log' on file 1.
RESTORE DATABASE successfully processed 49 pages in 1.283 seconds (0.307 MB/sec).

11、恢復日誌備份with recovery：

restore LOG test from disk = 'c:amazon est_LOG.bak' with recovery

Processed 83 pages for database 'test', file 'TEST_Log' on file 1.
RESTORE LOG successfully processed 83 pages in 0.065 seconds (10.397 MB/sec).

12、察看資料庫物件：test_incr和test_log全部恢復

select b.name from sysusers a,sysobjects b
where a.uid=b.uid
and a.name='TEST'
and xtype='U'
order by b.name

a_test
T
test
test_incr
test_log

SELECT * FROM TEST.test_log

1 111111

二、基於資料檔案備份的恢復：（檔案備份和還原操作必須與事務日誌備份一起使用。因此，檔案備份只適用於完全恢復和大容量日誌記錄恢復模式。）

BACKUP DATABASE test
FILE = 'test_Data'
TO disk='c:sqlserverbaktest_data1'
WITH INIT , STATS = 10, NOFORMAT

BACKUP DATABASE test
FILE = 'test_Data2'
TO disk='c:sqlserverbaktest_data2'
WITH INIT , STATS = 10, NOFORMAT

BACKUP DATABASE test
FILE = 'test_Data3'
TO disk='c:sqlserverbaktest_data3'
WITH INIT , STATS = 10, NOFORMAT

backup log test to disk = 'c:sqlserverbaktest_log'

restore database test file='test_Data'
from disk = 'c:sqlserverbaktest_data1' with norecovery

restore database test file='test_Data2'
from disk = 'c:sqlserverbaktest_data2' with norecovery

restore database test file='test_Data3'
from disk = 'c:sqlserverbaktest_data3' with norecovery

restore log test from disk = 'c:sqlserverbaktest_log' with recovery

Processed 120 pages for database 'test', file 'TEST_Data' on file 1.
RESTORE DATABASE successfully processed 120 pages in 0.200 seconds (4.915 MB/sec).
Processed 8 pages for database 'test', file 'TEST_Data2' on file 1.
RESTORE DATABASE successfully processed 8 pages in 0.057 seconds (1.149 MB/sec).
Processed 8 pages for database 'test', file 'TEST_Data3' on file 1.
RESTORE DATABASE successfully processed 8 pages in 0.039 seconds (1.680 MB/sec).
Processed 3 pages for database 'test', file 'TEST_Log' on file 1.
RESTORE LOG successfully processed 3 pages in 0.786 seconds (0.026 MB/sec).

三、生產環境的資料庫恢復：(略)

總結：如果不是磁碟空間嚴重不足，建議採用基於資料庫的備份方案，對於以後增加資料檔案等操作不必去修改備份策略，易於維護；而且提高了恢復的成功率，即使全庫備份的增量或者日誌檔案出錯了，也可以利用全庫備份檔案REVOCERY資料庫，而基於檔案備份的策略如果要成功恢復必須確保資料檔案備份、增量備份、日誌檔案全部成功儲存才可以恢復。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-242465/，如需轉載，請註明出處，否則將追究法律責任。