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

[複製資料庫精靈] 可讓您輕鬆地將資料庫和特定伺服器物件,從某個 SQL ServerSQL Server 執行個體移動或複製到另一個執行個體,而不需要讓伺服器停機。The Copy Database Wizard moves or copies databases and certain server objects easily from one instance of SQL ServerSQL Server to another instance, with no server downtime. 使用此精靈可以執行下列作業:By using this wizard, you can do the following:

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

  • 選取要移動或複製的資料庫。Select database(s) to move or copy.

  • 為資料庫指定檔案位置。Specify the file location for the database(s).

  • 將登入複製到目的地伺服器。Copy logins to the destination server.

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

  • 排程何時要移動或複製資料庫。Schedule when to move or copy the database(s).

限制事項 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 databases that:

    • 系統資料庫。Are System.

    • 標示為複寫的資料庫。Are marked for replication.

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

    • 在 Microsoft Azure 儲存體中儲存資料或記錄檔的資料庫。Have data or log files stored in Microsoft Azure storage.

  • 您無法將資料庫移動或複製到舊版 SQL Server。A database cannot be moved or copied to an earlier version of SQL Server.

  • 若您選取 [移動] 選項,則當移動資料庫之後,精靈會自動刪除來源資料庫。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. 此外,選取的伺服器物件會複製到目的地,而不是移至目的地;唯一實際移動的物件是資料庫。In addition, selected server objects are copied rather than moved to the destination; the database is the only object that is actually moved.

  • 如果您使用 SQL ServerSQL Server 管理物件方法來移動全文檢索目錄,您必須在移動之後重新擴展索引。If you use the SQL ServerSQL 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. 對於 SQL ServerSQL Server 管理物件方法而言,因為資料庫絕對不會離線,所以允許使用中工作階段。For the SQL ServerSQL Server Management Object method, active sessions are allowed because the database is never taken offline.

  • 傳送參考目的地伺服器上還不存在之資料庫的 SQL Server Agent 作業,將會導致整個作業失敗。Transferring SQL Server Agent jobs which reference databases that do not already exist on the destination server will cause the entire operation to fail. 此精靈會先嘗試建立 SQL Server Agent 作業,再建立資料庫。The Wizard attempts to create a SQL Server Agent job prior to creating the database. 因應措施:As a workaround:

    1. 在目的地伺服器上,建立與要複製或移動的資料庫名稱相同的 Shell 資料庫。Create a shell database on the destination server with the same name as the database to be copied or moved. 請參閱建立資料庫See Create a Database.

    2. 從 [設定目的地資料庫] 頁面中,選取 [卸除目的地伺服器上具有相同名稱的資料庫,然後繼續資料庫傳送,並覆寫現有的資料庫檔案]。From the Configure Destination Database page select Drop any database on the destination server with the same name, then continue with the database transfer, overwriting existing database files.

重要!!IMPORTANT!! 卸離與附加方法會使來源和目的地資料庫擁有權,成為設定為執行 [複製資料庫精靈] 的登入。The detach and attach method will cause the source and destination database ownership to become set to the login executing the Copy Database Wizard. 若要變更資料庫的擁有權,請參閱 ALTER AUTHORIZATION (Transact-SQL)See ALTER AUTHORIZATION (Transact-SQL) to change the ownership of a database.

必要條件 Prerequisites

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

  • 確定可從目的地伺服器連接到來源伺服器上的資料和記錄檔目錄。Ensure the data and log file directories on the source server can be reached from the destination server.

  • 根據 卸離與附加 方法,目的地伺服器上必須有 SSIS 子系統的 SQL Server Agent Proxy,以及可存取來源和目的地伺服器之檔案系統的認證。Under the detach and attach method, a SQL Server Agent Proxy for the SSIS subsystem must exist on the destination server with a credential that can access the file system of both the source and destination servers. 如需 Proxy 的詳細資訊,請參閱 建立 SQL Server Agent ProxyFor more information on proxies, see Create a SQL Server Agent Proxy.

重要!!IMPORTANT!! 根據 卸離與附加 方法,若未使用 Integration Services Proxy 帳戶,複製或移動程序將會失敗。Under the detach and attach method, the copy or move process will fail if an Integration Services Proxy account is not used. 在某些情況下,來源資料庫將無法重新附加至來源伺服器,並將從資料和記錄檔中移除所有 NTFS 安全性權限。Under certain situations the source database will not become re-attached to the source server and all NTFS security permissions will be stripped from the data and log files. 如果發生此情況,請巡覽至您的檔案、重新套用相關的權限,再將資料庫重新附加至您的 SQL Server 執行個體。If this happens, navigate to your files, re-apply the relevant permissions, and then re-attach the database to your instance of SQL Server.

建議 Recommendations

Permissions Permissions

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

[複製資料庫精靈] 頁面 The Copy Database wizard pages

從 SQL Server Management Studio 的物件總管啟動 [複製資料庫精靈],然後展開 [資料庫]。Launch the Copy Database Wizard in SQL Server Management Studio from Object Explorer and expand Databases. 以滑鼠右鍵按一下資料庫,指向 [工作],然後按一下 [複製資料庫]。Then right-click a database, point to Tasks, and then click Copy Database. 如果出現 [歡迎使用複製資料庫精靈] 開頭顯示頁面,請按一下 [下一步]。If the Welcome to the Copy Database Wizard splash page appears, click Next.

選取來源伺服器Select a source server

用來指定要移動或複製之資料庫所在的伺服器,以及輸入登入資訊。Used to 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
    用來識別您要移動或複製之資料庫所在的伺服器名稱。Used to identify the name of the server on which the database(s) you want to move or copy is located. 手動輸入,或按一下省略符號以巡覽至所需的伺服器。Manually enter, or click the ellipsis to navigate to the desired server. 該伺服器必須至少為 SQL Server 2005。The server must be at least SQL Server 2005.

  • 使用 Windows 驗證Use Windows Authentication
    可讓使用者透過 Microsoft Windows 使用者帳戶連接。Allows a user to connect through a Microsoft Windows user account.

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

    • 使用者名稱User name
      用來輸入要連接的使用者名稱。Used to enter the user name to connect with. 只有在您選擇使用 [SQL Server 驗證] 進行連接時,才能使用此選項。This option is only available if you have selected to connect using SQL Server Authentication.

    • 密碼Password
      用來輸入登入的密碼。Used to enter the password for the login. 只有在您選擇使用 [SQL Server 驗證] 進行連接時,才能使用此選項。This option is only available if you have selected to connect using SQL Server Authentication.

選取目的地伺服器Select a destination server

用來指定要移動或複製資料庫的目的地伺服器。Used to specify the server where the database will be moved or copied to. 如果您將來源和目的地伺服器設定成同一個伺服器執行個體,您就會建立一個資料庫複本。If you set the source and destination servers to the same server instance, you will make a copy of the 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
    用來識別您要移動或複製資料庫的目的地伺服器名稱。Used to identify the name of the server to which the database(s) you want to move or copy to is located. 手動輸入,或按一下省略符號以巡覽至所需的伺服器。Manually enter, or click the ellipsis to navigate to the desired server. 該伺服器必須至少為 SQL Server 2005。The server must be at least SQL Server 2005.

    注意: 您可以使用屬於叢集伺服器的目的地。[複製資料庫精靈] 會確保您只能選取叢集目的地伺服器上的共用磁碟機。NOTE 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
    可讓使用者透過 Microsoft Windows 使用者帳戶連接。Allows a user to connect through a Microsoft Windows user account.

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

    • 使用者名稱User name
      用來輸入要連接的使用者名稱。Used to enter the user name to connect with. 只有在您選擇使用 [SQL Server 驗證] 進行連接時,才能使用此選項。This option is only available if you have selected to connect using SQL Server Authentication.

    • 密碼Password
      用來輸入登入的密碼。Used to enter the password for the login. 只有在您選擇使用 [SQL Server 驗證] 進行連接時,才能使用此選項。This option is only available if you have selected to connect using SQL Server Authentication.

選取傳送方法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 ServerSQL Server 邏輯在資料庫中建立物件,或建立資料儲存結構。No SQL ServerSQL 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.

    注意: 此方法讓使用者在傳送期間無法使用資料庫。NOTE 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.

選取資料庫Select database

選取您要從來源伺服器移動或複製到目的地伺服器的資料庫。Select the database(s) you want to move or copy from the source server to the destination server. 請參閱主題上方的 限制事項See Limitations and Restrictions at the top of 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 various information of the source database.

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

設定目的地資料庫Configure destination database

適當地變更資料庫名稱,以及指定資料庫檔案的位置和名稱。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.

  • 來源資料庫Source Database
    來源資料庫的名稱。The name of the source database. 這是無法編輯的文字方塊。The text box is not editable.

  • 目的地資料庫Destination Database
    要建立的目的地資料庫名稱,請視需要進行修改。The name of the destination database to be created, modify as desired.

  • 目的地資料庫檔案:Destination database files:

    • 檔名Filename
      要建立的目的地資料庫檔案名稱,請視需要進行修改。The name of the destination database file to be created, modify as desired.

    • 大小 (MB)Size (MB)
      目的地資料庫檔案的大小 (以 MB 為單位)。Size of the destination database file in megabytes.

    • 目的資料夾Destination Folder
      要裝載目的地資料庫檔案之目的地伺服器上的資料夾,請視需要進行修改。The folder on the destination server to host the destination database file, modify as desired.

    • 狀態Status
      狀態Status

  • 如果目的地資料庫已存在:If the destination database already exists:
    決定目的地資料庫已存在時所要採取的動作。Decide what action to take if the destination database already exists.

    • 如果目的地已有相同名稱的資料庫或檔案,請停止傳送。Stop the transfer if a database or file with the same name exists at the destination.

    • 卸除目的地伺服器上具有相同名稱的資料庫,然後繼續資料庫傳送,並覆寫現有的資料庫檔案。Drop any database on the destination server with the same name, then continue with the database transfer, overwriting existing database files.

選取伺服器物件Select Server Objects

此頁面只能在來源和目的地是不同的伺服器時使用。This page is only available when the source and destination are different servers.

  • 可用的相關物件Available related objects
    列出可傳送至目的地伺服器的物件。Lists objects available to transfer to the destinations server. 若要包含物件,請按一下 [可用的相關物件] 方塊中的物件名稱,然後按一下 [>>] 按鈕,將物件移至 [選取的相關物件] 方塊。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.

  • 選取的相關物件Selected related objects
    列出將傳送至目的地伺服器的物件。Lists objects that will be transferred to the destinations server. 若要排除物件,請按一下 [選取的相關物件] 方塊中的物件名稱,然後按一下 [<<] 按鈕,將物件移至 [可用的相關物件] 方塊。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.

  • 伺服器物件清單List of Server Objects

    • 登入 (預設會選取)。Logins (Selected by default.)
    • SQL Server Agent 作業SQL Server Agent jobs

    • 使用者自訂錯誤訊息User-defined error messages

    • 端點Endpoints

    • 全文檢索目錄Full-text catalog

    • SSIS 封裝SSIS Package

    • master 資料庫裡的預存程序Stored procedures from master database

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

來源資料庫檔案的位置Location of source database files

此頁面只能在來源和目的地是不同的伺服器時使用。This page is only available when the source and destination are different servers. 指定包含來源伺服器上之資料庫檔案的檔案系統共用。Specify a file system share that contains the database files on the source server.

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

  • 資料夾位置Folder location
    指定來源伺服器上之資料庫檔案的資料夾位置。The folder location of the database files on the source server. 例如: 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
    包含來源伺服器上之資料庫檔案的檔案共用。The file share containing the database files on the source server. 手動輸入共用,或按一下省略符號以巡覽至共用。Manually enter the share, or click the ellipsis to navigate to the 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.

設定套件Configure the package

[複製資料庫精靈] 會建立 SSIS 封裝來傳送資料庫。The Copy Database Wizard creates an SSIS package to transfer the database.

  • 封裝位置Package location
    顯示 SSIS 套件的寫入位置。Displays to where the SSIS package will be written.

  • 封裝名稱Package name
    預設會建立 SSIS 封裝的名稱,請視需要進行修改。A default name for the SSIS package will be created, modify as desired.

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

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

排程套件Schedule the package

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

重要!!IMPORTANT!! Integration Services Proxy 帳戶必須依照 卸離與附加 方法使用。An Integration Services Proxy account must be used under the detach and attach method.

  • 立即執行Run immediately
    完成精靈後將會執行 SSIS 封裝。SSIS Package will execute after completing the wizard.

  • 排程Schedule
    根據排程來執行 SSIS 封裝。SSIS Package will execute according to a schedule.

    • 變更排程 Change Schedule
      開啟 [新增作業排程] 對話方塊。Opens the New Job Schedule dialog box. 請視需要進行設定。Configure as desired. 完成後按一下 [確定]。Click OK when finished.
  • Integration Services Proxy 帳戶:從下拉式清單中選取可用的 Proxy 帳戶。Integration Services Proxy account Select an available proxy account from the drop-down list. 若要排程傳送,至少必須有一個 Proxy 帳戶可供使用者使用,而且帳戶要設定為擁有 SSIS 封裝執行子系統的權限。To schedule the transfer, there must be at least one proxy account available to the user, configured with permission to the SSIS package execution subsystem.

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

完成精靈Complete the wizard

顯示所選選項的摘要。Displays summary of the selected options. [上一步] 即可變更選項。Click Back to change an option. 按一下 [完成] 以建立 SSIS 封裝。Click Finish to create the SSIS package. [正在執行作業] 頁面會監視有關 [複製資料庫精靈] 執行的狀態資訊。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.

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

範例 Examples

通用步驟Common Steps

不論您選擇的是 [移動] 或 [複製]、[卸離與附加] 或 [SMO],下列五個步驟都會相同。Regardless of whether you choose Move or Copy, Detach and Attach or SMO, the five steps listed below will be the same. 為求簡潔,這些步驟只會在此列出一次,所有範例將從步驟 6 開始進行。For brevity, the steps are listed here once and all examples will start on Step 6.

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

  2. 展開 [資料庫],以滑鼠右鍵按一下所需的資料庫,指向 [工作],然後按一下 [複製資料庫...]*Expand *Databases, right-click the desired database, point to Tasks, and then click Copy Database...

  3. 如果出現 [歡迎使用複製資料庫精靈] 開頭顯示頁面,請按一下 [下一步]。If the Welcome to the Copy Database Wizard splash page appears, click Next.

  4. [選取來源伺服器] 頁面:指定要移動或複製之資料庫所在的伺服器。Select a Source Server page: Specify the server with the database to move or copy. 選取驗證方法。Select the authentication method. 如果選擇 [使用 SQL Server 驗證],則必須輸入您的登入認證。If Use SQL Server Authentication is chosen you will need to enter your login credentials. 按一下 [下一步],建立與來源伺服器的連接。Click Next to establish the connection to the source server. 在整個工作階段中,此連接會保持開啟。This connection remains open throughout the session.

  5. [選取目的地伺服器] 頁面:指定將移動或複製資料庫的目的地伺服器。Select a Destination Server page: Specify the server where the database will be moved or copied to. 選取驗證方法。Select the authentication method. 如果選擇 [使用 SQL Server 驗證],則必須輸入您的登入認證。If Use SQL Server Authentication is chosen you will need to enter your login credentials. 按一下 [下一步],建立與來源伺服器的連接。Click Next to establish the connection to the source server. 在整個工作階段中,此連接會保持開啟。This connection remains open throughout the session.

    注意: 您可以從任何資料庫啟動 [複製資料庫精靈]。NOTE You can launch the Copy Database Wizard from any database. 您可以從來源或目的地伺服器使用 [複製資料庫精靈]。You can use the Copy Database Wizard from either the source or destination server.

A.使用卸離與附加方法,將資料庫移至不同實體伺服器上的執行個體。登入和 SQL Server Agent 作業也會一併移動。A. Move database using detach and attach method to an instance on a different physical server. A login and SQL Server Agent job will be moved as well.

下列範例會將 Sales 資料庫、名為 contoso\Jennie 的 Windows 登入和名為 Jennie’s Report 的 SQL Server Agent 作業,從 Server1 上的 SQL Server 2008 執行個體,移至 Server2上的 SQL Server 2016 執行個體。The following example will move the Sales database, a Windows login named contoso\Jennie and a SQL Server Agent job named Jennie’s Report from a 2008 instance of SQL Server on Server1 to a 2016 instance of SQL Server on Server2. Jennie’s Report 使用 Sales 資料庫。Jennie’s Report uses the Sales database. Sales 目前不在目的地伺服器 Server2上。Sales does not already exist on the destination server, Server2. Server1 將會在移動資料庫之後,重新指派給不同的小組。Server1 will be re-assigned to a different team after the database move.

  1. 如先前的 限制事項中所述,傳送參考尚不存在於目的地伺服器之資料庫的 SQL Server Agent 作業時,必須在目的地伺服器上建立 Shell 資料庫。As noted in Limitations and Restrictions, above, a shell database will need to be created on the destination server when transferring a SQL Server Agent job that references a database that does not already exist on the destination server. 請在目的地伺服器上,建立名為 Sales 的 Shell 資料庫。Create a shell database called Sales on the destination server.

  2. 回到精靈的 [選取傳送方法] 頁面︰檢閱並保留預設值。Back to the Wizard, Select the Transfer Method page: Review and maintain the default values. 按一下 [下一步]Click Next.

  3. [選取資料庫] 頁面︰針對所需的資料庫 Sales 選取 [移動] 核取方塊。Select Databases page: Select the Move checkbox for the desired database, Sales. 按一下 [下一步]Click Next.

  4. [設定目的地資料庫] 頁面︰此精靈指出 Sales 已存在於目的地伺服器上 (如上述步驟 6 所建立),並已在目的地資料庫名稱中附加 _newConfigure Destination Database page: The Wizard has identified that Sales already exists on the destination server, as created in Step 6 above, and has appended _new to the Destination database name. 請從 [目的地資料庫] 文字方塊中刪除 _newDelete _new from the Destination database text box. 如有需要,請變更 [檔案名稱] 和 [目的地資料夾]。If desired, change the Filename, and Destination Folder. 選取 [卸除目的地伺服器上具有相同名稱的資料庫,然後繼續資料庫傳送,並覆寫現有的資料庫檔案]。Select Drop any database on the destination server with the same name, then continue with the database transfer, overwriting existing database files. 按一下 [下一步]Click Next.

  5. [選取伺服器物件] 頁面︰在 [選取的相關物件:] 面板中,按一下[Object name Logins (物件名稱登入)] 的省略符號按鈕。Select Server Objects page: In the Selected related objects: panel, click the ellipsis button for Object name Logins. 在 [複製選項] 下,選取 [只複製選取的登入:]。Under Copy Options select Copy only the selected logins:. 核取 [顯示所有伺服器登入] 的方塊。Check the box for Show all server logins. 核取 contoso\Jennie 的 [登入] 方塊。Check the Login box for contoso\Jennie. 按一下 [確定]Click OK. 在 [可用的相關的物件:] 面板中,選取 [SQL Server Agent 作業] 然後按一下 [>] 按鈕。In the Available related objects: panel select SQL Server Agent jobs and then click the > button. 在 [選取的相關物件:] 面板中,按一下 [SQL Server Agent 作業] 的省略符號按鈕。In the Selected related objects: panel, click the ellipsis button for SQL Server Agent jobs. 在 [複製選項] 下,選取 [只複製選取的作業]。Under Copy Options select Copy only the selected jobs. 核取 [Jennie’s Report] 的方塊。Check the box for Jennie’s Report. 按一下 [確定]Click OK. 按一下 [下一步]Click Next.

  6. [來源資料庫檔案的位置] 頁面︰按一下 [來源伺服器上的檔案共用] 的省略符號按鈕,然後巡覽至指定的資料夾位置。Location of Source Database Files page: Click the ellipsis button for File share on source server and navigate to the location for the given Folder location. 例如,針對資料夾位置 D:\MSSQL13.MSSQLSERVER\MSSQL\DATA,使用 \\Server1\D$\MSSQL13.MSSQLSERVER\MSSQL\DATA 作為 [來源伺服器上的檔案共用]。For example, for Folder location D:\MSSQL13.MSSQLSERVER\MSSQL\DATA use \\Server1\D$\MSSQL13.MSSQLSERVER\MSSQL\DATA for File share on source server. 按一下 [下一步]Click Next.

  7. [設定封裝] 頁面︰在 [封裝名稱:] 文字方塊中,輸入 SalesFromServer1toServer2_MoveConfigure the Package page: In the Package name: text box enter SalesFromServer1toServer2_Move. 核取 [是否要儲存傳送記錄檔?] 方塊。Check the Save transfer logs? box. 在 [記錄選項] 下拉式清單中選取 [文字檔]。In the Logging options drop-down list select Text file. 記下 [錯誤記錄檔路徑];請視需要進行修改。Note the Error log file path; revise as desired. 按一下 [下一步]Click Next.

    注意:[錯誤記錄檔路徑] 是目的地伺服器上的路徑。NOTE The Error log file path is the path on the destination server.

  8. [排程封裝] 頁面︰從 [Integration Services Proxy 帳戶] 下拉式清單中選取相關的 Proxy。Schedule the Package page: Select the relevant proxy from the Integration Services Proxy account drop-down list. 按一下 [下一步]Click Next.

  9. [完成精靈] 頁面︰檢閱所選選項的摘要。Complete the Wizard page: Review the summary of the selected options. [上一步] 即可變更選項。Click Back to change an option. 按一下 [完成] 執行工作。Click Finish to execute the task. 在傳送期間,[正在執行作業] 頁面會監視有關此精靈執行的狀態資訊。During the transfer, the Performing operation page monitors status information about the execution of the Wizard.

  10. [正在執行作業] 頁面︰如果作業成功,請按一下 [關閉]。Performing Operation page: If operation is successful, click Close. 如果作業失敗,請檢閱錯誤記錄檔,也可選取 [上一步] 以進一步檢閱。If operation is unsuccessful, review error log, and possibly Back for further review. 否則,請按一下 [關閉]。Otherwise, click Close.

  11. 移動後步驟 :考慮在新的主機 Server2上執行下列 T-SQL 陳述式:Post Move Steps Consider executing the following T-SQL statements on the new host, Server2:

    ALTER AUTHORIZATION ON DATABASE::Sales TO sa;
    
    ALTER DATABASE Sales 
    SET COMPATIBILITY_LEVEL = 130;
    
    USE Sales
    GO
    
    EXEC sp_updatestats;
    
  12. 移動後步驟清除Post Move Steps Cleanup
    由於 Server1 會移至不同的小組,而且不會重複 移動 作業,因此請考慮執行下列步驟:Since Server1 will be moved to a different team and the Move operation will not be repeated, consider executing the following steps:

    • 刪除 SalesFromServer1toServer2_Move 上的 SSIS 封裝 Server2Deleting SSIS package SalesFromServer1toServer2_Move on Server2.
    • 刪除 SalesFromServer1toServer2_Move 上的 SQL Server Agent 作業 Server2Deleting SQL Server Agent job SalesFromServer1toServer2_Move on Server2.
    • 刪除 Jennie’s Report 上的 SQL Server Agent 作業 Server1Deleting SQL Server Agent job Jennie’s Report on Server1.
    • 卸除 contoso\Jennie 上的登入 Server1Dropping login contoso\Jennie on Server1.

B. 使用卸離與附加方法,將資料庫複製到相同的執行個體,並設定週期性排程。B. Copy database using detach and attach method to the same instance and set recurring schedule.

在此範例中, Sales 資料庫會複製並建立為相同執行個體上的 SalesCopyIn this example the Sales database will be copied and created as SalesCopy on the same instance. 之後,SalesCopy 會每週重新建立一次。Thereafter, SalesCopy, will be re-created on a weekly basis.

  1. [選取傳送方法] 頁面︰檢閱並保留預設值。Select a Transfer Method page: Review and maintain the default values. 按一下 [下一步]Click Next.

  2. [選取資料庫] 頁面︰針對 Sales 資料庫選取 [複製] 核取方塊。Select Databases page: Select the Copy checkbox for the Sales database. 按一下 [下一步]Click Next.

  3. [設定目的地資料庫] 頁面︰將目的地資料庫名稱變更為 SalesCopyConfigure Destination Database page: Change the Destination database name to SalesCopy. 如有需要,請變更 [檔案名稱] 和 [目的地資料夾]。If desired, change the Filename, and Destination Folder. 選取 [卸除目的地伺服器上具有相同名稱的資料庫,然後繼續資料庫傳送,並覆寫現有的資料庫檔案]。Select Drop any database on the destination server with the same name, then continue with the database transfer, overwriting existing database files. 按一下 [下一步]Click Next.

  4. [設定封裝] 頁面︰在 [封裝名稱:] 文字方塊中,輸入 SalesCopy Weekly RefreshConfigure the Package page: In the Package name: text box enter SalesCopy Weekly Refresh. 核取 [是否要儲存傳送記錄檔?] 方塊。Check the Save transfer logs? box. 按一下 [下一步]Click Next.

  5. [排程封裝] 頁面︰按一下 [排程:] 選項按鈕,然後按一下 [變更排程] 按鈕。Schedule the Package page: Click the Schedule: radio button and then click the Change Schedule button.

    1. [新增作業排程] 頁面︰在 [名稱] 文字方塊中,輸入 Weekly on SundayNew Job Schedule page: In the Name text box enter Weekly on Sunday.

    2. 按一下 [確定]Click OK.

  6. 從 [Integration Services Proxy 帳戶] 下拉式清單中選取相關的 Proxy。Select the relevant proxy from the Integration Services Proxy account drop-down list. 按一下 [下一步]Click Next.

  7. [完成精靈] 頁面︰檢閱所選選項的摘要。Complete the Wizard page: Review the summary of the selected options. [上一步] 即可變更選項。Click Back to change an option. 按一下 [完成] 執行工作。Click Finish to execute the task. 在封裝建立期間,[正在執行作業] 頁面會監視有關此精靈執行的狀態資訊。During the package creation, the Performing operation page monitors status information about the execution of the Wizard.

  8. [正在執行作業] 頁面︰如果作業成功,請按一下 [關閉]。Performing Operation page: If operation is successful, click Close. 如果作業失敗,請檢閱錯誤記錄檔,也可選取 [上一步] 以進一步檢閱。If operation is unsuccessful, review error log, and possibly Back for further review. 否則,請按一下 [關閉]。Otherwise, click Close.

  9. 手動啟動新建立的 SQL Server Agent 作業 SalesCopy weekly refreshManually start the newly created SQL Server Agent Job SalesCopy weekly refresh. 檢閱作業記錄,並確保執行個體上現在有 SalesCopyReview job history and ensure SalesCopy now exists on the instance.

待處理:升級資料庫之後 Follow up: After upgrading a database

在您使用複製資料庫精靈,將資料庫從舊版 SQL ServerSQL Server 升級至 SQL Server 2016SQL Server 2016之後,資料庫就會變成立即可用並自動進行升級。After you use the Copy Database Wizard to upgrade a database from an earlier version of SQL ServerSQL Server to SQL Server 2016SQL Server 2016, 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. 如需有關檢視或變更 全文檢索目錄升級選項 屬性設定的詳細資訊,請參閱< Manage and Monitor Full-Text Search for a Server Instance>。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 2016SQL Server 2016 所支援的最低相容性層級)。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 2016SQL Server 2016. 如需詳細資訊,請參閱 ALTER DATABASE 相容性層級 (Transact-SQL)For more information, see ALTER DATABASE Compatibility Level (Transact-SQL).

複製或移動後的考量 Post copy or move considerations

請考慮是否要在 複製移動後執行下列步驟:Consider whether to perform the following steps after a Copy or Move:

  • 使用卸離與附加方法時,變更資料庫的擁有權。Changing the ownership of the database(s) when the detach and attach method is used.
  • 移動後,卸除來源伺服器上的伺服器物件。Dropping server objects on the source server after a Move.
  • 卸除此精靈在目的地伺服器上建立的 SSIS 封裝。Dropping the SSIS package created by the Wizard on the destination server.
  • 卸除此精靈在目的地伺服器上建立的 SQL Server Agent 作業。Dropping the SQL Server Agent job created by the Wizard on the destination server.

詳細資訊!More information!

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