Restore a Database Backup Using SSMSRestore a Database Backup Using SSMS

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

本主題說明如何使用 SQL Server Management Studio 還原完整資料庫備份。This topic explains how to restore a full database backup using SQL Server Management Studio.

重要!Important!

您可能必須先備份使用中的交易記錄 (也稱為 記錄結尾),才能在完整或大量記錄復原模式下還原資料庫。Before you can restore a database under the full or bulk-logged recovery model, you may need to back up the active transaction log (known as tail of the log. 如需詳細資訊,請參閱 備份交易記錄 (SQL Server)),才能在完整或大量記錄復原模式下還原資料庫。For more information, see Back Up a Transaction Log (SQL Server).

從另一個執行個體還原資料庫時,請考慮 在另一個伺服器執行個體上提供可用的資料庫時,管理中繼資料 (SQL Server)中的資訊。When restoring a database from another instance, consider the information from Manage Metadata When Making a Database Available on Another Server Instance (SQL Server).

若要還原加密的資料庫,您必須能夠存取用來加密該資料庫的憑證或非對稱金鑰。To restore an encrypted database, you need access to the certificate or asymmetric key used to encrypt that database. 如果沒有此憑證或非對稱金鑰,您就無法還原該資料庫。Without the certificate or asymmetric key, you cannot restore that database. 只要您需要儲存備份,就必須保留用來加密資料庫加密金鑰的憑證。You must retain the certificate used to encrypt the database encryption key for as long as you need to save the backup. 如需詳細資訊,請參閱 SQL Server Certificates and Asymmetric KeysFor more information, see SQL Server Certificates and Asymmetric Keys.

如果您將舊版資料庫還原至 SQL Server 2017SQL Server 2017,該資料庫會自動升級至 SQL Server 2017SQL Server 2017If you restore an older version database to SQL Server 2017SQL Server 2017, that database will automatically upgrade to SQL Server 2017SQL Server 2017. 這可防止搭配 Database EngineDatabase Engine 的較舊版本使用資料庫。This precludes the database from being used with an older version of the Database EngineDatabase Engine. 但是,這與中繼資料狀態相關,且不會影響資料庫相容性層級However, this relates to metadata upgrade and does not affect the database compatibility level. 如果使用者資料庫的相容性層級在升級前為 100 或更高層級,則在升級後仍會保持相同。If the compatibility level of a user database is 100 or higher before upgrade, it remains the same after upgrade. 如果升級前的相容性層級為 90,則在升級後的資料庫中,相容性層級會設定為 100 (這是 SQL Server 2017SQL Server 2017 所支援的最低相容性層級)。If the compatibility level is 90 before upgrade, in the upgraded database, the compatibility level is set to 100, which is the lowest supported compatibility level in SQL Server 2017SQL Server 2017. 如需詳細資訊,請參閱 ALTER DATABASE 相容性層級 (Transact-SQL)For more information, see ALTER DATABASE Compatibility Level (Transact-SQL).

通常,資料庫立即變為可用。Typically, the database becomes available immediately. 不過,如果 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 資料庫具有全文檢索索引,升級程序就會根據 [全文檢索升級選項] 伺服器屬性的設定,匯入、重設或重建這些索引。However, if a SQL Server 2005 (9.x)SQL Server 2005 (9.x) database has full-text indexes, the upgrade process either imports, resets, or rebuilds the indexes, depending on the setting of the Full-Text Upgrade Option server property. 如果您將升級選項設定為 [匯入] 或 [重建],則全文檢索索引在升級期間將無法使用。If you set upgrade option to Import or Rebuild, the full-text indexes will be unavailable during the upgrade. 根據進行索引的資料數量而定,匯入可能需要數個小時,而重建將需要十倍以上的時間。Depending on the amount of data being indexed, importing can take several hours; rebuilding will take up to ten times longer.

當您將升級選項設定為 [匯入] 時,如果全文檢索目錄無法使用,系統就會重建相關聯的全文檢索索引。When you set upgrade option to Import, if a full-text catalog is not available, the associated full-text indexes are rebuilt. 如需有關檢視或變更 全文檢索目錄升級選項 屬性設定的詳細資訊,請參閱< 管理及監視伺服器執行個體的全文檢索搜尋>。For information about viewing or changing the setting of the Full-Text Upgrade Option property, see Manage and Monitor Full-Text Search for a Server Instance.

如需從 Microsoft Azure Blob 儲存體服務還原 SQL Server 的資訊,請參閱使用 Microsoft Azure Blob 儲存體服務進行 SQL Server 備份及還原For information on SQL Server restore from the Microsoft Azure Blob storage service, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service.

範例Examples

A.A. 還原完整資料庫備份Restore a full database backup

  1. [物件總管] 中,連接到 SQL Server Database EngineSQL Server Database Engine 的執行個體,然後展開該執行個體。In Object Explorer, connect to an instance of the SQL Server Database EngineSQL Server Database Engine and then expand that instance.

  2. 以滑鼠右鍵按一下 [資料庫],然後選取 [還原資料庫...]Right-click Databases and select Restore Database...

  3. [一般] 頁面上,使用 [來源] 區段指定要還原之備份組的來源和位置。On the General page, use the Source section to specify the source and location of the backup sets to restore. 選取下列其中一個選項:Select one of the following options:

    • [資料庫備份]Database

      從下拉式清單中選取要還原的資料庫。Select the database to restore from the drop-down list. 此清單僅包含已根據 msdb 備份記錄而備份的資料庫。The list contains only databases that have been backed up according to the msdb backup history.

      注意

      如果備份是根據不同的伺服器建立的,目的地伺服器就沒有指定之資料庫的備份記錄資訊。If the backup is taken from a different server, the destination server will not have the backup history information for the specified database. 在此情況下,請選取 [裝置] ,以便手動指定要還原的檔案或裝置。In this case, select Device to manually specify the file or device to restore.

    • [裝置]Device

      按一下瀏覽 (...) 按鈕,開啟 [選取備份裝置] 對話方塊。Click the browse (...) button to open the Select backup devices dialog box.

      • [選取備份裝置] 對話方塊Select backup devices dialog box

        備份媒體類型Backup media type
        從 [備份媒體類型] 下拉式清單中選取媒體類型。Select a media type from the Backup media type drop-down list. 注意:只有在電腦上有掛載磁帶機時才會出現 [磁帶] 選項,而只有在至少有一個備份裝置時才會出現 [備份裝置] 選項。Note: The Tape option appears only if a tape drive is mounted on the computer, and the Backup Device option appears, only if at least one backup device exists.

        [加入]Add
        依據您從 [備份媒體類型] 下拉式清單中選取的媒體類型,按一下 [加入] 就會開啟下列其中一個對話方塊。Depending on the type of media you select from the Backup media type drop-down list, clicking Add opens one of the following dialog boxes. (如果 [備份媒體] 清單方塊中的清單已滿,[加入] 按鈕便無法使用。)(If the list in the Backup media list box is full, the Add button is unavailable.)

        媒體類型Media type 對話方塊Dialog box DescriptionDescription
        檔案File 尋找備份檔案Locate Backup File 在這個對話方塊中,您可以從樹狀目錄中選取本機檔案,或是使用完整的通用命名慣例 (UNC) 名稱指定遠端檔案。In this dialog box, you can select a local file from the tree or specify a remote file using its fully qualified universal naming convention (UNC) name. 如需詳細資訊,請參閱 備份裝置 (SQL Server)執行個體上建立資料庫備份,就需要這個選項。For more information, see Backup Devices (SQL Server).
        裝置Device 選取備份裝置Select Backup Device 在這個對話方塊中,您可以從伺服器執行個體上所定義的邏輯備份裝置清單中選取裝置。In this dialog box, you can select from a list of the logical backup devices defined on the server instance.
        TapeTape 選取備份磁帶Select Backup Tape 在這個對話方塊中,您可以從實際連接到執行 SQL ServerSQL Server執行個體之電腦的磁帶機清單中選取磁帶。In this dialog box, you can select from a list of the tape drives that are physically connected to the computer running the instance of SQL ServerSQL Server.
        URLURL 選取備份檔案位置Select a Backup File Location 在此對話方塊中,您可以選取現有的 SQL Server 認證/Azure 儲存體容器、使用共用存取簽章新增 Azure 儲存體容器,或為現有的儲存體容器產生共用存取簽章和 SQL Server 認證。In this dialog box, you can select an existing SQL Server credential/Azure storage container, add a new Azure storage container with a shared access signature, or generate a shared access signature and SQL Server credential for an existing storage container. 另請參閱 連接到 Microsoft Azure 訂用帳戶See also, Connect to a Microsoft Azure Subscription

        移除 Remove
        移除一個或多個選取的檔案、磁帶或邏輯備份裝置。Removes one or more selected files, tapes, or logical backup devices.

        目錄 Contents
        顯示選取之檔案、磁帶或邏輯備份裝置的媒體內容。Displays the media contents of a selected file, tape, or logical backup device. 如果媒體類型為 [URL],此按鈕可能沒有作用。This button may not function if the media type is URL.

        備份媒體 Backup media
        列出選取的媒體。Lists the selected media.

        將您要的裝置加入 [備份媒體] 清單方塊後,按一下 [確定] 即可回到 [一般] 頁面。After you add the devices you want to the Backup media list box, click OK to return to the General page.

      [來源:裝置:資料庫] 清單方塊中,選取應該還原的資料庫名稱。In the Source: Device: Database list box, select the name of the database which should be restored.

      注意

      這份清單只能在選取 [裝置] 時使用。This list is only available when Device is selected. 只有在所選取裝置上有備份的資料庫才可供使用。Only databases that have backups on the selected device will be available.

  4. [目的地] 區段中,會將要還原之資料庫的名稱自動填入 [資料庫] 方塊。In the Destination section, the Database box is automatically populated with the name of the database to be restored. 若要變更資料庫的名稱,請在 [資料庫] 方塊中輸入新名稱。To change the name of the database, enter the new name in the Database box.

  5. [還原至] 方塊中,保留 [到上次建立的備份] 預設值,或按一下 [時間表] 存取 [備份時間表] 對話方塊,手動選取停止復原動作的時間點。In the Restore to box, leave the default as To the last backup taken or click on Timeline to access the Backup Timeline dialog box to manually select a point in time to stop the recovery action. 如需有關指定特定時間點的詳細資訊,請參閱< Backup Timeline>。For more information on designating a specific point in time, see Backup Timeline.

  6. [要還原的備份組] 方格中,選取要還原的備份。In the Backup sets to restore grid, select the backups to restore. 這個方格會顯示指定位置可用的備份。This grid displays the backups available for the specified location. 依預設,會建議一個復原計畫。By default, a recovery plan is suggested. 若要覆寫建議的復原計畫,您可以變更方格中的選取項目。To override the suggested recovery plan, you can change the selections in the grid. 取消選取先前的備份時,會自動取消選取相依於先前備份還原的備份。Backups that depend on the restoration of an earlier backup are automatically deselected when the earlier backup is deselected. 如需 [要還原的備份組] 方格中各資料行的資訊,請參閱還原資料庫 (一般頁面)For information about the columns in the Backup sets to restore grid, see Restore Database (General Page).

  7. (選擇性) 按一下 [選取頁面] 窗格中的 [檔案] 存取 [檔案] 對話方塊。Optionally, click Files in the Select a page pane to access the Files dialog box. 如此,您就可以在 [將資料庫檔案還原為] 方格中,為每個檔案指定新的還原目的地,藉以將資料庫還原到新的位置。From here, you can restore the database to a new location by specifying a new restore destination for each file in the Restore the database files as grid. 如需這個方格的詳細資訊,請參閱還原資料庫 (檔案頁面)For more information about this grid, see Restore Database (Files Page).

  8. 若要檢視或選取進階選項,在 [選項] 頁面的 [還原選項] 面板中,您可以選取下列任何選項 (如果情況適用):To view or select the advanced options, on the Options page, in the Restore options panel, you can select any of the following options, if appropriate for your situation:

    1. WITH 選項 (非必要):WITH options (not required):
    • 覆寫現有的資料庫 (WITH REPLACE)Overwrite the existing database (WITH REPLACE)

    • 保留複寫設定 (WITH KEEP_REPLICATION)Preserve the replication settings (WITH KEEP_REPLICATION)

    • 限制對還原資料庫的存取 (WITH RESTRICTED_USER)Restrict access to the restored database (WITH RESTRICTED_USER)

    1. 針對 [還原狀態] 方塊,選取選項。Select an option for the Recovery state box. 此方塊決定資料庫在還原作業之後的狀態。This box determines the state of the database after the restore operation.
    • RESTORE WITH RECOVERY 是預設行為,透過回復未認可的交易,讓資料庫保持備妥可用。RESTORE WITH RECOVERY is the default behavior which leaves the database ready for use by rolling back the uncommitted transactions. 無法還原其他交易記錄。Additional transaction logs cannot be restored. 若您要立即還原所有必要的備份,請選取這個選項。Select this option if you are restoring all of the necessary backups now.

    • RESTORE WITH NORECOVERY ,讓資料庫保持不運作,且不回復未認可的交易。RESTORE WITH NORECOVERY which leaves the database non-operational, and does not roll back the uncommitted transactions. 可以還原其他交易記錄。Additional transaction logs can be restored. 資料庫在復原之前都無法使用。The database cannot be used until it is recovered.

    • RESTORE WITH STANDBY ,讓資料庫處於唯讀模式。RESTORE WITH STANDBY which leaves the database in read-only mode. 它會復原未認可的交易,但會將復原動作儲存在待命資料庫檔案中,以還原復原影響。It undoes uncommitted transactions, but saves the undo actions in a standby file so that recovery effects can be reverted.

    1. 還原前先進行結尾記錄備份。Take tail-log backup before restore. 並不是所有的還原實例都需要結尾記錄備份。Not all restore scenarios require a tail-log backup. 如需詳細資訊,請參閱結尾記錄備份 (SQL Server) 中的<需要結尾記錄備份的實例>。For more information, see Scenarios That Require a Tail-Log Backup from Tail-Log Backups (SQL Server).

    2. 若資料庫有使用中的連接,還原作業可能會失敗。Restore operations may fail if there are active connections to the database. 核取 [關閉現有的連接選項] ,確定已關閉 Management StudioManagement Studio 與資料庫之間的所有使用中連接。Check the Close existing connections option to ensure that all active connections between Management StudioManagement Studio and the database are closed. 這個核取方塊會在執行還原作業之前將資料庫設定為單一使用者模式,並在完成後將資料庫設定為多使用者模式。This check box sets the database to single user mode before performing the restore operations, and sets the database to multi-user mode when complete.

    3. 如果想要系統在每個還原作業之間提示您,請選取 [還原每個備份之前先提示]Select Prompt before restoring each backup if you wish to be prompted between each restore operation. 除非資料庫夠大,而且您想要監視還原作業的狀態,否則這通常不需要。This is not usually necessary unless the database is large and you wish to monitor the status of the restore operation.

如需這些還原選項的詳細資訊,請參閱還原資料庫 (選項頁面)For more information about these restore options, see Restore Database (Options Page).

  1. 按一下 [確定] 。Click OK.

B.B. 以覆蓋現有資料庫的方式還原先前的磁碟備份Restore an earlier disk backup over an existing database

下列範例會還原先前對 Sales 執行的磁碟備份,並覆寫現有的 Sales 資料庫。The following example restores an earlier disk backup of Sales and overwrites the existing Sales database.

  1. [物件總管] 中,連接到 SQL Server Database EngineSQL Server Database Engine 的執行個體,然後展開該執行個體。In Object Explorer, connect to an instance of the SQL Server Database EngineSQL Server Database Engine and then expand that instance.

  2. 以滑鼠右鍵按一下 [資料庫],然後選取 [還原資料庫...]Right-click Databases and select Restore Database...

  3. 在 [一般] 頁面上,選取 [來源] 區段下的 [裝置]。On the General page, select Device under the Source section.

  4. 按一下瀏覽 (...) 按鈕,開啟 [選取備份裝置] 對話方塊。Click the browse (...) button to open the Select backup devices dialog box. 按一下 [加入] 並巡覽至您的備份。Click Add and navigate to your backup. 選取您的磁碟備份檔案,然後按一下 [確定]。Click OK after you have selected your disk backup file(s).

  5. 按一下 [確定] 回到 [一般] 頁面。Click OK to return to the General page.

  6. 在 [選取頁面] 窗格中,按一下 [選項]。Click Options in the Select a page pane.

  7. 在 [還原選項] 區段下,核取 [覆寫現有的資料庫 (WITH REPLACE)]。Under the Restore options section, check Overwrite the existing database (WITH REPLACE).

    注意

    未選取此選項可能會導致下列錯誤訊息:"System.Data.SqlClient.SqlError:備份組包含現有的 'Sales' 資料庫以外的資料庫備份。Not checking this option may result in the following error message: "System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'Sales' database. (Microsoft.SqlServer.SmoExtended)」(Microsoft.SqlServer.SmoExtended)"

  8. 在 [結尾記錄備份] 區段下,取消核取 [還原前先進行結尾記錄備份]。Under the Tail-log backup section, uncheck Take tail-log backup before restore.

    注意

    並不是所有的還原實例都需要結尾記錄備份。Not all restore scenarios require a tail-log backup. 如果復原點是包含在較早的記錄備份中,則不需要結尾記錄備份。You do not need a tail-log backup if the recovery point is contained in an earlier log backup. 而且,如果您要移動或取代 (覆寫) 資料庫,而且不需要將它還原至最近備份之後的某個時間點,就不需要有結尾記錄備份。Also, a tail-log backup is unnecessary if you are moving or replacing (overwriting) a database and do not need to restore it to a point of time after its most recent backup. 如需詳細資訊,請參閱 結尾記錄備份 (SQL Server)For more information, see Tail-Log Backups (SQL Server).

    簡單復原模式下的資料庫無法使用此選項。This option is not available for databases in the SIMPLE recovery model.

  9. 在 [伺服器連接] 區段下,核取 [關閉目的地資料庫的現有連接]。Under the Server connections section, check Close existing connections to destination database.

    注意

    未選取此選項可能會導致下列錯誤訊息:"System.Data.SqlClient.SqlError:無法獲得獨佔存取權,因為資料庫正在使用中。Not checking this option may result in the following error message: "System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.SmoExtended)」(Microsoft.SqlServer.SmoExtended)"

  10. 按一下 [確定] 。Click OK.

C.C. 在原始資料庫仍存在的情況下,使用新的資料庫名稱還原先前的磁碟備份Restore an earlier disk backup with a new database name where the original database still exists

下列範例會還原先前對 Sales 執行的磁碟備份,並建立新的資料庫 SalesTestThe following example restores an earlier disk backup of Sales and creates a new database called SalesTest. 原始的資料庫 Sales仍存在於伺服器上。The original database, Sales, still exists on the server.

  1. [物件總管] 中,連接到 SQL Server Database EngineSQL Server Database Engine 的執行個體,然後展開該執行個體。In Object Explorer, connect to an instance of the SQL Server Database EngineSQL Server Database Engine and then expand that instance.

  2. 以滑鼠右鍵按一下 [資料庫],然後選取 [還原資料庫...]Right-click Databases and select Restore Database...

  3. 在 [一般] 頁面上,選取 [來源] 區段下的 [裝置]。On the General page, select Device under the Source section.

  4. 按一下瀏覽 (...) 按鈕,開啟 [選取備份裝置] 對話方塊。Click the browse (...) button to open the Select backup devices dialog box. 按一下 [加入] 並巡覽至您的備份。Click Add and navigate to your backup. 選取您的磁碟備份檔案,然後按一下 [確定]。Click OK after you have selected your disk backup file(s).

  5. 按一下 [確定] 回到 [一般] 頁面。Click OK to return to the General page.

  6. [目的地] 區段中,會將要還原之資料庫的名稱自動填入 [資料庫] 方塊。In the Destination section, the Database box is automatically populated with the name of the database to be restored. 若要變更資料庫的名稱,請在 [資料庫] 方塊中輸入新名稱。To change the name of the database, enter the new name in the Database box.

  7. 在 [選取頁面] 窗格中,按一下 [選項]。Click Options in the Select a page pane.

  8. 在 [結尾記錄備份] 區段下,取消核取 [還原前先進行結尾記錄備份]。Under the Tail-log backup section, uncheck "Take tail-log backup before restore".

    重要

    無法取消核取此選項將會導致現有的資料庫 Sales變更為正在還原狀態。Not unchecking this option will result in the existing database, Sales, to change to the restoring state.

  9. 按一下 [確定] 。Click OK.

    注意

    若您收到下列錯誤訊息:If you receive the following error message:
    "System.Data.SqlClient.SqlError:資料庫 "Sales" 的記錄結尾尚未備份。"System.Data.SqlClient.SqlError: The tail of the log for the database "Sales" has not been backed up. 若其中包含您不想遺失的內容,請使用 BACKUP LOG WITH NORECOVERY 備份記錄。Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. 亦可使用 RESTORE 陳述式的 WITH REPLACEWITH STOPAT 子句來覆寫記錄的內容。Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log. (Microsoft.SqlServer.SmoExtended)」。(Microsoft.SqlServer.SmoExtended)".
    則您可能未在上述步驟 6 中輸入新的資料庫名稱。Then you likely did not enter the new database name from Step 6, above. 還原通常可以防止意外將資料庫覆寫成不同資料庫。Restore normally prevents accidentally overwriting a database with a different database. 如果 RESTORE 陳述式中指定的資料庫已經存在於目前伺服器,而且所指定資料庫系列 GUID 與備份組中記錄的資料庫系列 GUID 不同,便不會還原資料庫。If the database specified in a RESTORE statement already exists on the current server and the specified database family GUID differs from the database family GUID recorded in the backup set, the database is not restored. 這是重要的防護措施。This is an important safeguard.

D.D. 將先前的磁碟備份還原至某個時間點Restore earlier disk backups to a point in time

下列範例會將資料庫還原至 1:23:17 PM May 30, 2016 時的狀態,並顯示含有多個記錄備份的還原作業。The following example restores a database to its state as of 1:23:17 PM on May 30, 2016 and shows a restore operation that involves multiple log backups. 資料庫目前不在伺服器上。The database does not currently exist on the server.

  1. [物件總管] 中,連接到 SQL Server Database EngineSQL Server Database Engine 的執行個體,然後展開該執行個體。In Object Explorer, connect to an instance of the SQL Server Database EngineSQL Server Database Engine and then expand that instance.
  2. 以滑鼠右鍵按一下 [資料庫],然後選取 [還原資料庫...]Right-click Databases and select Restore Database...
  3. 在 [一般] 頁面上,選取 [來源] 區段下的 [裝置]。On the General page, select Device under the Source section.
  4. 按一下瀏覽 (...) 按鈕,開啟 [選取備份裝置] 對話方塊。Click the browse (...) button to open the Select backup devices dialog box. 按一下 [加入] 並巡覽至您的完整備份及所有相關交易記錄備份。Click Add and navigate to your full backup and all relevant transaction log backups. 選取您的磁碟備份檔案,然後按一下 [確定]。Click OK after you have selected your disk backup files.
  5. 按一下 [確定] 回到 [一般] 頁面。Click OK to return to the General page.
  6. 在 [目的地] 區段中,按一下 [時間表] 存取 [備份時間表] 對話方塊,手動選取停止復原動作的時間點。In the Destination section, click on Timeline to access the Backup Timeline dialog box to manually select a point in time to stop the recovery action.
  7. 選取 [特定的日期與時間]。Select Specific date and time.
  8. 將 [時間表間隔] 變更為下拉式方塊中的 [小時] (選擇性)。Change the Timeline interval to Hour in the drop down box (optional).
  9. 將滑桿移至想要的時間。Move the slider to the desired time.
  10. 按一下 [確定] 回到 [一般] 頁面。Click OK to return to the General page.
  11. 按一下 [確定] 。Click OK.

E.E. 從 Microsoft Azure 儲存體服務還原備份Restore a backup from the Microsoft Azure storage service

通用步驟Common Steps

下列兩個範例會從 Microsoft Azure 儲存體服務中的備份執行 Sales 的還原。The two examples below perform a restore of Sales from a backup located in the Microsoft Azure storage service. 儲存體帳戶名稱為 mystorageaccountThe storage Account name is mystorageaccount. 容器名稱為 myfirstcontainerThe container is called myfirstcontainer. 為求簡潔,前六個步驟只會在此列出一次,所有範例將從步驟 7 開始進行。For brevity, the first six steps are listed here once and all examples will start on Step 7.

  1. 在物件總管中,連接到 SQL Server Database Engine 的執行個體,然後展開該執行個體。In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
  2. 以滑鼠右鍵按一下 [資料庫],然後選取 [還原資料庫...]。Right-click Databases and select Restore Database....
  3. 在 [一般] 頁面上,選取 [來源] 區段下的 [裝置]。On the General page, select Device under the Source section.
  4. 按一下瀏覽 (...) 按鈕,開啟 [選取備份裝置] 對話方塊。Click the browse (...) button to open the Select backup devices dialog box.
  5. 從 [備份媒體類型:] 下拉式清單中選取 [URL]。Select URL from the Backup media type: drop-down list.
  6. 按一下 [加入],[選取備份檔案位置] 對話方塊隨即開啟。Click Add and the Select a Backup File Location dialog box opens.

E1.E1. 以覆蓋現有資料庫的方式還原等量備份,而且存在共用存取簽章。Restore a striped backup over an existing database and a shared access signature exists.

已建立具有讀取、寫入、刪除和列出權限的預存存取原則。A stored access policy has been created with read, write, delete, and list rights. 為容器 https://mystorageaccount.blob.core.windows.net/myfirstcontainer建立了與此預存存取原則相關聯的共用存取簽章。A shared access signature that is associated with the stored access policy was created for the container https://mystorageaccount.blob.core.windows.net/myfirstcontainer. 如果 SQL Server 認證已經存在,則步驟大致相同。The steps are mostly the same if a SQL Server credential already exists. 資料庫 Sales 目前不在伺服器上。The database Sales currently exists on the server. 備份檔案為 Sales_stripe1of2_20160601.bakSales_stripe2of2_20160601.bakThe backup files are Sales_stripe1of2_20160601.bak and Sales_stripe2of2_20160601.bak.

  1. 如果 SQL Server 認證已經存在,請從 [Azure 儲存體容器:] 下拉式清單中選取 https://mystorageaccount.blob.core.windows.net/myfirstcontainer;否則請手動輸入容器 https://mystorageaccount.blob.core.windows.net/myfirstcontainer 的名稱。Select https://mystorageaccount.blob.core.windows.net/myfirstcontainer from the Azure storage container: drop-down list if the SQL Server credential already exists, else manually enter the name of the container, https://mystorageaccount.blob.core.windows.net/myfirstcontainer.
  2. 在 [共用存取簽章:] RTF 方塊中,輸入共用存取簽章。Enter the shared access signature in the Shared Access Signature: rich-text box.
  3. 按一下 [確定],[在 Microsoft Azure 中尋找備份檔案] 對話方塊隨即開啟。Click OK and the Locate Backup File in Microsoft Azure dialog box opens.
  4. 展開 [容器] 並巡覽至 https://mystorageaccount.blob.core.windows.net/myfirstcontainerExpand Containers and navigate to https://mystorageaccount.blob.core.windows.net/myfirstcontainer.
  5. 按住 Ctrl,然後選取檔案 Sales_stripe1of2_20160601.bakSales_stripe2of2_20160601.bakHold ctrl and select files Sales_stripe1of2_20160601.bak and Sales_stripe2of2_20160601.bak.
  6. 按一下 [確定] 。Click OK.
  7. 按一下 [確定] 回到 [一般] 頁面。Click OK to return to the General page.
  8. 在 [選取頁面] 窗格中,按一下 [選項]。Click Options in the Select a page pane.
  9. 在 [還原選項] 區段下,核取 [覆寫現有的資料庫 (WITH REPLACE)]。Under the Restore options section, check Overwrite the existing database (WITH REPLACE).
  10. 在 [結尾記錄備份] 區段下,取消核取 [還原前先進行結尾記錄備份]。Under the Tail-log backup section, uncheck Take tail-log backup before restore.
  11. 在 [伺服器連接] 區段下,核取 [關閉目的地資料庫的現有連接]。Under the Server connections section, check Close existing connections to destination database.
  12. 按一下 [確定] 。Click OK.

E2.E2. 共用存取簽章不存在A shared access signature does not exist

在此範例中,Sales 資料庫目前不在伺服器上。In this example the Sales database does not currently exist on the server.

  1. 按一下 [加入],[連接至 Microsoft 訂用帳戶] 對話方塊隨即開啟。Click Add and the Connect to a Microsoft Subscription dialog box will open.
  2. 完成 [連接至 Microsoft 訂用帳戶] 對話方塊,然後按一下 [確定] 回到 [選取備份檔案位置] 對話方塊。Complete the Connect to a Microsoft Subscription dialog box and then click OK to return the Select a Backup File Location dialog box. 如需其他資訊,請參閱連接到 Microsoft Azure 訂用帳戶See Connect to a Microsoft Azure Subscription for additional information.
  3. 在 [選取備份檔案位置] 對話方塊中按一下 [確定],[在 Microsoft Azure 中尋找備份檔案] 對話方塊隨即開啟。Click OK in the Select a Backup File Location dialog box and the Locate Backup File in Microsoft Azure dialog box opens.
  4. 展開 [容器] 並巡覽至 https://mystorageaccount.blob.core.windows.net/myfirstcontainerExpand Containers and navigate to https://mystorageaccount.blob.core.windows.net/myfirstcontainer.
  5. 選取備份檔案,然後按一下 [確定]。Select the backup file and then click OK.
  6. 按一下 [確定] 回到 [一般] 頁面。Click OK to return to the General page.
  7. 按一下 [確定] 。Click OK.

F.F. 將本機備份還原至 Microsoft Azure 儲存體 (URL)Restore local backup to Microsoft Azure storage (URL)

Sales 資料庫將從 E:\MSSQL\BAK 中的備份還原至 Microsoft Azure 儲存體容器 https://mystorageaccount.blob.core.windows.net/myfirstcontainerThe Sales database will be restored to the Microsoft Azure storage container https://mystorageaccount.blob.core.windows.net/myfirstcontainer from a backup located at E:\MSSQL\BAK. 已建立 Azure 容器的 SQL Server 認證。The SQL Server credential for the Azure container has already been created. 目的地容器的 SQL Server 認證必須已經存在,因為您無法透過 還原 工作建立此認證。A SQL Server credential for the destination container must already exist as it cannot be created through the Restore task. Sales 資料庫目前不在伺服器上。The Sales database does not currently exist on the server.

  1. 在物件總管中,連接到 SQL Server Database Engine 的執行個體,然後展開該執行個體。In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
  2. 以滑鼠右鍵按一下 [資料庫],然後選取 [還原資料庫...]。Right-click Databases and select Restore Database....
  3. 在 [一般] 頁面上,選取 [來源] 區段下的 [裝置]。On the General page, select Device under the Source section.
  4. 按一下瀏覽 (...) 按鈕,開啟 [選取備份裝置] 對話方塊。Click the browse (...) button to open the Select backup devices dialog box.
  5. 從 [備份媒體類型:] 下拉式清單中選取 [檔案]。Select File from the Backup media type: drop-down list.
  6. 按一下 [加入],[尋找備份檔案] 對話方塊隨即開啟。Click Add and the Locate Backup File dialog box opens.
  7. 巡覽至 E:\MSSQL\BAK 並選取備份檔案,然後按一下 [確定]。Navigate to E:\MSSQL\BAK, select the backup file and then click OK.
  8. 按一下 [確定] 回到 [一般] 頁面。Click OK to return to the General page.
  9. 在 [選取頁面] 窗格中,按一下 [檔案]。Click Files in the Select a page pane.
  10. 核取 [將所有檔案重新放置到資料夾] 方塊。Check the box Relocate all files to folder.
  11. 在 [資料檔案資料夾:] 和 [記錄檔資料夾:] 的文字方塊中,輸入容器 https://mystorageaccount.blob.core.windows.net/myfirstcontainerEnter the container, https://mystorageaccount.blob.core.windows.net/myfirstcontainer, in the text boxes for Data file folder: and Log file folder:.
  12. 按一下 [確定] 。Click OK.

另請參閱See Also

備份交易記錄 (SQL Server) Back Up a Transaction Log (SQL Server)
建立完整資料庫備份 (SQL Server) Create a Full Database Backup (SQL Server)
將資料庫還原到新位置 (SQL Server) Restore a Database to a New Location (SQL Server)
還原交易記錄備份 (SQL Server) Restore a Transaction Log Backup (SQL Server)
RESTORE (Transact-SQL) RESTORE (Transact-SQL)
還原資料庫 (選項頁面) Restore Database (Options Page)
還原資料庫 (一般頁面)Restore Database (General Page)