Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
Converting SQL Server 6.5 Databases to SQL Server 7.0
Microsoft’s SQL Server development team provides step-by-step instructions for using the SQL Server Upgrade Wizard to convert all elements of SQL Server 6.5 databases (except the data) to SQL Server 7.0.
Microsoft's Sql Server Development Team
February 20, 2002
2 Min Read
Can I create SQL Server 7.0 versions of our SQL Server 6.5 database that include everything (tables, views, logins, user data types—UDTs—and so on) except the application data?
Creating SQL Server 7.0 versions of your SQL Server 6.5 database is possible, but doing it the easy way requires downtime at both the target and source databases. (Don't attempt this project in prime time!) The SQL Server Upgrade Wizard will create these databases for you. The wizard contains a specialized OLE DB driver that can read SQL Server 6.5 files, convert the 6.5 database structure into a SQL Server 7.0 equivalent, then create the scripts required to copy over the objects (and data). This process requires full access to the SQL Server 6.5 files and hence downtime. In addition, to load the SQL Server 6.5 files into SQL Server 7.0, the wizard has to start the loading process with a specific trace flag, which requires stopping and starting the SQL Server 7.0 instance. Here are the steps you need to take to convert the databases.
On the Start menu, choose the MS SQL Server-Switch group, then open the wizard.
Follow the prompts, selecting as many databases as you want.
In the Database Creation window, you have the option of reviewing and possibly altering the database structure that the conversion will create.
Clicking the Finish button in a later dialog box takes you into the SQL Server Upgrade Script Interpreter.
In the SQL Server Upgrade Script Interpreter, click the Pause Between Steps button, choose Export, then complete the export steps (about four or five steps).
As soon as you've seen the last Export
All the instance-wide conversion scripts will be in your sqlserver..upgradesql65servername_Date+#_Time directory. (For example, in the directory, you can see the createdb.sql script, which shows you the CREATE DATABASE commands that the Interpreter would have to run to create the SQL Server 7.0 databases.) You'll find the database-object-specific scripts in subdirectories specific to each dbid (a number for the database found in master.dbo.sysdatabases) and dbname (database name). You can easily look through the files and figure out which files contain UDTs, which files contain stored procedures, and so on.
The hard way to convert the SQL Server 6.5 database to SQL Server 7.0 is to write your own queries against the SQL Server 6.5 catalog or use sp_help_revdatabase to create an output file. Then, you can use the output file as input to a program that parses the structures and do your own conversion.
About the Author
You May Also Like
