INF: SQL 6.5 Database Upgrade with a Space in the Path to Master.dat Fails (246181)

SUMMARY

When you attempt to perform a database upgrade with the Upgrade Wizard, the SQL Server Upgrade Script Interpreter screen displays a message box at the point of EXPORT and IMPORT VIA NAMED PIPES titled Informational Files Found. The message box contains the following two files:

EXPORT and IMPORT VIA NAMED PIPES - 003MODEL.ERR
EXPORT and IMPORT VIA NAMED PIPES - 003MODEL.OUT

The contents of the EXPORT and IMPORT VIA NAMED PIPES - 003MODEL.ERR file is as follows:

Export Exit Code: -1 - d:\mssql7\upgrade\export.exe -CodePage 1252
-DeviceType Pipe -AllTables yes -MasterPath C:\MSSQL\test bug
DATA\MASTER.DAT -DatabaseName model -DevicePath \\.\pipe\~cnvpipe0

I do not recognize the option <b>
Usage :
export.exe
-MasterPath _MASTER_DEVICE_PATH_
Import Exit Code: -1 - ~cnvpipe0

Msg 4854, Level 21, State 1, Server LARRYCHE2, Procedure , Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]****
Time:11-15-1999 10:49:10.558
Error return from function CreateFile
in file CnvPipe.cpp
Line=140
rc=2
Msg=The system cannot find the file specified.
****

The EXPORT and IMPORT VIA NAMED PIPES - 003MODEL.OUT file contains the following:

One Machine Data Transfer...
Export Exit Code: -1 - ~cnvpipe0
Import Exit Code: -1 - ~cnvpipe0

This condition may be caused by the SQL Server 6.5 installation having one or more spaces in the path of the directory to the Master.dat file.

For example: C:\MSSQL\test bug DATA\MASTER.DAT

The following excerpt is from SQL Server 6.5 Books Online:

The default SQL Server 6.5 root directory is C:\MSSQL, although during installation the setup program allows you to change this setting. SQL Server directory names must conform to the FAT 8.3 naming convention, even if the directory is installed on an NTFS volume. Do not use spaces in the directory name.

MORE INFORMATION

Relocate the SQL 6.5 Master.dat file to a directory path that does not have spaces. To do this, perform the following steps:

If SQL Server 7.0 is on the same computer as SQL 6.5, version switch to 6.5.
Match the SQL Server 6.5 device names with their paths using the sp_helpdevice stored procedure.

Install the stored procedure sp_movedevice found in SQL Server 6.5 Books Online.

Use master
Go

Exec sp_configure "allow",1
RECONFIGURE WITH OVERRIDE
go

CREATE PROCEDURE sp_movedevice @@devname varchar(30), @@newpath varchar(255)
AS
BEGIN
EXEC sp_configure "allow",1
RECONFIGURE WITH OVERRIDE
BEGIN TRAN
UPDATE sysdevices set phyname = @@newpath WHERE name = @@devname
IF @@rowcount <> 1
BEGIN
PRINT "**********************ERROR**********************"
PRINT "Moved failed - more or less than one row affected"
PRINT "**********************ERROR**********************"
ROLLBACK TRAN
END
ELSE
BEGIN
PRINT "Device moved successfully"
PRINT "Change will take effect next time you start SQL Server"
COMMIT TRAN
END
EXEC sp_configure "allow",0
RECONFIGURE WITH OVERRIDE
END
Go

EXEC sp_configure "allow",0
RECONFIGURE WITH OVERRIDE

Run sp_movedevice master, 'new path' as follows:
```
exec sp_movedevice MASTER, 'D:\MSSQL\DATA\MASTER.DAT'
reconfigure
						
```
To ensure that the changes made were the ones intended, run the sp_helpdevice stored procedure to confirm that the new location is correct.
Open the SQL Server 6.5 Enterprise Manager, highlight the server, click Server, click SQL Server, and then click Configure. Modify the "Master Database Path" option on the Server Options tab of this dialog box to look like this:
D:\MSSQL\DATA\MASTER.DAT
WARNING: After completing this step, you must make sure your parameters are in the correct order or you may run into another possible hindrance when using the Upgrade Wizard. To check parameter order, click the PARAMETERS button. In the window titled Existing Parameters make sure the sequence of vertical order for your server parameters matches the following:
-dD:\MSSQL\DATA\MASTER.DAT
-eD:\MSSQL\LOG\ERRORLOG
For additional information concerning this problem, see the following article in the Microsoft Knowledge Base:
231988 BUG: 7.0 Upgrade Loops When 6.5 Parameters Are Reversed
Stop the SQL Server 6.5 services.
Move the Master.dat file to the new location (for example D:\Mssql\Data).
Start the SQL Server 6.5 services.
Check the SQL Server 6.5 error log to see if all the databases have recovered or if there are any marked as suspect. Correct any errors. If there are no errors, move to the next step.
Use the Upgrade Wizard to perform the upgrade.NOTE: If you want to have all SQL devices (MSDBData and MSDBLog, and so forth) under the new path, you can repeat steps 4, 6, 8, and 9. For example:
```
exec sp_movedevice msdbdata, 'd:\mssql\data\msdb.dat'
exec sp_movedevice msdblog, 'd:\mssql\data\msdblog.dat'
					
```

Another workaround is as follows:

If SQL Server 7.0 is installed on the same computer as SQL Server 6.5, uninstall SQL Server 7.0.

NOTE: If there are SQL 7.0 databases already existing, do not remove the data files/folder when removing SQL Server 7.0. Instead, before removing SQL Server 7.0, rename the folder that contains the data and log files to DATA_OLD so that the folder will not be inadvertently deleted during the uninstall, and to ensure that you will be left with a backup of the current state of the SQL Server 7.0 before the uninstall.
Create and save a script for the later re-creation of your SQL 6.5 user databases by running the stored procedure sp_help_revdatabase.
Dump the databases.
Run the following script, substituting for your particular databases' name. Save the output, and search for any "Msg" statements. If there are no "Msg" error messages, continue to the next step. If there are, resolve those error messages before continuing.
```
DBCC TRACEON (3604)
go
DBCC CHECKCATALOG (pubs)
go
DBCC TEXTALL (pubs)
go
DBCC NEWALLOC (pubs)
go
DBCC CHECKDB (pubs)
					
```
Uninstall SQL Server 6.5.
Reinstall SQL Server 6.5 with the Master.dat file being written to a folder without spaces.
Re-create the user databases by using the script from the sp_help_revdatabase output.
Restore the SQL Server 6.5 databases.
Run the following script again, substituting for your particular databases' names. Save the output, and search for any "Msg" statements. If there are no "Msg" error messages, continue to the next step. If there are, resolve those error messages before continuing.
```
DBCC TRACEON (3604)
go
DBCC CHECKCATALOG (pubs)
go
DBCC TEXTALL (pubs)
go
DBCC NEWALLOC (pubs)
go
DBCC CHECKDB (pubs)
					
```
Install SQL Server 7.0. If there were previous SQL 7.0 user databases in existence prior to uninstalling, then after the install, swap the new MSSQL7\DATA folder name with the original DATA_OLD.
Use the Upgrade Wizard to perform the upgrade.