一、基于数据库的备份的恢复：

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数据库，而基于文件备份的策略如果要成功恢复必须确保数据文件备份、增量备份、日志文件全部成功保存才可以恢复。