■ Backup and Restore database
■ Publish Database by using Generate and Publish Scripts Wizard
MORE INFO COPY DATABASES TO OTHER SERVERS
You can learn more about copying databases to other servers at http://msdn.microsoft .com/en-us/library/ms189624(SQL.110).aspx.
Copy Database Wizard
The Copy Database Wizard simplifies the process of copying databases from previous ver-sions of SQL Server to SQL Server 2012 Database Engine instances. When you use the Copy Database Wizard, the database becomes available immediately on the target instance after the transfer completes and is automatically upgraded. The default configuration of SQL Server 2012 has full-text indexes imported, and these indexes will be unavailable during the upgrade process. Importing text indexes can take a substantial amount of time, but rebuilding full-text indexes usually takes even longer.
The Copy Database Wizard enables you to:
■ Choose the source and destination instance. The destination instance must be running SQL Server 2012.
■ Copy databases and move them. Choosing the Move option deletes the database on the source instance after a successful transfer.
TermsKey
TermsKey
Lesson 1: Migrating to SQL Server 2012 CHAPTER 4 165
■ Choose database file locations on the destination instance.
■ Migrate SQL logins to the destination instance, as shown in Figure 4-6.
■ Migrate jobs, user-defined stored procedures, and custom error messages.
■ Schedule what time the copy or move occurs.
FIGURE 4-6 Migrate objects, including logins
You can’t use the Copy Database Wizard to copy the system databases across to a new instance. You also cannot use the Copy Database Wizard to copy databases that are being replicated or databases that are marked as Inaccessible, Loading, Offline, Recovering, Suspect, or in Emergency mode. When using the Copy Database Wizard, the user performing the copy or move must be a member of the sysadmin fixed server role on both the source and desti-nation instances. To use the Copy Database Wizard successfully, SQL Server Agent must be started on the destination instance.
When using the Copy Database Wizard, you must specify a transfer method, as shown in Figure 4-7. The difference between these methods is as follows:
■ Detach and Attach method This method is faster, but the database on the source instance is taken offline. This method is suitable for moving large databases between instances. The database is reattached to the source instance when performing a copy operation.
■ SQL Management Object method This method is slower but enables the database on the source instance to remain online.
FIGURE 4-7 Database copy transfer method
To copy a database by using the Copy Database Wizard, perform the following steps:
1. Use SQL Server Management Studio to connect to the source and destination instances with a user account that is a member of the sysadmin fixed server role.
2. Ensure that the SQL Server Agent is started on the destination instance.
3. On the source instance, right-click the database you want to copy, click Tasks, and then click Copy Database. This starts the Copy Database Wizard. Click Next.
4. On the Source Server page, specify the source instance and click Next.
5. On the Select A Destination Server page, specify the destination instance, as shown in Figure 4-8.
FIGURE 4-8 Specify destination server
Lesson 1: Migrating to SQL Server 2012 CHAPTER 4 167 6. Choose whether to use the detach and attach method or the SQL Management Object
method.
7. On the Select Databases page, choose which databases you want to migrate from the source instance to the destination instance. You also choose whether to perform a move or a copy operation on this page.
8. On the Configure Destination Database page, shown in Figure 4-9, specify the name of the database on the destination instance. You can also specify the destination file loca-tion of the database and transacloca-tion log files.
FIGURE 4-9 Destination database properties
9. On the Select Server Objects page, you can choose which objects to migrate, including logins used by security principals related to the databases that you want to migrate.
10. If you are using the detach and attach method, on the Location Of Source Database Files page, specify a file share that is configured for the location of the source database files.
11. Choose a name for the Integration Services package that will be created to assist with the migration.
12. On the Schedule page, choose whether to perform the transfer immediately or at a later point in time. Here you must also select an Integration Services Proxy account that has access to the file system on both the source and destination instances.
You can create an Integration Services Proxy account by first creating a credential under the Security node mapped to a user that has the appropriate permissions on the destination instance. The second step requires that you add an SSIS Package Execution Proxy mapped to the newly created credential.
MORE INFO COPY DATABASE WIZARD
You can learn more about the Copy Database Wizard at http://msdn.microsoft.com/en-us /library/ms188664(SQL.110).aspx.
NOTE FIREWALL CONFIGURATION ON DESTINATION INSTANCE
If you are having problems performing a database copy by using the wizard, set the fire-wall on the destination instance to allow incoming traffic on UDP ports 137, 138 (for the object method), and 1434 (for the detach and attach method).