SQLServer實例教程(2019版)數(shù)據(jù)庫管理操作第十二章”十四五”職業(yè)教育國家規(guī)劃教材01數(shù)據(jù)庫備份數(shù)據(jù)庫備份01數(shù)據(jù)庫備份概述數(shù)據(jù)的安全對于數(shù)據(jù)庫管理系統(tǒng)來說是至關(guān)重要的，任何數(shù)據(jù)的丟失和危險都會帶來嚴重的后果。對于SQLServer2019數(shù)據(jù)庫系統(tǒng)中的數(shù)據(jù)，主要存在下面三種危險。（1）系統(tǒng)故障。由于硬件故障（停電等）、軟件錯誤（操作系統(tǒng)不穩(wěn)定等）使內(nèi)存中的數(shù)據(jù)或日志內(nèi)容突然損壞,事務(wù)處理終止,但是物理介質(zhì)上的數(shù)據(jù)和日志并沒有被破壞。SQLServer2019系統(tǒng)本身可以修復(fù)這種故障，不用管理人員干預(yù)。（2）事務(wù)故障。事務(wù)是SQLServer2019執(zhí)行SQL命令的一個完整的邏輯操作，事務(wù)故障是事務(wù)運行到最后沒有得到正常提交而產(chǎn)生的故障。SQLServer2019系統(tǒng)本身也可以修復(fù)這種故障,不用管理人員干預(yù)。數(shù)據(jù)庫備份01數(shù)據(jù)庫備份概述（3）介質(zhì)故障。介質(zhì)故障又稱硬故障，是由于物理存儲介質(zhì)的故障發(fā)生的讀寫錯誤，或者管理人員操作失誤刪除了重要數(shù)據(jù)文件和日志文件。這種故障需要管理人員手工進行恢復(fù)，而恢復(fù)的基礎(chǔ)就是在發(fā)生故障以前做的數(shù)據(jù)庫備份和日志記錄。管理人員需要掌握的備份與恢復(fù)技術(shù)主要針對介質(zhì)故障。數(shù)據(jù)庫備份就是對SQLServer數(shù)據(jù)庫或事務(wù)日志進行復(fù)制。數(shù)據(jù)庫備份記錄了在進行備份這一操作時數(shù)據(jù)庫中所有數(shù)據(jù)的狀態(tài)，以便在數(shù)據(jù)庫遭到破壞時能夠及時地將其恢復(fù)。數(shù)據(jù)庫備份01數(shù)據(jù)庫備份設(shè)備1．備份設(shè)備類型（1）磁盤設(shè)備。磁盤備份設(shè)備是硬盤或其他磁盤存儲媒體上的文件，與常規(guī)操作系統(tǒng)文件一樣。引用磁盤備份設(shè)備與引用任何其他操作系統(tǒng)文件一樣，可以在服務(wù)器的本地磁盤上或共享網(wǎng)絡(luò)資源的遠程磁盤上定義磁盤備份設(shè)備，磁盤備份設(shè)備根據(jù)需要可大可小。最大文件大小可以相當于磁盤上的可用磁盤空間。（2）磁帶設(shè)備。磁帶備份設(shè)備的用法與磁盤設(shè)備基本相同，但必須將磁帶設(shè)備物理連接到運行SQLServer實例的計算機上。SQLServer不支持備份到遠程磁帶設(shè)備上。如果磁帶備份設(shè)備在備份操作過程中已滿，但還需要寫入一些數(shù)據(jù)，則SQLServer將提示更換新磁帶并繼續(xù)備份操作。數(shù)據(jù)庫備份01數(shù)據(jù)庫備份設(shè)備2．使用SQLServerManagementStudio管理備份設(shè)備在進行備份以前，首先必須指定或創(chuàng)建備份設(shè)備。當使用磁盤作為備份設(shè)備時，SQLServer允許將本地主機硬盤和遠程主機上的硬盤作為備份設(shè)備。備份設(shè)備在硬盤中是以文件的方式存儲的?！救蝿?wù)1-1】使用SQLServerManagementStudio創(chuàng)建磁盤備份設(shè)備webshop。（1）啟動SQLServerManagementStudio，在“對象資源管理器”中展開【服務(wù)器對象】節(jié)點。數(shù)據(jù)庫備份01數(shù)據(jù)庫備份設(shè)備2．使用SQLServerManagementStudio管理備份設(shè)備（2）右擊【備份設(shè)備】節(jié)點，在彈出的快捷菜單中選擇【新建備份設(shè)備】選項，如圖12-2所示。數(shù)據(jù)庫備份01數(shù)據(jù)庫備份設(shè)備2．使用SQLServerManagementStudio管理備份設(shè)備（3）打開“備份設(shè)備”對話框，在“設(shè)備名稱”文本框中輸入“webshop”(即邏輯名稱為webshop)，對應(yīng)的物理文件名為“C:ProgramFilesMicrosoftSQLServerlMSSQL.11MSSQLBackuplwebshop.bak”，也可通過“文件”單選按鈕右側(cè)的

按鈕指定備份設(shè)備對應(yīng)的物理文件名，如圖12-3所示。數(shù)據(jù)庫備份01數(shù)據(jù)庫備份設(shè)備3．使用T-SQL管理備份設(shè)備（1）創(chuàng)建備份設(shè)備。在SQLServer中，可以使用sp_addumpdevice語句將備份設(shè)備添加到MicrosoftsQLServer2019數(shù)據(jù)庫引擎的實例中，其基本語句格式如下:數(shù)據(jù)庫備份01數(shù)據(jù)庫備份設(shè)備3．使用T-SQL管理備份設(shè)備（2）查看備份設(shè)備。在SQLServer中，可以使用sp_helpdevice語句查看備份設(shè)備信息，其基本語句格式如下:（3）刪除備份設(shè)備。在SQLServer中，可以使用sp_dropdevice語句刪除備份設(shè)備，其基本語句格式如下:數(shù)據(jù)庫備份01執(zhí)行數(shù)據(jù)庫備份1．制定數(shù)據(jù)庫備份策略（1）選擇備份的內(nèi)容。備份內(nèi)容包括如下幾個方面。①系統(tǒng)數(shù)據(jù)庫：系統(tǒng)數(shù)據(jù)庫master中存儲著SQLServer2019服務(wù)器的配置參數(shù)、用戶登錄標識、系統(tǒng)存儲過程等重要內(nèi)容，需要備份。在執(zhí)行影響系統(tǒng)數(shù)據(jù)庫master中內(nèi)容的SQL語句或系統(tǒng)存儲過程后，都要再次備份該數(shù)據(jù)庫。②用戶數(shù)據(jù)庫：包含了用戶加載的數(shù)據(jù)信息，是數(shù)據(jù)庫應(yīng)用程序操作的主體，應(yīng)定期備份。數(shù)據(jù)庫備份01執(zhí)行數(shù)據(jù)庫備份1．制定數(shù)據(jù)庫備份策略③事務(wù)日志：記錄用戶對數(shù)據(jù)庫的修改，一個事務(wù)就是單個工作單元。SQLServer2019自動維護和管理所有數(shù)據(jù)庫更改事務(wù)，在修改數(shù)據(jù)庫以前，它把事務(wù)寫入日志，所以日志要定期備份。（2）確定備份頻率。影響備份頻率的兩個因素如下:①存儲介質(zhì)出現(xiàn)故障可能導(dǎo)致丟失的工作量的大小;②數(shù)據(jù)庫事務(wù)的數(shù)量。數(shù)據(jù)庫備份01執(zhí)行數(shù)據(jù)庫備份2．備份方式（1）完整備份。該操作將備份包括部分事務(wù)日志在內(nèi)的整個數(shù)據(jù)庫。通過包括在完整備份中的事務(wù)日志，可以使用備份恢復(fù)到備份完成時的數(shù)據(jù)庫。創(chuàng)建完整備份是單一操作，通常會安排該操作定期發(fā)生。（2）完整差異備份。在完整備份之后執(zhí)行的完整差異備份只記錄上次數(shù)據(jù)庫備份后更改的數(shù)據(jù)。完整差異備份比完整備份更小、更快，可以簡化頻繁的備份操作，減少數(shù)據(jù)丟失的風險。完整差異備份基于以前的完整備份，因此，這樣的完整備份稱為“基準備份”。數(shù)據(jù)庫備份01執(zhí)行數(shù)據(jù)庫備份2．備份方式（3）部分備份。部分備份類似于完整數(shù)據(jù)庫備份，但只能包含主文件組和所有的讀/寫文件組。（4）部分差異備份。在部分備份之后執(zhí)行的部分差異備份只包含在主文件組和所有讀/寫文件組中更改的數(shù)據(jù)。部分差異備份僅與部分備份一起使用。部分差異備份僅包含在備份時主文件組和讀/寫文件組中更改的那些區(qū)。如果部分備份捕獲的數(shù)據(jù)只有一部分已更改，則使用部分差異備份可以使數(shù)據(jù)庫管理員更快地創(chuàng)建更小的備份。部分差異備份是與單個基準備份一起使用的。數(shù)據(jù)庫備份01執(zhí)行數(shù)據(jù)庫備份2．備份方式（5）文件和文件組備份。文件組備份與文件備份的作用相同。文件組備份是文件組中所有文件的單個備份，相當于在創(chuàng)建備份時顯式列出文件組中的所有文件?？梢赃€原文件組備份中的個別文件，也可以將所有文件作為一個整體還原。（6）文件差異備份。在文件備份或文件組備份之后執(zhí)行的文件差異備份。文件差異備份只包含在指定文件或文件組中更改的數(shù)據(jù)。數(shù)據(jù)庫備份01執(zhí)行數(shù)據(jù)庫備份3．使用T-SQL語句執(zhí)行備份備份數(shù)據(jù)庫可以使用BACKUPDATABASE語句完成,使用T-SQL備份數(shù)據(jù)庫根據(jù)不同的備份類型，有不同的語句格式。備份整個數(shù)據(jù)庫的基本語句格式如下:02數(shù)據(jù)庫恢復(fù)數(shù)據(jù)庫恢復(fù)02數(shù)據(jù)庫恢復(fù)概述1．數(shù)據(jù)庫恢復(fù)定義數(shù)據(jù)庫備份后，一旦系統(tǒng)崩潰或執(zhí)行了錯誤的數(shù)據(jù)庫操作，就可以從備份文件中恢復(fù)數(shù)據(jù)庫。數(shù)據(jù)庫恢復(fù)是指將數(shù)據(jù)庫備份加載到系統(tǒng)中的過程。系統(tǒng)在恢復(fù)數(shù)據(jù)庫的過程中，自動執(zhí)行安全性檢查，重建數(shù)據(jù)庫結(jié)構(gòu)及完整數(shù)據(jù)庫內(nèi)容，從而保證將遭到破壞或丟失的數(shù)據(jù)恢復(fù)到備份時的狀態(tài)，使數(shù)據(jù)庫能夠正常工作。數(shù)據(jù)庫恢復(fù)02數(shù)據(jù)庫恢復(fù)概述2．數(shù)據(jù)庫恢復(fù)模式SQLServer2019中有三種恢復(fù)模式：簡單恢復(fù)模式、完整恢復(fù)模式和大容量日志恢復(fù)模式。（1）簡單恢復(fù)模式。對數(shù)據(jù)安全性要求不高，但對性能要求很高的數(shù)據(jù)庫，可以工作在簡單恢復(fù)模式下。工作在簡單恢復(fù)模式的SQLServer2019數(shù)據(jù)庫的日志雖然會記錄數(shù)據(jù)庫的所有日志操作(包括大型操作)，但檢查點進程會自動截斷日志中不活動部分(已經(jīng)完成的部分)。每發(fā)生一次檢查點，日志已經(jīng)完成的部分就會被刪除，所以簡單恢復(fù)模式的數(shù)據(jù)庫可能會導(dǎo)致無法恢復(fù)到歷史上某個時刻的情況。當創(chuàng)建SQLServer2019數(shù)據(jù)庫時，用戶數(shù)據(jù)庫會繼承系統(tǒng)數(shù)據(jù)庫的恢復(fù)模式等參數(shù)設(shè)置，多數(shù)情況下，默認工作在簡單恢復(fù)模式下。數(shù)據(jù)庫恢復(fù)02數(shù)據(jù)庫恢復(fù)概述2．數(shù)據(jù)庫恢復(fù)模式（2）完整恢復(fù)模式。對于十分重要的生產(chǎn)數(shù)據(jù)庫，如銀行、電信、電力、郵政等系統(tǒng)，一旦發(fā)生故障必須保證數(shù)據(jù)不能丟失，在發(fā)生故障時可能要求恢復(fù)到歷史某一時刻。這樣的數(shù)據(jù)庫必須工作在完整恢復(fù)模式。在完整恢復(fù)模式下工作的SQLServer2019數(shù)據(jù)庫將忠實、完整地記錄所有日志，因此必須定期地進行數(shù)據(jù)庫備份或事務(wù)日志備份，確保日志空間被定期回收使用，否則日志空間將無限期延長。數(shù)據(jù)庫恢復(fù)02數(shù)據(jù)庫恢復(fù)概述2．數(shù)據(jù)庫恢復(fù)模式（3）大容量日志恢復(fù)模式。數(shù)據(jù)庫管理人員在某些時候需要對SQLServer2019簡單恢復(fù)模式數(shù)據(jù)庫進行一些大批量（如幾千條）的數(shù)據(jù)錄入、更新、刪除操作，如果工作在完整恢復(fù)模式下，會產(chǎn)生大量的日志記錄，導(dǎo)致數(shù)據(jù)庫性能下降。這時可以使數(shù)據(jù)庫工作在大容量日志恢復(fù)模式下，這樣可以大大減少日志記錄，減少I/O讀寫，提高數(shù)據(jù)庫性能。數(shù)據(jù)庫恢復(fù)02執(zhí)行數(shù)據(jù)庫恢復(fù)由于備份和恢復(fù)是兩個相關(guān)聯(lián)的過程，制定備份策略的同時，也就確定了恢復(fù)策略；同樣，選擇恢復(fù)策略的同時，也就確定了備份策略，所以這里把備份和恢復(fù)放在一起分析。SQLServer2019共有四種備份和恢復(fù)類型。（1）完整數(shù)據(jù)庫備份和恢復(fù)：對整個數(shù)據(jù)庫進行備份，包括數(shù)據(jù)文件和日志文件，需要較大的備份空間，需要恢復(fù)時進行完整數(shù)據(jù)庫恢復(fù)。（2）差異備份或稱增量備份和恢復(fù)：在執(zhí)行一次完整的數(shù)據(jù)庫備份后，僅僅備份對數(shù)據(jù)庫修改的內(nèi)容，比完整數(shù)據(jù)庫備份小且備份速度快，因此可以經(jīng)常備份。（3）事務(wù)日志備份和恢復(fù)：事務(wù)日志是自上次備份事務(wù)日志后對數(shù)據(jù)庫執(zhí)行的所有事務(wù)的一系列記錄，可以使用事務(wù)日志備份將數(shù)據(jù)庫恢復(fù)到特定的即時點或故障點。（4）數(shù)據(jù)庫文件及文件組備份和恢復(fù)完整備份特定的數(shù)據(jù)文件或者數(shù)據(jù)文件組。數(shù)據(jù)庫恢復(fù)02執(zhí)行數(shù)據(jù)庫恢復(fù)還原數(shù)據(jù)庫可以使用RESTOREDATABASE語句完成，還原數(shù)據(jù)庫根據(jù)不同的類型，有不同的語句格式。使用T-SQL語句執(zhí)行恢復(fù)03數(shù)據(jù)庫的分離與附加數(shù)據(jù)庫的分離與附加03分離和附加概述可以分離某個SQLServer實例中的數(shù)據(jù)庫的數(shù)據(jù)文件和事務(wù)日志文件，然后將它們重新附加到同一或其他SQLServer實例上。如果要將數(shù)據(jù)庫更改到同一計算機的不同SQL

Server實例上或移動數(shù)據(jù)庫，分離和附加數(shù)據(jù)庫會很有用。其中，分離數(shù)據(jù)庫是將數(shù)據(jù)庫從SQLServer數(shù)據(jù)庫引擎實例中刪除，但保留完整的數(shù)據(jù)庫及其數(shù)據(jù)文件和事務(wù)日志文件；附加數(shù)據(jù)庫是附加復(fù)制的或分離的SQLServer數(shù)據(jù)庫，附加數(shù)據(jù)庫時，數(shù)據(jù)庫包含的全文文件隨數(shù)據(jù)庫一起附加。在SQLServer2019中，除了系統(tǒng)數(shù)據(jù)庫之外，其余的數(shù)據(jù)庫都可以從服務(wù)器中分離出來，脫離當前服務(wù)器的管理。被分離的數(shù)據(jù)庫保持了數(shù)據(jù)文件和日志文件的完整性及一致性。被分離出的數(shù)據(jù)庫還可以通過附加功能附加到其他SQLServer2019服務(wù)器上，重新構(gòu)成完整的數(shù)據(jù)庫，附加得到的數(shù)據(jù)庫和分離時的數(shù)據(jù)庫完全一致。被分離的數(shù)據(jù)庫在執(zhí)行分離操作時一定不能被其他用戶使用。數(shù)據(jù)庫的分離與附加03分離數(shù)據(jù)庫使用T-SQL語句分離數(shù)據(jù)庫在SQLServer2019中，使用存儲過程sp_detach_db可以實現(xiàn)數(shù)據(jù)庫的分離。但只有sysadmin固定服務(wù)器角色的成員才能執(zhí)行sp_detach_db，其基本語句格式如下:數(shù)據(jù)庫的分離與附加03附加數(shù)據(jù)庫使用T-SQL語句附加數(shù)據(jù)庫在SQLServer2019中,使用存儲過程sp_attach_db可以實現(xiàn)數(shù)據(jù)庫的附加。只有sysadmin和dbcreator固定服務(wù)器角色的成員才能執(zhí)行此過程，其基本語句格式如下:04數(shù)據(jù)導(dǎo)入和導(dǎo)出數(shù)據(jù)導(dǎo)入和導(dǎo)出04數(shù)據(jù)導(dǎo)入和導(dǎo)出概述SQLServer2019中提供了數(shù)據(jù)導(dǎo)入/導(dǎo)出功能，以使用數(shù)據(jù)轉(zhuǎn)換服務(wù)（DTS）在不同類型的數(shù)據(jù)源之間導(dǎo)入和導(dǎo)出數(shù)據(jù)。通過數(shù)據(jù)導(dǎo)入/導(dǎo)出操作可以完成SQLServer2019數(shù)據(jù)庫和其他類型數(shù)據(jù)庫（如Excel表格、Access數(shù)據(jù)庫和Oracle數(shù)據(jù)庫）之間的數(shù)據(jù)轉(zhuǎn)換，從而實現(xiàn)不同應(yīng)用系統(tǒng)之間的數(shù)據(jù)移植和共享。數(shù)據(jù)導(dǎo)入和導(dǎo)出04數(shù)據(jù)導(dǎo)出【任務(wù)5-1】使用SQLServerManagementStudio將WebShop數(shù)據(jù)庫的數(shù)據(jù)導(dǎo)出到Excel文件WebShop.xlsx中。（1）啟動SQLServerManagementStudio，在“對象資源管理器”中展開【數(shù)據(jù)庫】節(jié)點。（2）右擊【W(wǎng)ebShop】節(jié)點，在彈出的快捷菜單中選擇【任務(wù)】→【導(dǎo)出數(shù)據(jù)】選項，如圖12-23所示。或者在“對象資源管理器”中右擊【管理】節(jié)點，在彈出的快捷菜單中選擇【導(dǎo)出數(shù)據(jù)】選項，如圖12-24所示。數(shù)據(jù)導(dǎo)入和導(dǎo)出04數(shù)據(jù)導(dǎo)出（3）打開“SQLServer導(dǎo)入和導(dǎo)出向?qū)А睂υ捒?。?）單擊【Next】按鋁，打開“選擇數(shù)據(jù)源”對話框，在【數(shù)據(jù)源】中選擇“SQLServerNativeClient11.0”，表示將從SQLServer中導(dǎo)出數(shù)據(jù);也可以根據(jù)實際情況設(shè)置【身份驗證】模式和選擇【數(shù)據(jù)庫】項目，如圖12-25所示。數(shù)據(jù)導(dǎo)入和導(dǎo)出04數(shù)據(jù)導(dǎo)出（5）單擊【Next】按鈕，打開“選擇目標”對話框，在【目標】中選擇“MicrosoftExcel"，表示把數(shù)據(jù)導(dǎo)出到Excel表中；也可以根據(jù)實際情況設(shè)置【Excel文件路徑】和【Excel版本】等，如圖12-26所示。數(shù)據(jù)導(dǎo)入和導(dǎo)出04數(shù)據(jù)導(dǎo)出（6）單擊【Next】按鈕，打開“指定表復(fù)制或查詢”對話框，默認選中“復(fù)制一個或多個表或視圖的數(shù)據(jù)”單選按鈕;也可以根據(jù)實際情況選中“編寫查詢以指定要傳輸?shù)臄?shù)據(jù)”單選按鈕，如圖12-27所示。數(shù)據(jù)導(dǎo)入和導(dǎo)出04數(shù)據(jù)導(dǎo)出（7）單擊【Next】按鈕，打開“選擇源表和源視圖”對話框，如圖12-28所示。選中WebShop數(shù)據(jù)庫中的Customers表和Goods表，單擊【編輯映射】按鈕，可以編輯源數(shù)據(jù)和目標數(shù)據(jù)之間的映射關(guān)系，如圖12-29所示。數(shù)據(jù)導(dǎo)入和導(dǎo)出04數(shù)據(jù)導(dǎo)出（8）單擊【Next】按鈕，打開“查看數(shù)據(jù)類型映射”對話框，如圖12-30所示。（9）單擊【Next】按鈕，打開“保存并運行包”對話框，如圖12-31所示。數(shù)據(jù)導(dǎo)入和導(dǎo)出04數(shù)據(jù)導(dǎo)出（10）單擊【Next】按鈕，打開“完成該向?qū)А睂υ捒?，如圖12-32所示。（11）單擊【完成】按鈕，打開“執(zhí)行成功”對話框，如圖12-33所示。在D:kdata文件夾中生成WebShop.xlsx文件，該文件的內(nèi)容如圖12-34所示。數(shù)據(jù)導(dǎo)入和導(dǎo)出04數(shù)據(jù)導(dǎo)出數(shù)據(jù)導(dǎo)入和導(dǎo)出04數(shù)據(jù)導(dǎo)入【任務(wù)5-2】使用SQLServerManageme