1、實驗十六數據備份、恢復和導入導出【實驗目的與要求】1 學會使用企業管理器、備份命令創建和刪除命名備份設備。2 掌握使用企業管理器、備份命令進行數據庫的備份。3 掌握使用企業管理器和恢復命令進行數據庫的恢復。4 掌握數據的導入導出。【實驗內容與步驟】16.1 正常情況下的數據備份與恢復1、創建命名備份設備在C:Program FilesMicrosoft SQL ServerMSSQLBACKUP下創建一個名為“test1”的命名備份設備。2、數據庫的備份和恢復（1）將整個CPXS數據庫備份到test1，并利用該備份集恢復CPXS數據庫。（2）在CPXS數據庫中增加表s1。create tabl

2、e s1(sno char(5), sname char(10)再差異備份CPXS數據庫追加到test1。恢復時，先用test1備份設備上媒體集1恢復CPXS，再用test1備份設備上媒體集2 對CPXS數據庫進行差異恢復。注意過程和觀察結果。（3）在CPXS數據庫中增加表s。create table s(sno char(5), sname char(10) on uu再將文件組uu追加備份到test1，并將日志追加備份到test1。恢復時，先用test1備份設備上媒體集1恢復CPXS，再用test1備份設備上媒體集2對CPXS數據庫進行差異恢復，接著用test1備份設備上媒體集1恢復文件組

3、uu，然后用test1備份設備上媒體集4進行日志恢復。注意過程和觀察結果。16.2 異常情況下的數據恢復1、部分數據文件損壞的修復處理請完成以下練習，理解每一個操作過程：-創建數據庫CREATE DATABASE dbON PRIMARY(NAME='db_data',FILENAME= 'c:db_data.mdf'),FILEGROUP db_fg1(NAME = 'db_fg1_data',FILENAME = 'c:db_fg1_data.ndf'),FILEGROUP db_fg2(NAME = 'db_fg2_

4、data',FILENAME = 'c:db_fg2_data.ndf')LOG ON(NAME='db_log',FILENAME ='c:db.ldf')GO-創建表CREATE TABLE db.dbo.ta(id int) ON PRIMARYCREATE TABLE db.dbo.tb(id int) ON db_fg1CREATE TABLE db.dbo.tc(id int) ON db_fg2INSERT db.dbo.tb SELECT id FROM sysobjectsGO-做文件組備份BACKUP DATABASE

5、 db FILEGROUP='db_fg1' TO DISK='c:db_fg1.bak' WITH FORMAT-備份后,再做數據處理INSERT db.dbo.ta SELECT id FROM sysobjectsGO/*-下面演示了破壞數據文件的處理，這些操作在操作系統中進行1. 停止SQL Server服務(msqlserver服務)2. 刪除文件 c:db_fg1_data.ndf (模擬破壞)3. 重新SQL Server服務,此時數據庫DB置疑-*/GO-下面演示了如何恢復數據-首先要備份當前日志BACKUP LOG db TO DISK=

6、9;c:db_log.bak' WITH FORMAT,NO_TRUNCATE-利用文件組備份恢復破壞的文件RESTORE DATABASE db FILEGROUP='db_fg1' FROM DISK='c:db_fg1.bak' WITH NORECOVERY-還原到日志點RESTORE LOG db FROM DISK='c:db_log.bak' WITH RECOVERY-顯示已經恢復的數據SELECT COUNT(*) FROM db.dbo.tbSELECT COUNT(*) FROM db.dbo.taGO-刪除測試DR

7、OP DATABASE db2、僅有日志文件的數據修復處理-創建數據庫CREATE DATABASE dbON PRIMARY(NAME='db_data',FILENAME= 'c:db_data.mdf')LOG ON(NAME='db_log',FILENAME ='c:db.ldf')GO-創建表CREATE TABLE db.dbo.ta(id int)INSERT db.dbo.ta SELECT id FROM sysobjectsGO-做文件組備份BACKUP DATABASE db TO DISK='c:

8、db.bak' WITH FORMAT-備份后,再做數據處理CREATE TABLE db.dbo.tb(id int)INSERT db.dbo.tb SELECT id FROM sysobjectsGO/*-下面演示了破壞數據文件的處理，這些操作在操作系統中進行1. 停止SQL Server服務(msqlserver服務)2. 刪除文件 c:db_data.ndf (模擬破壞)3. 重新SQL Server服務,此時數據庫DB置疑-*/GO-下面演示了如何恢復數據-首先要備份當前日志BACKUP LOG db TO DISK='c:db_log.bak' WITH

9、 FORMAT,NO_TRUNCATE-利用文件組備份恢復破壞的文件RESTORE DATABASE db FROM DISK='c:db.bak' WITH NORECOVERY-還原到日志點RESTORE LOG db FROM DISK='c:db_log.bak' WITH RECOVERY-顯示已經恢復的數據SELECT COUNT(*) FROM db.dbo.taSELECT COUNT(*) FROM db.dbo.tbGO3、數據還原到指定時間點的處理-創建測試數據庫CREATE DATABASE DbGO-對數據庫進行備份BACKUP DAT

10、ABASE Db TO DISK='c:db.bak' WITH FORMATGO-創建測試表CREATE TABLE Db.dbo.TB_test(ID int)-延時1秒鐘,再進行后面的操作(這是由于SQL Server的時間精度最大為百分之三秒,不延時的話,可能會導致還原到時間點的操作失敗)WAITFOR DELAY '00:00:01'GO-假設我們現在誤操作刪除了 Db.dbo.TB_test 這個表DROP TABLE Db.dbo.TB_test-保存刪除表的時間SELECT dt=GETDATE() INTO #GO-在刪除操作后,發現不應該刪除

11、表 Db.dbo.TB_test-下面演示了如何恢復這個誤刪除的表 Db.dbo.TB_test-首先,備份事務日志(使用事務日志才能還原到指定的時間點)BACKUP LOG Db TO DISK='c:db_log.bak' WITH FORMATGO-接下來,要先還原完全備份(還原日志必須在還原完全備份的基礎上進行)RESTORE DATABASE Db FROM DISK='c:db.bak' WITH REPLACE,NORECOVERYGO-將事務日志還原到刪除操作前（這里的時間對應上面的刪除時間，并比刪除時間略早DECLARE dt datetime

12、SELECT dt=DATEADD(ms,-20,dt) FROM # -獲取比表被刪除的時間略早的時間RESTORE LOG Db FROM DISK='c:db_log.bak' WITH RECOVERY,STOPAT=dtGO-查詢一下,看表是否恢復SELECT * FROM Db.dbo.TB_test請給出測試結果：-最后刪除所做的測試環境DROP DATABASE DbDROP TABLE #4、在現有數據庫上還原時的數據文件處理-創建一個測試數據庫CREATE DATABASE dbON(NAME=db,FILENAME='c:db.mdf')L

13、OG ON(NAME=db_log,FILENAME='c:db.ldf')-備份并刪除測試數據庫BACKUP DATABASE db TO DISK='c:a.bak' WITH FORMATDROP DATABASE db-創建一個文件結構相同,但物理文件位置不同的數據庫CREATE DATABASE dbON(NAME=db,FILENAME='d:db.mdf')LOG ON(NAME=db_log,FILENAME='d:db.ldf')-在新建的數據庫上強制還原備份RESTORE DATABASE db FROM DI

14、SK='c:a.bak' WITH REPLACE-查看還原后的文件位置SELECT name,filename FROM db.dbo.sysfiles請給出測試結果：-刪除測試5、備份數據庫,并按日期生成備份目錄/*-調用示例-備份當前數據庫exec p_backupdb -*/if exists (select * from dbo.sysobjects where id = object_id(N'dbo.p_backupdb') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop proced

15、ure dbo.p_backupdbGOcreate proc p_backupdbdbname sysname='',-要備份的數據庫名稱,不指定則備份當前數據庫bkfname nvarchar(260)='',-備份文件名,文件名中可以用DBNAME代表數據庫名,DATE代表日期,TIME代表時間bkpath nvarchar(260)='c:'-備份文件的存放目錄,在該目錄下創建每天備份的目錄asdeclare sql varchar(8000)if isnull(dbname,'')='' set dbna

16、me=db_name()if right(bkpath,1)<>'' set bkpath=bkpath+''set bkpath=bkpath+convert(varchar,getdate(),112)+''-創建目錄set sql='md '+bkpathexec master.xp_cmdshell sql,no_output-備份處理if isnull(bkfname,'')='' set bkfname='DBNAME_DATE.BAK'set bkfname

17、=replace(replace(replace(bkfname,'DBNAME',dbname),'DATE',convert(varchar,getdate(),112),'TIME',replace(convert(varchar,getdate(),108),':','')set sql='backup database '+dbname+' to disk='''+bkpath+bkfname+''''exec(sql)go1

18、6.3 數據導入與導出1、導入/導出 Excel 的基本方法（1）從Excel文件中導入數據到SQL數據庫。如果接受數據導入的表已經存在insert into 表名 select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=c:test.xls',sheet1$)如果導入數據并生成表select * into 表名 from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DA

19、TABASE=c:test.xls',sheet1$)（2）從SQL數據庫中,導出數據到Excel如果Excel文件已經存在,而且已經按照要接收的數據創建好表頭,就可以簡單的用:insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=c:test.xls',sheet1$)select * from 表如果Excel文件不存在,也可以用BCP來導出Excel的文件(注意大小寫):EXEC master.xp_cmdshell 'bcp 數據庫名.db

20、o.表名 out "c:test.xls" /c /S"服務器名" /U"用戶名" -P"密碼"'導出查詢的情況EXEC master.xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs.authors ORDER BY au_lname" queryout "c:test.xls" /c /S"服務器名" /U"用戶名" -P"密碼"'

21、說明:c:test.xls為導入/導出的Excel文件名.要求文件在SQL服務器或者服務器可以訪問的共享目錄上sheet1$ 為Excel文件的工作表名,一般要加上$才能正常使用.（3）將某個目錄上的Excel表,導入到數據庫中將所有的Excel文件放到一個目錄中,假設為c:test,然后用下面的方法來做create table #t(fname varchar(260),depth int,isf bit)insert into #t exec master.xp_dirtree 'c:test',1,1declare tb cursor for select fn='c:test'+fname from #twhere isf=1 and fname like '%.xls' -取.