INF: Frequently Asked Questions - SQL Server 2000 - Upgrade (261334)

MORE INFORMATION

1. Q. Can you detach a SQL Server 7.0 database and attach it to a SQL Server 2000 server?
   
   A. Yes. SQL Server 7.0 databases are compatible with SQL Server 2000. However, exceptions do exist. For a list of these exceptions, see the SQL Server 2000 Books Online topic "Database Upgrade from SQL Server 7.0". Note that attaching a SQL Server 7.0 database to SQL Server 2000 automatically upgrades the SQL Server 7.0 database to a SQL Server 2000 database and the database is then no longer usable by the SQL Server 7.0 installation.
2. Q. Can you detach a SQL Server 2000 database and attach it to a SQL Server 7.0 server?
   
   A. No. The only way to move a SQL Server 2000 database to a SQL Server 7.0 server is by transferring the data using a method such as Data Transformation Services (DTS), bcp, or use of a query between linked servers.
3. Q. Can you restore a SQL Server 7.0 database backup to a SQL Server 2000 server?
   
   A. Yes. Other than the master, model, msdb, and distribution databases, SQL Server 7.0 databases are compatible with SQL Server 2000.
4. Q. Can you restore a SQL Server 2000 database backup to a SQL Server 7.0 server?
   
   A. No. The only way to move a SQL Server 2000 database to a SQL Server 7.0 server is by transferring the data using a method such as DTS, bcp, or use of a query between linked servers.
5. Q. Can you restore or attach a SQL Server 6.5 database to SQL Server 2000?
   
   A. No. The only way to move a SQL Server 6.5 database to SQL Server 2000 is to run the Upgrade Wizard.
6. Q. Is a SQL Server 7.0 service pack required in order to upgrade?
   
   A. No service packs are required in order to perform the upgrade from SQL Server 7.0 to SQL Server 2000, version 8.0, although Microsoft Product Support Services recommends that you be on the latest service pack.
7. Q. Which SQL Server 6.5 service pack is required to upgrade to SQL Server 2000, version 8.0?
   
   A. When you upgrade SQL Server 6.5 to an instance of SQL Server 2000 on the same computer, you must have first apply the SQL Server version 6.5 Service Pack 5a or later. When you upgrade SQL Server 6.5 to an instance of SQL Server 2000 on a different computer, you must first apply the SQL Server version 6.5 Service Pack 3 or later.
8. Q. How long will it take to upgrade my SQL Server 6.5 databases?
   
   A. Many factors affect the amount of time needed to upgrade SQL Server 6.5 databases to SQL Server 2000, version 8.0. Each object in the SQL Server 6.5 database must be rebuilt in the SQL Server 2000 database, and every row must be transferred. Depending on the complexity of each database, the length of time needed to convert two 10 GB databases that have differing numbers of rows and objects varies widely. Also, the hardware platform, number of processors, disk subsystem, and amount of RAM plays a significant part in the amount of time required for the upgrade. Selecting "data validation" during the setup increases the amount of time needed to perform the upgrade by a factor of two. Some typical times for the upgrade process are:
   
   

   Size of Database	Estimated Time Required to Upgrade
   400 MB	Less than 20 minutes.
   1 GB	Less than  1 hour.
   10 GB	Less than  4 hours.
   50 GB	Less than 12 hours.
   100 GB	Less than 24 hours.

9. Q. Can users be connected to the SQL Server 7.0 server while the installation upgrade process runs?
   
   A. No. When you perform an installation upgrade, the SQL Server 7.0 server is stopped and started, hence users are not able to stay connected. In order to perform an upgrade while users stay connected, you must install a separate instance of SQL Server 2000 and then use the Copy Database Wizard to copy each database from Microsoft SQL Server 7.0 to that instance of Microsoft SQL Server 2000. The Copy Database Wizard allows you to upgrade your SQL Server 7.0 databases without having to shut down any servers in the process.
10. Q. Can users be connected to the SQL Server 6.5 server while the upgrade process runs?
    
    A. No. During the upgrade process, the SQL Server 6.5 server is stopped and started while objects are scripted and data is extracted. Also, when the data transfer starts, only SQL Server 2000 is running, and it is not possible to access SQL Server 6.5.
11. Q. How should I configure my SQL Server 6.5 server prior to performing the upgrade?
    
    A. If you will be upgrading your existing SQL Server 6.5 server to a new computer that is running SQL Server 2000, both computers should be configured to use a domain user name and password for the MSSQLServer service. The domain user account should also belong to the Administrators group on both computers. A local system account is sufficient for a one-computer upgrade. If you are upgrading across different domains, you must have a trust relationship set up between the domains before you start the upgrade.
12. Q. Can I consolidate databases from two or more 6.5 SQL Server servers onto one SQL Server 2000 server?
    
    A. No. The upgrade process keeps track of the server being upgraded and only allows databases from one 6.5 SQL Server server to be upgraded. Consolidating databases from different servers may cause problems in user login IDs, user accounts, and object permissions. If you want to consolidate several databases from different 6.5 SQL Server servers, move all the databases that you want to consolidate to a single SQL Server server and verify that your applications work correctly before upgrading to SQL Server 2000.
13. Q. Can I upgrade only one or a few of my databases to SQL Server 2000?
    
    A. An upgrade of an existing SQL Server 7.0 instance to SQL Server 2000 always upgrades all databases because it replaces SQL Server 7.0 with SQL Server 2000. If you want to upgrade only some of your SQL Server 7.0 databases, you should install SQL Server 2000 as a separate instance and use the Copy Database Wizard to upgrade the databases. For more information, see the SQL Server Books Online topic "Upgrading Databases from SQL Server 7.0 (Copy Database Wizard)".
    
    When you upgrade SQL Server 6.5, you can upgrade one, some, or all of your SQL Server databases to SQL Server 2000. You can even convert individual databases as a test or as practice before you upgrade all of the databases on the server. However, note that Microsoft recommends that you convert all production databases on a server at the same time, which will minimize potential problems. If you only want to convert a subset of your existing SQL Server databases, you should convert them all at the same time.
    
    If you will not be upgrading all of the SQL Server 6.5 databases at the same time, there are some issues of which you need to be aware. Any objects that rely on the contents of other databases, including views, stored procedures, and triggers, fail to be created if the object or the dependent database does not exist.
    
    If the SQL Server 6.5 model database has been modified to include additional objects, it should be converted either at the same time as all of the other SQL Server 6.5 databases, or last. Any objects that were created in SQL Server 6.5 databases as a result of non-default objects being added into the SQL Server 6.5 model database will be scripted during the upgrade process.
    
    When other SQL Server 6.5 databases are upgraded after the model database has already been converted, they will contain non-default objects based on the SQL Server 6.5 model database. Because the objects will be added to the new SQL Server 2000 databases when they are initially created by the SQL Server 2000 model database, the creation script fails to create any objects that already exist in the database. Therefore, by converting the model database last, any changes in the database structure will only be applied to new SQL Server 2000 databases. All of the non-default objects in the SQL Server 6.5 converted databases will have been created by scripts during the conversion process of those databases.
14. Q: Can I run SQL Server 2000 at the same time as SQL Server 7.0 or 6.5 on the same computer?
    
    A: SQL Server 6.5 and SQL Server 7.0 install as the default instance on a server, and you can run only one of these two versions on a particular computer at a time. However, SQL Server 2000 does support multiple instances of the SQL Server database engine running concurrently on the same computer. If you install SQL Server 2000 as a named instance you can run it alongside the SQL Server 6.5 or SQL Server 7.0 default instance that was previously installed on the computer. If you install SQL Server 2000 as the default instance it upgrades the SQL Server 6.5 or 7.0 default instance that is already present on the computer. When this occurs on a computer that was running SQL Server 6.5, you can use the vswitch utility to switch between the SQL Server 2000 default instance and the SQL Server 6.5 default instance following the upgrade. In the case where the SQL Server 7.0 default instance is upgraded, only the SQL Server 2000 default instance is accessible following the upgrade.
    
    Note that it is important to realize that each instance of the SQL Server database engine has its own set of system and user databases that are not shared between instances.
    
    For more information, see the SQL Server Books Online topic "Working with Instances and Versions of SQL Server".
15. Q. Why am I getting the following error during a SQL Server 6.5 conversion?
    
    
    @@servername not valid
    A. This error message may occur if the SQL Server 6.5 you are upgrading has not been named. To resolve these problems, perform the following steps on the 6.5 SQL Server server:
    
    
    1. In ISQL or ISQL/w, run the following query to make sure that the server has a name:

       SELECT @@servername
       							

    2. If the server does not have a name, run the following stored procedure to add a name:

          sp_addserver <server_name>, local
       							

16. Q. When you upgrade a SQL Server 6.5 server, what causes the following error messages?
    Cannot open default database
    Error querying @@servername
    A. If the default database for the system administrator (SA) has not recovered yet, or if it is marked as suspect, the upgrade wizard produces either of these error messages. Resolve the problem with the default database and run the upgrade wizard again.
17. Q. When you upgrade a SQL Server 6.5 server, the upgrade wizard seems to stop responding and fails. Why?
    
    A. If applications or services have open ODBC connections to the SQL Server 6.5 server during the conversion process, they may not allow the SQL Server server to shut down completely. The conversion process will not proceed on to the next step if it does not receive verification that the SQL Server 6.5 server has been completely stopped. The conversion process appears to stop responding and eventually fails in this situation. To resolve this situation, close all applications and services that may have ODBC connections or that may be using SQL Server before you perform the upgrade. If SQL Trace is connected to the version 6.5 SQL Server server, you will see a similar problem. Although the server will not actually stop responding, tasks that once happened quickly use so much CPU time that they become exponentially slower.
18. Q. Where can I look to see a record of any errors that I may have encountered during the upgrade process?
    
    A. During the upgrade process, detailed logs are generated and stored in your SQL Server directory. If any errors occur during the upgrade process, you will see a dialog box at the end of the process. This dialog box will display the contents of the error files. This output file is located in the Program Files\Microsoft SQL Server\MSSQL\Upgrade\<servername>_<date>_<time> directory. Each database will have its own subdirectory with output and error files that were generated during the upgrade process.