1、實驗九：數(shù)據(jù)庫備份及恢復(fù)一、實驗?zāi)康?. 熟悉數(shù)據(jù)庫備份及恢復(fù)機制；2. 了解SQL Server的數(shù)據(jù)備份和恢復(fù)機制；3. 掌握SQL-Server中數(shù)據(jù)庫備份和恢復(fù)的方法。二、實驗環(huán)境已安裝SQL Server 2005 企業(yè)版的計算機；具有局域網(wǎng)環(huán)境,有固定IP;三、實驗學(xué)時2學(xué)時四、實驗要求1. 了解創(chuàng)建備份設(shè)備和進行數(shù)據(jù)庫完全備份操作的方法；2. 了解進行數(shù)據(jù)庫恢復(fù)的步驟；3. 完成實驗報告。五、實驗內(nèi)容及步驟以管理員帳號登錄SQL Server Management Studio，以原有數(shù)據(jù)庫stu為基礎(chǔ)，請使用Management Stuio界面方式或T-SQL 語句實現(xiàn)以下操作

2、：1. 針對數(shù)據(jù)庫stu創(chuàng)建完全數(shù)據(jù)庫備份集stu.bak，目標(biāo)磁盤為D： user stu.bak;BACKUP DATABASE student TO DISK=C:userstu.bak2. 在數(shù)據(jù)庫stu中新建數(shù)據(jù)表ceshi，內(nèi)容自定，然后針對數(shù)據(jù)庫stu創(chuàng)建差異備份；CREATE TABLE ceshi(Sno char(9) NOT NULL primary key,Sname char(6) NOT NULL,Ssex char(2) NULL,Sage int NULL,Sdept varchar(8) NULL)GOBACKUP DATABASE student TO DI

3、SK=C:userstu_differential.bak WITH DIFFERENTIAL3. 向數(shù)據(jù)庫stu的數(shù)據(jù)表ceshi插入部分記錄，然后針對數(shù)據(jù)庫stu創(chuàng)建事務(wù)日志備份；BACKUP LOG student TO DISK=C:userstu_log.bak4. 根據(jù)需要，將數(shù)據(jù)庫恢復(fù)到數(shù)據(jù)庫stu的最初狀態(tài)；USE master RESTORE DATABASE student FROM DISK=C:userstu.bak5. 根據(jù)需要，將數(shù)據(jù)庫恢復(fù)到創(chuàng)建數(shù)據(jù)表ceshi后的狀態(tài)；USE master RESTORE DATABASE student FROM DISK=C:

4、userstu.bak WITH REPLACE, NORECOVERY GO RESTORE DATABASE student FROM DISK=C:userstu_differential.bak6. 根據(jù)需要，將數(shù)據(jù)庫恢復(fù)到在ceshi表插入記錄后的狀態(tài)；USE master RESTORE DATABASE studentFROM DISK=C:userstu.bak WITH REPLACE, NORECOVERY GO RESTORE LOG student FROM DISK=C:userstu_log.bak7. 針對現(xiàn)有數(shù)據(jù)庫stu創(chuàng)建完全文件和文件組備份集stu_file

5、，目標(biāo)磁盤為D： user stu_file.bak；user stu_file.bak；BACKUP DATABASE student FILE = stu, FILE = db2, FILEGROUP = PRIMARY TO DISK=C:userstu_file.bak8. 在當(dāng)前數(shù)據(jù)庫中新建數(shù)據(jù)表ceshi2，然后針對數(shù)據(jù)庫stu創(chuàng)建差異文件和文件組備份；USE student CREATE TABLE ceshi2( testID int, testName varchar(10) GO BACKUP DATABASE student FILE = stu, FILE = db2,

6、 FILEGROUP = PRIMARY TO DISK=C:userstu_file_differential.bak WITH DIFFERENTIAL9. 向數(shù)據(jù)庫stu的數(shù)據(jù)表ceshi2插入部分記錄，然后針對數(shù)據(jù)庫stu創(chuàng)建事務(wù)日志文件和文件組備份；BACKUP LOG student TO DISK=C:userstu_file_log.bak10. 根據(jù)需要，將數(shù)據(jù)庫以文件和文件組方式恢復(fù)到創(chuàng)建數(shù)據(jù)表ceshi2后的狀態(tài)；USE master RESTORE DATABASE student FILE = stu, FILE = db2, FILEGROUP = PRIMARY

7、FROM DISK=C:userstu_file.bak WITH REPLACEGO USE master RESTORE DATABASE student FILE = stu, FILE = db2, FILEGROUP = PRIMARY FROM DISK=C:userstu_file.bak WITH REPLACE,NORECOVERY RESTORE DATABASE student FILE = stu, FILE =db2,FILEGROUP=PRIMARYFROM DISK=C:userstu_file_differential.bak11. 根據(jù)需要，將數(shù)據(jù)庫以文件和文

8、件組方式恢復(fù)到數(shù)據(jù)表ceshi2插入記錄后的狀態(tài)；USE master RESTORE DATABASE student FILE = stu, FILE = db2, FILEGROUP = PRIMARY FROM DISK=C:userstu_file.bak WITH REPLACE, NORECOVERY GO RESTORE LOG student FROM DISK=C:userstu_file_log.bak12. 為數(shù)據(jù)庫stu設(shè)置一個備份計劃1（建議使用數(shù)據(jù)庫維護計劃向?qū)?，【服?wù)器】【管理】【維護計劃】【維護計劃向?qū)А浚?；13. 為數(shù)據(jù)庫stu設(shè)置一個備份計劃2，并比較各自優(yōu)缺點。六、思考題1. 某些備份無法執(zhí)行，原因？2. 某些恢復(fù)無法執(zhí)行，原因？ 差異備份的恢復(fù)和事務(wù)日志的恢復(fù)需要先有回滾對象，即需要先對完整備份進行WITH REPLACE,NORECOVERY 方式恢復(fù)3. 說明數(shù)據(jù)庫的備份和恢復(fù)的基本步驟；4. SQL Server中的完全備份、事務(wù)日志備份和增量備份形式的功能特點； 完全備份備份了整個數(shù)據(jù)庫內(nèi)容，占用空間大（如果對