FIX: Attaching Database Files Saved In Different Disk Folders Through SQL Enterprise Manager (293329)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q293329
BUG #: 236633 (SHILOH_bugs)

SYMPTOMS

When you use SQL Server Enterprise Manager to attach multiple database files, which are saved on different hard disk driver folders, the following problems may occur:

  • If you create a database for some of the log or data files that are stored in a different directory than the primary database file, after you detach and try to reattach the database the Current File(s) Location check box is selected, which indicates there are invalid filenames that correspond to the files that are not located in the same folder as the primary database file. No ellipsis button is available to browse the file location for the secondary data file and the log file location. If the file location is manually changed to the correct location the attach process completes successfully.

  • While reattaching a previously detached database that has only one log file after the log file has been deleted (the engine allows the database to attach and automatically creates a new log file if you do not explicitly specify a log file), the Current File(s) Location check box under the Attach Database dialog box is selected, which indicates invalid filenames correspond to the log file that is not located in the same folder as the primary database file. After you click OK , the single file attachment completes successfully.

  • When you attach only a single data file without a log file, changing the database name disables the OK button and the attach process cannot complete.

RESOLUTION

To resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

WORKAROUND

You can run the sp_attach_db stored procedure to attach the database for which some of the log or data files were stored in a different directory than the primary database file.

You can run the sp_attach_single_file_db stored procedure to attach a single data file.

STATUS

Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.

MORE INFORMATION

After You Apply SQL Server 2000 Service Pack 1

  • The Attach dialog box now looks for the files (secondary database files and log files) both in the path of the primary database file and in their initial path (from where the files where detached).

  • If the database initially had only one log file, SQL Server allows the attachment of the database if the log file is already deleted. A new message box prompts the user to check and see if they really want to attach the database without having the log file.

  • The algorithm for checking if the OK button should be enabled was modified and it is called now not only when an entry in the grid is edited but also after the grid is automatically filled with filenames (a new primary data file is specified).
Modification Type:MajorLast Reviewed:11/6/2003
Keywords:kbBug kbfix kbSQLServ2000sp1fix KB293329
©2003 Microsoft Corporation. All rights reserved.