Troubleshooting
Upgrading to SQL Server 2000 FAQ
Can you detach a SQL Server 7.0 database and attach it to a SQL Server 2000 server?
Answer:
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 no longer usable by the SQL Server 7.0 installation.
Can you detach a SQL Server 2000 database and attach it to a SQL Server 7.0 server?
Answer:
No. The only way to move a SQL Server 2000 database to a SQL Server 7.0 server is by using Data Transformation Services (DTS).
Can you restore a SQL Server 7.0 database backup to a SQL Server 2000 server?
Answer:
Yes. Other than the master, model, msdb, and distribution databases, SQL Server 7.0 databases are compatible with SQL Server 2000.
Can you restore a SQL Server 2000 database backup to a SQL Server 7.0 server?
Answer:
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.
Can you restore or attach a SQL Server 6.5 database to SQL Server 2000?
Answer:
No. The only way to move a SQL Server 6.5 database to SQL Server 2000 is to run the SQL Server Upgrade Wizard.
Is a SQL Server 7.0 service pack required to upgrade?
Answer:
No service packs are required to perform the upgrade from SQL Server 7.0 to SQL Server 2000, although Microsoft Product Support Services (PSS) recommends that you be on the latest service pack.
Which SQL Server 6.5 service pack is required to upgrade to SQL Server 2000?
Answer:
When you upgrade SQL Server 6.5 to an instance of SQL Server 2000 on the same computer, you must 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.
How long will the upgrade process take to upgrade my SQL Server 6.5 databases?
Answer:
Many factors affect the amount of time needed to upgrade SQL Server 6.5 databases to SQL Server 2000. 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. In addition, the hardware platform, number of processors, disk subsystem, and amount of RAM play 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. This table shows some typical times for the upgrade process.
| 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 |
Can users be connected to the SQL Server 7.0 server while the installation upgrade process runs?
Answer:
No. When you perform an installation upgrade, the SQL Server 7.0 server is stopped and started; users are not able to stay connected. 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 SQL Server 7.0 to that instance of 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.
Can users be connected to the SQL Server 6.5 server while the upgrade process runs?
Answer:
No. During the upgrade process, the SQL Server 6.5 server is stopped and started while objects are scripted and data is extracted. When the data transfer starts, only SQL Server 2000 is running, and it is not possible to access SQL Server 6.5.
How should I configure my SQL Server 6.5 server before performing the upgrade?
Answer:
If you are upgrading your existing SQL Server 6.5 server to a different 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.
Can I consolidate databases from two or more SQL Server 6.5 servers onto one SQL Server 2000 server?
Answer:
No. The upgrade process keeps track of the server being upgraded and only allows databases from one SQL Server 6.5 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 SQL Server 6.5 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.
Can I upgrade only one or a few of my databases to SQL Server 2000?
Answer:
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, install SQL Server 2000 as a separate instance and use the Copy Database Wizard to upgrade the databases. For more information, see
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. Microsoft recommends that you convert all production databases on a server at the same time to minimize potential problems. Even if you only want to convert a subset of your existing SQL Server databases, you should still convert them all at the same time.
If you are not upgrading all of the SQL Server 6.5 databases at the same time, 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 after all of the other SQL Server 6.5 databases. Any objects that were created in SQL Server 6.5 databases because 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 been converted, they contain nondefault objects based on the SQL Server 6.5 model database. Because the objects are 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 are applied only to new SQL Server 2000 databases. All of the nondefault objects in the SQL Server 6.5 converted databases will have been created by scripts during the conversion process of those databases.
Can I run SQL Server 2000 at the same time as SQL Server 7.0 or SQL Server 6.5 on the same computer?
Answer:
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 versions on a particular computer at a time. 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 SQL Server 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 in which the SQL Server 7.0 default instance is upgraded, only the SQL Server 2000 default instance is accessible following the upgrade.
Important 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 Working with Instances and Versions of SQL Server.
Why am I getting the following error during a SQL Server 6.5 conversion?
@@servername not valid
Answer:
This error message may occur if the SQL Server 6.5 server you are upgrading has not been named. To resolve these problems, perform the following steps on the SQL Server 6.5 server:
- In isql or isql/w, run the following query to make sure that the server has a name:
SELECT @@servername
- If the server does not have a name, run the following stored procedure to add a name:
sp_addserver <server_name>, local
When you upgrade a SQL Server 6.5 server, what causes the following error messages?
Cannot open default database
Error querying @@servername
Answer:
If the default database for the system administrator has not recovered yet, or if it is marked as suspect, the upgrade wizard produces one of these error messages. Resolve the problem with the default database and run the upgrade wizard again.
When you upgrade a server running SQL Server 6.5, the upgrade wizard seems to stop responding and fails. Why?
Answer:
If applications or services have open ODBC connections to the SQL Server 6.5 server during the conversion process, they may not allow the server to shut down completely. The conversion process will not proceed to the next step if it does not receive verification that the server running SQL Server 6.5 has been completely stopped. The conversion process appears to stop responding and eventually fails. To resolve the situation, close all applications and services that may have ODBC connections or that may be using SQL Server before you perform the upgrade. If either SQL Profiler or SQL Trace is connected to the server running SQL Server 6.5, you will see a similar problem. Although the server will not actually stop responding, tasks that once processed quickly are now exponentially slower.
Where can I look to see a record of any errors that I may have encountered during the upgrade process?
Answer:
During the upgrade process, detailed logs are generated and stored in your SQL 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 has its own subdirectory with output and error files that were generated during the upgrade process.