In this practice, you use the backup and restore and detach and attach methods of migrating databases.
EXERCISE 1 Copy a Database by Using Backup and Restore and Copy Database Wizard
In this exercise, you use the backup and restore method to copy the AdventureWorks2012 database from the default instance on SQL-A to the Alternate instance of SQL-A. To complete this exercise, perform the following steps:
1. Log on to the domain controller with the Kim_Akers user account and edit the SQL-POLICY Group Policy Object (GPO).
2. On the Inbound Rules node of the Windows Firewall With Advanced Security node, create a new Inbound rule allowing traffic on UDP ports 137, 138, and 1434 for all network profiles.
3. Log on to server SQL-A with the Kim_Akers user account.
4. Create a database named Neptune on the default SQL-A instance.
5. Use the backup and restore method to transfer the database to the SQL-A\ALTERNATE instance.
6. Create a credential mapped to the contoso\kim_akers account on the SQL-A\Alternate instance. Create a new SSIS Package Execution Proxy mapped to the new contoso
\kim_akers credential.
7. Copy the AdventureWorks2012 database from the Default instance of SQL-A to the SQL-A\ALTERNATE instance by using the Copy Database Wizard.
EXERCISE 2 Migrate a Database by Using Detach and Attach
In this exercise, you migrate a database from one instance to another instance on the same server by using the Detach and Attach method. To complete this exercise, perform the follow-ing steps:
1. Log on to server SQL-A with the Kim_Akers user account.
2. On SQL-A, create the C:\SpaceElevator directory.
3. In SQL Server Management Studio, connect to the default instance on SQL-A and create a database named SpaceElevator. Store the database file and the transaction log file in the C:\SpaceElevator directory.
4. Using Transact-SQL, detach the SpaceElevator database from the SQL-A instance.
5. In SQL Server Management Studio, connect to the SQL-A\Alternate instance.
6. Use SQL Server Management Studio to attach the SpaceElevator database to the SQL-A\Alternate instance.
EXERCISE 3 Migrate Logins
In this exercise, you create and migrate a Windows-authenticated SQL login and a SQL-authenticated SQL login from one Database Engine instance to another. To complete this exercise, perform the following steps:
1. On the domain controller, create a new user account named Cassie_Hicks. Set the password to Pa$$w0rd and configure it so that it neither expires nor must be changed at the next login.
2. On the default instance of SQL-A, execute the following Transact-SQL statements to create the two SQL logins that you will migrate.
CREATE LOGIN "CONTOSO\Cassie_Hicks" FROM WINDOWS;
CREATE LOGIN Ben_Andrews WITH PASSWORD = 'Pa$$w0rd';
Lesson 1: Migrating to SQL Server 2012 CHAPTER 4 173 3. Use the Script Login As functionality in SQL Server Management Studio to script both
logins to a new query window.
4. Execute the script generated in step 3 on the SQL-A\ALTERNATE instance to migrate the two logins to the ALTERNATE instance.
NOTE PASSWORD COMPLEXITY
You might need to alter the random password generated by the script for the Ben_ Andrews login to ensure that it meets password complexity requirements.
Lesson Summary
■ You can upgrade from SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2 if the appropriate service packs have been applied, you are upgrading within the same processor architecture, and you are attempting a supported edition upgrade path.
■ You can migrate a database from one instance to another instance by using the detach and attach method. You can’t detach a database that is being mirrored or replicated or that has a snapshot.
■ You can copy a database to another instance by using the Copy Database Wizard or by backing up and then restoring the database. The advantage of using the Copy Database Wizard is that it also enables you to migrate database metadata, such as logins, to the new instance.
■ You can migrate SQL logins by using the Generate A Script function in SQL Server Management Studio.
Lesson Review
Answer the following questions to test your knowledge of the information in this lesson. You can find the answers to these questions and explanations of why each answer choice is correct or incorrect in the “Answers” section at the end of this chapter.
1. Which of the following can you upgrade to SQL Server 2012 Standard edition without having to apply additional service packs? (Each correct answer presents a complete solution. Choose all that apply.)
A. SQL Server 2005 SP4 Standard edition installed on Windows Server 2008 with Service Pack 2
B. SQL Server 2008 SP2 Standard edition installed on Windows Server 2003 R2 C. SQL Server 2008 SP2 Enterprise edition installed on Windows Server 2008 R2 SP1 D. SQL Server 2008 R2 SP1 Standard edition installed on Windows Server 2008 R2
SP1
2. To which of the following editions and versions of SQL Server 2012 can you upgrade SQL Server 2008 R2 Datacenter edition (x64)? (Each correct answer presents a com-plete solution. Choose all that apply.)
A. SQL Server 2012 Enterprise edition (x86) B. SQL Server 2012 Enterprise edition (x64)
C. SQL Server 2012 Business Intelligence edition (x64) D. SQL Server 2012 Standard edition (x64)
3. Which of the following tools can you use to migrate Windows-authenticated SQL log-ins from an x86 log-instance of SQL Server 2012 to an x64 log-instance of SQL Server 2012 if contained databases are not in use? (Each correct answer presents a complete solution.
Choose all that apply.) A. Copy Database Wizard B. Import and Export Wizard C. Backup and Restore Database D. Generate A Script
4. You want to migrate a database, including logins and user-defined error messages, from one SQL Server 2012 instance to another. Which of the following tools can you use to accomplish this goal?
A. Copy Database Wizard
B. BACKUP DATABASE Transact-SQL statement C. Import and Export Wizard
D. sp_detach_db stored procedure
5. You want to use the detach and attach method of migrating a database to another SQL Server 2012 Database Engine instance. The database is currently published and replicated. Which of the following steps must you take prior to detaching the database from the source instance? (Each correct answer presents part of a complete solution.
Choose all that apply.) A. Drop database snapshots.
B. Unpublish the database.
C. Create a format file.
D. Create a database snapshot.