In SQL Server 6.5, devices were used as the means to allocate space on the file system and the databases were created from the devices. Although it was possible, it was never a good option to combine the data and log segments, but rather have a dedicated data device and a dedicated log device to prevent the error messages you are receiving. Based on the scenario, below outlines my recommendation:
- Schedule downtime to migrate the data from one database to another with the proper devices and segments.
- Backup the current production SQL Server database.
- Script out all of the objects in the database (tables, indexes, stored procedures, defaults, etc.) based on object type and save as text files in the file system.
- Create a new data device sized appropriately on the SQL Server with a suffix of '_New'.
- Create a new log device sized appropriately on the SQL Server with a suffix of '_New'.
- Create a new database on the SQL Server with a suffix of '_New' based on the new data and log devices just created.
- Execute the contents of the text files in ISQL\W to create the tables, stored procedures, etc. with the exception of indexes.
- Set the current production SQL Server database to read-only and dbo use only to prevent any data modifications.
- Issue BCP OUT statements from the current production SQL Server database to the file system.
- Issue BCP IN statements from the file system to the '_New' SQL Server database.
- Issue the CREATE INDEX statements from the text files in the file system.
- Check the system tables between the current production SQL Server database and the '_New' to verify all of the object counts are equal between the two databases.
- Rename the current production SQL Server database to have a suffix of '_Old'.
- Rename the '_New' database to the proper database name.
- Request the users test the application.
- SQL Server Upgrade recommendations and best practices
- SQL Server 6.5 to 2000 critical upgrade decisions and redundant upgrade architecture
For More Information
- Dozens more answers to tough SQL Server questions from Greg Robidoux and Jeremy Kadlec are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in June 2004