«

»

Aug 20

SQL Server 2012: How to copy a database from one server to another using the Copy Database Wizard

SQL Server 2012: How to copy a database from one server to another using the Copy Database Wizard

I was provisioning my SQL box when I found I didn´t need an already provisioned Windows Server box I had used for Exchange 2013. Lazy enough I installed SQL Server 2012 on my already installed Windows box. Later on I learned that Exchange leaves things behind when you uninstall it and I figured it would be better to start with a fresh Windows Installation for SQL. Long story short, that is when I needed a method to transfer all my databases to my new server without too much struggle. I used to backup my databases and then restore them on the destination server… but things like server logins usually result in some manual work and if you are not careful something might not work right off the bat. Clearly I wanted a better method to transfer my databases to another server: The Copy Database Wizard!

How to Use the Copy Database Wizard

The Copy Database Wizard allows you to move or copy databases between servers. This is also used to upgrade databases from a previous version Server to SQL 2012. Remember that using this method you can copy associated metadata, for example, logins and objects from the master database that are required by a copied database.
Here are some things to keep in mind:

 

  • I recommend using the Copy options rather than Move. After you are done copying you can take the database offline if you want. Just keep in mind moving does delete the database from the source server after completion.

 

Here are the steps to follow:

  1. Open SQL Server Management Studio
  2. Go to Object Explorer
  3. Expand Databases
  4. right-click a database you want to copy or move
  5. under Tasks click Copy Database.
  6. 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. The server must be at least SQL Server 2005.
    • Use Windows Authentication                                           Allow a user to connect through a Microsoft Windows user account.
    • Use SQL Server Authentication                                           Allow a user to connect by providing a SQL Server Authentication user name and password.
    • User name                                           Enter the user name to connect with. This option is only available if you have selected to connect using SQL Server Authentication .
    • Password                                           Enter the password for the login. This option is only available if you have selected to connect using SQL Server Authentication.
    • Next                                           Connect to the server and validate the user. This process checks whether the user is a member of the sysadmin fixed server role on the selected computer.
    • 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.

      Note                               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.
      Use Windows Authentication                                           
      Allow a user to connect through a Microsoft Windows user account.
      Use SQL Server Authentication                                           
      Allow a user to connect by providing a SQL Server Authentication user name and password.
      User name                                           
      Enter the user name to connect with. This option is only available if you have selected SQL Server Authentication.
      Password                                           
      Enter the password for the login. This option is only available if you have selected 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.
    • From the Select a Transfer Method page, select the transfer method.
      Use the detach and attach method                                           
      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. No 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. 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                               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.
      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.

      Note                               Note
      Database users can continue to access the database during the transfer.
    • 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.
      Source                                           
      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.
    • 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.
    • 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.
      Stored procedures from master database                                           
      Include stored procedures from the master database in the move or copy operation.

      Note                               Note
      Extended stored procedures and their associated DLLs are not eligible for automated copy.
      SQL Server Agent jobs                                           
      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 Package                                           
      Include SSIS packages defined in the source database.
      Description                                           
      A description of the object.
    • 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.For example: C:Program FilesMicrosoft SQL ServerMSSQL110.MSSQLSERVERMSSQLDATA
      File share on source server                                           
      Specify the location of the source database files as a path of a file share.For example: “server_nameC$Program FilesMicrosoft SQL ServerMSSQL110.MSSQLSERVERMSSQLData
    • The Copy Database Wizard creates a SSIS package to transfer the database From the Configure the Package page, customize the package if appropriate.
      Package location                                           
      Displays where the SSIS package will be written.
      Package name                                           
      Enter a name for the SSIS package.
      Logging options                                           
      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.
    • From the Schedule the Package page, specify when you want the move or copy operation to start. 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.
      Run 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 account                                           
      Select an available proxy account. 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.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.Members of the sysadmin fixed server role can select the SQL Server Agent Service Account, which has the necessary permissions.
    • 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.
Message                                           
Provides any messages returned from each step.

 

Follow Up: After Upgrading a SQL Server Database


After you use the Copy Database Wizard to upgrade a database from an earlier version of SQL Server to SQL Server 2012, 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.If the compatibility level of a user database was 90 or 100 before upgrade, it remains the same after upgrade. If the compatibility level was 80 or less before upgrade, in the upgraded database, the compatibility level is set to 90, which is the lowest supported compatibility level in SQL Server 2012. For more information, see ALTER DATABASE Compatibility Level (Transact-SQL).
Enhanced by Zemanta

Leave a Reply

%d bloggers like this: