This page of the SQL Server Upgrade Assistant UI is used to restore the user databases to an instance of SQL Server 2005/2008. The Upgrade Assistant Setup Test Server workflow is designed to setup the test server that has SQL Server 2005/2008 installed. Once setup is complete, you will then upgrade the test computer to SQL Server Code Name “Denali” and replay a trace against these databases at a later step.

If you are using multiple computers for the test, run this step on the test server. For more information, see Create a Multiple-Computer Test Environment.

Server name

Specify the name of the SQL Server 2005/2008 server or instance for the test computer where the databases will be restored. By default this field contains the name of the local computer. For a named instance, specify servername\instancename.

Authentication

Specify the type of connection to use to restore the databases. Windows Authentication is specified by default. If you specify SQL Server Authentication, enter the User Name and Password for a SQL Server account that is a member of the sysadmin fixed server role.

Playback source directory

Specify the full path of the folder containing the database backups.

Note: If you used Upgrade Assistant to capture a playback, the backup files are in the folder you specified in Playback capture location. If you created the backups manually, enter the path to the local folder or network share where the backup files are. Click Browse to locate the directory, or type the full path.

Restore databases directory

Specify the full path of the folder where the user databases should be restored. The default directory is the folder where you installed Upgrade Assistant. The instance of SQL Server 2005/2008 must have appropriate permissions for this directory.   The easiest way to do this is to use the default backup directory that SQL Server setup creates (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup for example)

Click Browse to locate the directory, or type the full path.

Restore System Databases

Selecting this checkbox indicates that the system databases – master, model, msdb – should be restored from backup.  This should be selected unless the current copies of these databases match the playback source.

Regenerate Service Master Key

The Service Master Key is used to encrypt other keys stored in your databases.  You may want to regenerate this key so that the test system uses a different key than the system where you ran the capture.

Set Up Playback

Restore the user databases. This button is enabled when Server name, Authentication, Playback source directory, and Restore databases directory are specified.

Results

Displays results as the user databases are restored.

 In this step you will upgrade your database to SQL Server Code Name “Denali” from the original 2005/2008 instance. This is an in-place upgrade exercise that will involve installing SQL Server Code Name “Denali”.

1. Navigate to where your SQL Server Code Name “Denali” installation source is located and launch setup.
2. Accept the license terms and click “Next”
3. In the “Installation Prerequisites” screen, click “Install”. This will install the necessary setup application and files required for the SQL Server Code Name “Denali” Installation Center.
4. When the prerequisites installation completes successfully, you will be presented with the SQL Server Installation Center console. Click on the “Upgrade from SQL Server 2005 or SQL Server 2008” option and make sure the next screen does not report any errors.
5. Make sure the “Upgrade Selected Instance and Shared Components” radio button is checked then select the SS2008 or SS2005 instance (depending on your source DB version) from the drop-down listbox.
6. Accept all the default options and complete the upgrade process.
7. When this step is complete, you have just performed an in-place upgrade of your SQL Server 2005/2008 database to SQL Server Code Name “Denali”.
8. At this point you are running new server binaries, but in order to preserve backward compatibility, SQL Server will not upgrade the database compatibility level of your user databases.  You need to manually update the database compatibility level to the value corresponding with SQL Server Code Name “Denali” (110) using the sp_dbcmptlevel system stored procedure.  You should do this for all of your user databases prior to proceeding with testing.