SQL Server Performance Forum – Threads Archive

Upgrading sql 7 to 2000 – please help!

Hi. When trying to install sql 2000 using the upgrade option from sql7sp4, the installation proceeds but then hits an error on the first upgrade sql script.. Could not run messages.sql (1) Any ideas please?!

What is the error stated in Errorlog for upgrade? I guess the reason would be – the installation is trying to add some message to systemessages and the error number has been already taken by some user defined message. I am not sure of this but it would be worthwhile to look at the SQL Script message.sql. Gaurav

Thanks. I noticed all protocols had been removed from client network util. In server net util, tcp and named pipes had been removed (presumably by the upgrade procedure). I cannot connect properly via enterprise manager anymore to check the log file, it just stalls when trying to expand the database tree. It seems my server is now half between stages. When I rerun sql 2000 setup I am told a previous install failed (I can either exit, or retry the install). Retrying results in the same error. This is our main production server, it all went swimmingly on the dev server.
Appreciate the help!

When u upgrade the SQL Server, there is a Error Log Directory that is created. In that there will be a Err file. I am not sure about the directory and the file name. But you can see the directory in the SQL 2K deirectories. I think the path of the file is also shown when the error is displayed on the upgrade screen. Look for the error file in that. Also for reading SQL Error Log, you can open the ERRORLOG file present in the LOG directory of SQL Server 2K Directories. Any text editor like notepad can be used to open that. I don’t think this error will be listed in error log as this is a upgrade error and should be mentioned in the upgrade log mentiond above. Gaurav

the log suggested one of my databases was causing the problem, so I shut down sql and moved the mdf and ldf so sql couldnt find them. Enterprise manager now starts up and expands database list. So im making progress. Now I try to restore the database from backup, However, I get the error: ————————— Microsoft SQL-DMO (ODBC SQLState: 42S22) ————————— Invalid column name ‘flags’. Too many arguments were supplied for procedure sp_restoredbreplication. Could not adjust the replication state of database ‘CAR’. The database was successfully restored, however its replication state is indeterminate. See the Troubleshooting Replication section in SQL Server Books Online. Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful. Backup or restore operation successfully processed 11227260 pages in 816299.529 seconds (19.012 MB/sec). ————————— OK ————————— Seems like its going to be a long night <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />

When SQL Server does a backup / restore operation, it makes an entry in the msdb..backupset table. I guess there is some problem in this table. If you are able to connect to this server, try to run dbcc checks on the msdb..backupset and eventuallly the database. Although not making this entry won’t affect the database operation as this is kept just as a record of the operation. But still I would like you to investigate into the problem so that further backup / restore operations don’t create problem. One more thing if you are restoring the database on SQL 7.0 installation, you can wait for the upgrade to finish as SQL 7.0 database can be restored directly to SQL 2k installation. For the replication question… Cann’t help you much as I have little Experience in that <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />( HTH. Gaurav

Many thanks for the advice. My current situation is this.. After proceeding past the error message, the database was indeed restored (as far as I can tell) correctly.
DBCC checks on all databases show no obvious problems, however, it seems like the upgrade of the msdb database has not worked correctly. Right clicking server properties shows error messages from enterprise manager (such as missing stored procedures in master and msdb). Also several other places complain of missing xp_regread. The stored procedure is defined, but the dll which it is implemented in is not present that i can see. The replication error isnt a problem in itself since this server doesnt use replication, but im not happy there are even any errors to be honest. SELECT @@VERSION reports SQL 2000. Im really considering my options right now. Instinct tells me to uninstall and reinstall a clean sql 2000 with no databases. And then restore the v7 backups into it. The upgrade from 7 to 2000 apparently runs some conversion scripts on the user databases. If I choose to restore v7 backups rather than retry the upgrade wizard route, at what point will this conversion of my user databases take place? Rather my question is, I think they will run in 70 mode, how would I then do a similar conversion to bring them up to 2000? Thanks

Go with your instincts. Clean install, restore the dbs and do the upgrade that way..is my advice. Tom Pullen
DBA, Oxfam GB

Well I would have recommended restoring the user databases on a fresh installation of SQL 2k but this will mean u will loose all security information stored in master and hence u’ll have to create the logins, roles etc. again. Also the SQL Agent info like jobs etc. will also be lost. We can restore the master database as well but that will mean we may miss the changes in SQL 2k. Although I guess they can be corrected by using rebuild master utility. But this is too much of risk. What makes u sure that msdb has not been upgraded correctly? When it comes to the missing dll, you can find the dll name from any other SQL 2k installation by executing master..sp_helptext xp_readreg which shows xpstar.dll as the dll. So you can copy this dll to your machine from some other machine. I don’ think the user databases are changed when upgrading from SQL 7.0 to 2K. I restored one SQL 7.0 Database to SQL 2k and the compatibilit showed as 80 which means SQ 2k. So I guess the best way is to remove the dll error, correct any error in msdb database and then test your application. Else, if you are planning to do from scratch – first SQL 2k and then rstoring databases, take a backup copy of the SQL Server databases just migrated and then after the installation is over, restore the user databases from this backup rather than 7.0 backup. This will ensuer that all the changes done in the user databases as part of upgrade are reflected in the restored SQL 2K database. HTH. Gaurav

Sounds like a plan! Ill opt for a fresh install. I dont like the idea of copying the dll file because it kind of suggests to me if it failed to copy the dll, it could snowball into quite a few errors, some of which might not be as imediately apparent. I know msdb has not fully upgraded because EM complains of problems with the structure of some tables (ie, similar to the error message about ‘flag’ column not existing). Thanks for the info about restored db’s reporting 80. Relieves a bit of my headache! The sql server log reported conversion processes occuring on each user table, which made me think restoring a 7.0 backup would not replicate these upgrade operations. The bitter irony is that I read on this forum not so long ago that someone did not fully trust upgrade wizards for such a major task. In my infinite wisdom I dismissed this as being perhaps overly paranoid: After all, surely MS would ensure this important process is as robust as possible. I think Ive learnt something today <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

Good luck.. stay calm.. I hope you get home before people come in for work tomorrow! Tom Pullen
DBA, Oxfam GB

I had the same problem with my upgrade. Our test environment went fine, but the production server gave me the trouble. Things never go smoothly when you want them to. I tried the upgrade a second time like you did and still got the error. Rather than doing a clean install and redoing my security, db’s, linked servers, DTS packages, etc…, I gave it one last try. The third time’s a charm. Upgrade worked and everything was fine afterwards. It’s no scientific solution, but for anyone else having the same issue, it’s worth a shot.

If you could refer to Upgrade log file for the reason behind failures, which will give information. Also with SQL server error log for information. _________
Satya SKJ

]]>