使用複製資料庫精靈Use the Copy Database Wizard

「複製資料庫精靈」可讓您輕鬆地在伺服器之間移動或複製資料庫及其物件,而不需要讓伺服器停機。The Copy Database Wizard lets you move or copy databases and their objects easily from one server to another, with no server downtime. 您也可以將資料庫從舊版 [SQL Server]SQL Server 升級至 SQL Server 2014SQL Server 2014You can also upgrade databases from a previous [SQL Server]SQL Server version to SQL Server 2014SQL Server 2014. 使用此精靈可以執行下列作業:By using this wizard, you can do the following:

  • 挑選來源和目的地伺服器。Pick a source and destination server.

  • 選取要移動、複製或升級的資料庫。Select databases to move, copy or upgrade.

  • 為資料庫指定檔案位置。Specify the file location for the databases.

  • 在目的地伺服器上建立登入。Create logins on the destination server.

  • 複製其他支援的物件、作業、使用者定義的預存程序和錯誤訊息。Copy additional supporting objects, jobs, user-defined stored procedures, and error messages.

  • 何時要移動或複製資料庫的排程。Schedule when to move or copy the databases.

除了複製資料庫以外,您還可以複製相關的中繼資料,例如,被複製之資料庫所需之 master 資料庫中的登入和物件。In addition to copying databases, you can copy associated metadata, for example, logins and objects from the master database that are required by a copied database.

本主題內容In This Topic

開始之前Before You Begin

限制事項Limitations and Restrictions

  • Express 版本不提供複製資料庫精靈。The Copy Database Wizard is not available in the Express edition.

  • 複製資料庫精靈無法用於複製或移動下列資料庫。The Copy Database Wizard cannot be used to copy or move the following databases.

    • 系統資料庫System databases

    • 要用於複寫的資料庫。Databases marked for replication.

    • 標示為「無法存取」、「正在載入」、「離線」、「正在復原」、「有疑問」或是「緊急模式」的資料庫。Databases marked Inaccessible, Loading, Offline, Recovering, Suspect, or in Emergency Mode.

  • 資料庫升級後,無法降級至舊版。After a database has been upgraded, it cannot be downgraded to a previous version.

  • 若您選取 [移動] 選項,則當移動資料庫之後,精靈會自動刪除來源資料庫。If you select the Move option, the wizard deletes the source database automatically after moving the database. 如果您選取 [複製] 選項,「複製資料庫精靈」就不會刪除來源資料庫。The Copy Database Wizard does not delete a source database if you select the Copy option.

  • 如果您使用 [SQL Server]SQL Server 管理物件方法來移動全文檢索目錄,您必須在移動之後重新擴展索引。If you use the [SQL Server]SQL Server Management Object method to move the full-text catalog, you must repopulate the index after the move.

  • 卸離和附加方法可卸離資料庫、移動或複製資料庫 .mdf、.ndf 和 .ldf 檔案,並在新的位置中重新附加資料庫。The detach-and-attach method detaches the database, moves or copies the database .mdf, .ndf, .ldf files and reattaches the database in the new location. 對於卸離和附加方法而言,為了避免資料遺失或不一致,使用中工作階段不能附加到正在移動或複製的資料庫。For the detach-and-attach method, to avoid data loss or inconsistency, active sessions cannot be attached to the database being moved or copied. 如果有任何使用中工作階段存在,「複製資料庫精靈」將不會執行移動或複製作業。If any active sessions exist, the Copy Database Wizard does not execute the move or copy operation. 對於 [SQL Server]SQL Server 管理物件方法而言,因為資料庫絕對不會離線,所以允許使用中工作階段。For the [SQL Server]SQL Server Management Object method, active sessions are allowed because the database is never taken offline.

必要條件Prerequisites

確定目的地伺服器上已啟動 SQL Server Agent。Ensure that SQL Server Agent is started on the destination server.

建議Recommendations

  • 為了確保升級的資料庫能有最佳效能,請針對升級的資料庫執行 sp_updatestats (更新統計資料)。To ensure optimal performance of an upgraded database, run sp_updatestats (update statistics) against the upgraded database.

  • 將資料庫複製到另一個伺服器執行個體時,為了提供一致的經驗給使用者和應用程式,您可能會需要在其他伺服器執行個體上為資料庫重新建立部分或所有中繼資料,例如登入和作業。When you copy a database to another server instance, to provide a consistent experience to users and applications, you might have to re-create some or all of the metadata for the database, such as logins and jobs, on the other server instance. 如需詳細資訊,請參閱 在另一個伺服器執行個體上提供可用的資料庫時,管理中繼資料 (SQL Server)For more information, see Manage Metadata When Making a Database Available on Another Server Instance (SQL Server).

安全性Security

權限Permissions

您必須在來源伺服器與目的地伺服器上成為 系統管理員 (sysadmin) 固定伺服器角色的成員。You must be a member of the sysadmin fixed server role on both the source and destination servers.

複製、 移動或升級資料庫Copy, Move or Upgrade Databases

  1. SQL Server Management StudioSQL Server Management Studio的 [物件總管] 中,展開 [資料庫],然後以滑鼠右鍵按一下資料庫,再指向 [工作],然後按一下 [複製資料庫]In SQL Server Management StudioSQL Server Management Studio, in Object Explorer, expand Databases, right-click a database, point to Tasks, and then click Copy Database.

  2. [選取來源伺服器] 頁面,指定要移動或複製之資料庫所在的伺服器,以及輸入登入資訊。From the Select a Source Server page, specify the server with the database to move or copy, and to enter login information. 在您選取驗證方法並輸入登入資訊之後,按 [下一步] 以建立與來源伺服器的連接。After you select the authentication method and enter login information, click Next to establish the connection to the source server. 在整個工作階段中,此連接會保持開啟。This connection remains open throughout the session.

    來源伺服器Source server
    選取您想要移動或複製之資料庫所在的伺服器名稱,或是按一下瀏覽 (...) 按鈕,尋找您要的伺服器。Select the name of the server on which the database or databases you want to move or copy are located, or click the browse (...) button to locate the server you want. 該伺服器必須至少為 SQL Server 2005SQL Server 2005The server must be at least SQL Server 2005SQL Server 2005.

    [使用 Windows 驗證]Use Windows Authentication
    可讓使用者透過 MicrosoftMicrosoft Windows 使用者帳戶連接。Allow a user to connect through a MicrosoftMicrosoft Windows user account.

    [使用 SQL Server 驗證]Use SQL Server Authentication
    可讓使用者經由提供 [SQL Server]SQL Server 驗證使用者名稱和密碼來進行連接。Allow a user to connect by providing a [SQL Server]SQL Server Authentication user name and password.

    使用者名稱User name
    輸入要用來連接的使用者名稱。Enter the user name to connect with. 這個選項只有在您選取了使用 [SQL Server]SQL Server 驗證連接時才可以使用。This option is only available if you have selected to connect using [SQL Server]SQL Server Authentication .

    密碼Password
    輸入登入的密碼。Enter the password for the login. 這個選項只有在您選取了使用 [SQL Server]SQL Server 驗證連接時才可以使用。This option is only available if you have selected to connect using [SQL Server]SQL Server Authentication.

    下一個Next
    連接到伺服器,並驗證使用者。Connect to the server and validate the user. 此處理序會檢查使用者是否為所選取電腦中的 [系統管理員 (sysadmin)] 固定伺服器角色成員。This process checks whether the user is a member of the sysadmin fixed server role on the selected computer.

  3. [選取目的地伺服器] 頁面,指定將移動或複製資料庫的伺服器。From the Select a Destination Server page, specify the server where the database will be moved or copied. 如果您將來源和目的地伺服器設定成同一個伺服器執行個體,您就會複製一個資料庫。If you set the source and destination servers to the same server instance, you will make a copy of a database. 在這種情況下,您必須稍後在精靈中重新命名此資料庫。In this case you must rename the database at a later point in the wizard. 只有當目的地伺服器上沒有名稱衝突時,才可以將來源資料庫名稱用於複製或移動的資料庫。The source database name can be used for the copied or moved database only if name conflicts do not exist on the destination server. 如果有名稱衝突存在,您必須先手動解決目的地伺服器上的衝突,然後才能在這裡使用來源資料庫名稱。If name conflicts exist, you must resolve them manually on the destination server before you can use the source database name there.

    目的地伺服器Destination server
    選取要移動或複製資料庫的目的地伺服器名稱,或按一下瀏覽 (...) 按鈕來尋找目的地伺服器。Select the name of the server to which the database or databases will be moved or copied, or click the browse (...) button to locate a destination server.

    注意

    您可以使用屬於叢集伺服器的目的地。「複製資料庫精靈」將會確保您只能選取叢集目的地伺服器上的共用磁碟機。You can use a destination that is a clustered server; the Copy Database Wizard will make sure you select only shared drives on a clustered destination server.

    [使用 Windows 驗證]Use Windows Authentication
    可讓使用者透過 MicrosoftMicrosoft Windows 使用者帳戶連接。Allow a user to connect through a MicrosoftMicrosoft Windows user account.

    [使用 SQL Server 驗證]Use SQL Server Authentication
    可讓使用者經由提供 [SQL Server]SQL Server 驗證使用者名稱和密碼來進行連接。Allow a user to connect by providing a [SQL Server]SQL Server Authentication user name and password.

    使用者名稱User name
    輸入要用來連接的使用者名稱。Enter the user name to connect with. 唯有在您選取了 [SQL Server]SQL Server 驗證之後,此選項才可以使用。This option is only available if you have selected [SQL Server]SQL Server Authentication.

    密碼Password
    輸入登入的密碼。Enter the password for the login. 唯有在您選取了 [SQL Server]SQL Server 驗證之後,此選項才可以使用。This option is only available if you have selected [SQL Server]SQL Server Authentication.

    下一個Next
    連接到伺服器,並驗證使用者。Connect to the server and validate the user. 此處理序會檢查使用者在選取的電腦上是否擁有以上所列的權限。This process checks whether the user has the permissions listed above on the selected computers.

  4. [選取傳送方法] 頁面,選取傳送方法。From the Select a Transfer Method page, select the transfer method.

    使用卸離和附加方法Use the detach and attach method
    從來源伺服器卸離資料庫,將資料庫檔案 (.mdf、.ndf 以及 .ldf) 複製到目的地伺服器,然後在目的地伺服器端附加該資料庫。Detach the database from the source server, copy the database files (.mdf, .ndf, and .ldf) to the destination server, and attach the database at the destination server. 此方法通常是比較快速的方法,因為它的主要工作是讀取來源磁碟和寫入目的地磁碟。This method is usually the faster method because the principal work is reading the source disk and writing the destination disk. 不需要 [SQL Server]SQL Server 邏輯在資料庫中建立物件,或建立資料儲存結構。No [SQL Server]SQL Server logic is required to create objects within the database, or create data storage structures. 不過,如果資料庫包含大量已配置但未使用的空間,此方法就會比較慢。This method can be slower, however, if the database contains a large amount of allocated but unused space. 例如,新的且實際上幾乎是空的資料庫,在建立時若配置 100 MB,即使資料只填滿 5 MB,也會複製全部 100 MB。For instance, a new and practically empty database that is created allocating 100 MB, copies the entire 100 MB, even if only 5 MB is full.

    注意

    此方法讓使用者在傳送期間無法使用資料庫。This method makes the database unavailable to users during the transfer.

    如果失敗,則重新附加來源資料庫If a failure occurs, reattach the source database
    複製資料庫時,一律會將原始資料庫檔案重新附加至來源伺服器。When a database is copied, the original database files are always reattached to the source server. 無法完成資料庫移動時,使用這個方塊即可將原始檔案重新附加至來源資料庫。Use this box to reattach original files to the source database if a database move cannot be completed.

    使用 SQL 管理物件方法Use the SQL Management Object method
    這個方法會讀取來源資料庫上的每個資料庫物件的定義,然後在目的地資料庫中建立每個物件。This method reads the definition of each database object on the source database and creates each object in the destination database. 接著它會從來源資料表傳送資料到目的地資料表,重新建立索引與中繼資料。Then it transfers the data from the source tables to the destination tables, recreating indexes and metadata.

    注意

    在傳送期間,資料庫使用者可以繼續存取資料庫。Database users can continue to access the database during the transfer.

  5. [選取資料庫] 頁面,選取您要從來源伺服器,移動或複製到目的地伺服器的資料庫。From the Select Database page, select the database or databases you want to move or copy from the source server to the destination server. 請參閱本主題中<開始之前>一節的< 限制事項 >。See Limitations and Restrictions in the 'Before You Begin' section of this topic.

    [移動]Move
    移動資料庫至目的地伺服器。Move the database to the destination server.

    [複製]Copy
    複製資料庫至目的地伺服器。Copy the database to the destination server.

    SourceSource
    顯示存在於來源伺服器上的資料庫。Displays the databases that exist on the source server.

    狀態Status
    如果可以移動資料庫,就會顯示 [確定]Displays OK if the database can be moved. 否則就會顯示無法移動資料庫的原因。Otherwise displays the reason why the database cannot be moved.

    [重新整理]Refresh
    重新整理資料庫清單。Refresh the list of databases.

    下一個Next
    開始驗證處理,然後移動到下一個畫面。Start the validation process, and then move to the next screen.

  6. [設定目的地資料庫] 頁面,適當地變更資料庫名稱,以及指定資料庫檔案的位置和名稱。From the Configure Destination Database page, change the database name if appropriate and specify the location and names of the database files. 每次移動或複製各個資料庫時,就會出現此頁面。This page appears once for each database being moved or copied.

  7. [選取資料庫物件] 頁面,選取要包含在移動或複製作業中的物件。From the Select Database Objects page, select the objects to include in the move or copy operation. 此頁面只能在來源和目的地是不同的伺服器時使用。This page is only available when the source and destination are different servers. 若要包含物件,請按一下 [可用的相關物件] 方塊中的物件名稱,然後按一下 [>>] 按鈕,將物件移至 [選取的相關物件] 方塊。To include an object, click the object name in the Available related objects box, and then click the >> button to move the object to the Selected related objects box. 若要排除物件,請按一下 [選取的相關物件] 方塊中的物件名稱,然後按一下 [<<] 按鈕,將物件移至 [可用的相關物件] 方塊。To exclude an object, click the object name in the Selected related objects box, and then click the << button to move the object to the Available related objects box. 根據預設,屬於所選取類型的所有物件都會傳送。By default all objects of each selected type are transferred. 若要選擇任何類型的個別物件,請按一下 [選取的相關物件] 方塊中之任何物件類型旁的省略符號按鈕。To choose individual objects of any type, click the ellipsis button next to any object type in the Selected related objects box. 這會開啟一個對話方塊,其中您可以選取個別物件。This opens a dialog box where you can select individual objects.

    登入 (在執行階段的所有登入)Logins (All logins at run time)
    在移動或複製作業中加入登入。Include logins in the move or copy operation. 依預設為已選取。Selected by default.

    master 資料庫裡的預存程序Stored procedures from master database
    master 資料庫裡的預存程序包含在移動或複製作業中。Include stored procedures from the master database in the move or copy operation.

    注意

    擴充預存程序及其相關聯的 DLL 不適合自動複製。Extended stored procedures and their associated DLLs are not eligible for automated copy.

    SQL Server Agent 作業SQL Server Agent jobs
    msdb 資料庫裡的作業包含在移動或複製作業中。Include jobs from the msdb database in the move or copy operation.

    使用者自訂錯誤訊息User-defined error messages
    將使用者自訂錯誤訊息包含在移動或複製作業中。Include user-defined error messages in the move or copy operation.

    端點Endpoints
    包含在來源資料庫中所定義的端點。Include endpoints defined in the source database.

    全文檢索目錄Full-text catalog
    包含來源資料庫中的全文檢索目錄。Include full-text catalogs from the source database.

    SSIS 封裝SSIS Package
    包含在來源資料庫中所定義的 SSISSSIS 封裝。Include SSISSSIS packages defined in the source database.

    說明Description
    對物件的描述。A description of the object.

  8. [來源資料庫檔案的位置] 頁面,指定包含來源伺服器資料庫檔案的檔案系統共用。From the Location of Source Database Files page, specify a file system share that contains the database files on the source server. 如果來源和目的地伺服器執行個體位於不同的電腦上,這就是必要項。This is required if the source and destination server instances are on different computers.

    [資料庫備份]Database
    顯示要移動的每個資料庫的名稱。Displays the name of each database being moved.

    資料夾位置Folder location
    指定檔案系統上之來源資料庫檔案的位置。Specify the location of the source database files on the file system.

    例如:C:\Program Files\Microsoft SQL Server\MSSQL110.MSSQLSERVER\MSSQL\DATAFor example: C:\Program Files\Microsoft SQL Server\MSSQL110.MSSQLSERVER\MSSQL\DATA

    來源伺服器上的檔案共用File share on source server
    將來源資料庫檔案的位置指定為檔案共用的路徑。Specify the location of the source database files as a path of a file share.

    例如:"\\server_name\C$\Program Files\Microsoft SQL Server\MSSQL110。MSSQLSERVER\MSSQL\DataFor example: "\\server_name\C$\Program Files\Microsoft SQL Server\MSSQL110.MSSQLSERVER\MSSQL\Data

  9. 複製資料庫精靈會建立 SSISSSIS 封裝來傳送資料庫。請從 [設定封裝] 頁面適當地自訂封裝。The Copy Database Wizard creates a SSISSSIS package to transfer the database From the Configure the Package page, customize the package if appropriate.

    封裝位置Package location
    顯示 SSISSSIS 封裝的寫入位置。Displays where the SSISSSIS package will be written.

    封裝名稱Package name
    輸入 SSISSSIS 封裝的名稱。Enter a name for the SSISSSIS package.

    記錄選項Logging options
    選取是要將記錄資訊儲存在 Windows 事件記錄檔中,還是儲存在文字檔中。Select whether to store the logging information in the Windows event log, or in a text file.

    錯誤記錄檔路徑Error log file path
    提供記錄檔位置的路徑。Provide a path for the location of the log file. 只有在已選取文字檔案登入選項時,才能使用此選項。This option is only available if the text file logging option is selected.

  10. [排程封裝] 頁面,指定您要讓移動或複製作業開始的時間。From the Schedule the Package page, specify when you want the move or copy operation to start. 如果您不是系統管理員,您必須指定可存取 [SQL Server]SQL Server (SSIS) 封裝執行子系統的 Integration ServicesIntegration Services Agent Proxy 帳戶。If you are not a system administrator, you must specify a [SQL Server]SQL Server Agent Proxy account that has access to the Integration ServicesIntegration Services (SSIS) Package execution subsystem.

    Run immediatelyRun immediately
    在您按 [下一步] 之後,開始移動或複製作業。Start the move or copy operation after you click Next.

    [排程]Schedule
    稍後開始移動或複製作業。Start the move or copy operation later. 目前的排程設定會出現在描述方塊中。The current schedule settings appear in the description box. 若要變更排程,請按一下 [變更]To change the schedule, click Change.

    變更Change
    開啟 [新增作業排程] 對話方塊。Open the New Job Schedule dialog box.

    Integration Services proxy 帳戶Integration Services proxy account
    選取可用的 Proxy 帳戶。Select an available proxy account. 若要排程傳送,至少必須有一個 Proxy 帳戶可供使用者使用,而且帳戶要設定為擁有 [SQL Server Integration Services 封裝執行] 子系統的權限。To schedule the transfer, there must be at least one proxy account available to the user, configured with permission to the SQL Server Integration Services package execution subsystem.

    若要建立 SSISSSIS 封裝執行的 Proxy 帳戶,請在 [物件總管] 中,展開 [SQL Server Agent]、展開 [Proxy]、以滑鼠右鍵按一下 [SSIS 封裝執行],然後按一下 [新增 Proxy]To create a proxy account for SSISSSIS package execution, in Object Explorer, expand SQL Server Agent, expand Proxies, right-click SSIS Package Execution, and then click New Proxy.

    [系統管理員 (sysadmin)] 固定伺服器角色成員的使用者,可以選取具有必要權限的 [SQL Server Agent 服務帳戶] 來執行這個作業步驟。Members of the sysadmin fixed server role can select the SQL Server Agent Service Account, which has the necessary permissions.

  11. [完成精靈] 頁面,檢閱所選選項的摘要。From the Complete the Wizard page, review the summary of the selected options. [上一步] 即可變更選項。Click Back to change an option. 按一下 [完成] 以建立資料庫。Click Finish to create the database. 在傳送期間, [正在執行作業] 頁面會監視有關 [複製資料庫精靈] 執行的狀態資訊。During the transfer, the Performing operation page monitors status information about the execution of the Copy Database Wizard.

    動作Action
    列出每個執行的動作。Lists each action being performed.

    狀態Status
    表示動作完全成功或失敗。Indicates whether the action as a whole succeeded or failed.

    MessageMessage
    提供每個步驟所傳回的任何訊息。Provides any messages returned from each step.

後續操作:升級 SQL Server 資料庫之後Follow Up: After Upgrading a SQL Server Database

在您使用複製資料庫精靈,將資料庫從舊版 [SQL Server]SQL Server 升級至 SQL Server 2014SQL Server 2014之後,資料庫就會變成立即可用並自動進行升級。After you use the Copy Database Wizard to upgrade a database from an earlier version of [SQL Server]SQL Server to SQL Server 2014SQL Server 2014, the database becomes available immediately and is automatically upgraded. 如果資料庫具有全文檢索索引,升級程序就會根據 [全文檢索目錄升級選項] 伺服器屬性的設定,匯入、重設或重建這些索引。If the database has full-text indexes, the upgrade process either imports, resets, or rebuilds them, depending on the setting of the Full-Text Upgrade Option server property. 如果升級選項設定為 [匯入][重建],則全文檢索索引在升級期間將無法使用。If the upgrade option is set to Import or Rebuild, the full-text indexes will be unavailable during the upgrade. 根據進行索引的資料數量而定,匯入可能需要數個小時,而重建可能需要十倍以上的時間。Depending the amount of data being indexed, importing can take several hours, and rebuilding can take up to ten times longer. 此外,請注意,當升級選項設定為 [匯入] 時,如果全文檢索目錄無法使用,系統就會重建相關聯的全文檢索索引。Note also that when the upgrade option is set 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.

如果使用者資料庫的相容性層級在升級前為 100 或更高層級,則在升級後仍會保持相同。If the compatibility level of a user database was 100 or higher before upgrade, it remains the same after upgrade. 如果已升級資料庫中的相容性層級為 90,則相容性層級會設定為 100 (這是 SQL Server 2014SQL Server 2014 所支援的最低相容性層級)。If the compatibility level was 90 in the upgraded database, the compatibility level is set to 100, which is the lowest supported compatibility level in SQL Server 2014SQL Server 2014. 如需詳細資訊,請參閱 ALTER DATABASE 相容性層級 (Transact-SQL)For more information, see ALTER DATABASE Compatibility Level (Transact-SQL).

另請參閱See Also

使用卸離與附加來升級資料庫 (Transact-SQL) Upgrade a Database Using Detach and Attach (Transact-SQL)
建立 SQL Server Agent ProxyCreate a SQL Server Agent Proxy