Delete or Rename Logical File Name?

[jluost1]

Can anyone tell me how to rename, if possible, or delete logical file name of a database?

I created a database with a logical file name that I cannot use now (because it doen't follow my naming convention). But I have to keep the database name. I tried very hard to delete or rename the logical file name. For example, I backup the database (db_original) and restore it(db_original) as a new name(db_new). Then I delete the old database(db_original) and backup the new one(db_new) then restore it(db_new) with the old name(db_original).

Unfortunately, as long as I keep the database name unchanged, the logical filename still exists.

Any thoughts?

 

[RickCole]

Try this

Backup the database (db_original).

Drop database (db_original) and delete the files.

Create database (db_original) with new naming conventions.

Restore (db_original) to new database just created and check the options tab to ensure names are as you wish.

Rick.

 

[tiger5000]

I am attempting to follow the steps you outlined above, but I keep getting the following error:

Error 3702: Cannot drop the database XYZ because it is currently in use.

Can anyone help shed some light on this?

 

[fluteplr]

1. Make sure no one else is using the database.
2. Make sure you are not using the database. If you are running this in query analyser make sure that the dropdown showing which database is active is set to some other database such as master.

You might also want to look at 'alter database' which will allow you to alter the logical filename without all this stuff.

the following was cut from the alter database documentation in SQL BOL

MODIFY FILE

Specifies the given file that should be modified, including the FILENAME, SIZE, FILEGROWTH, and MAXSIZE options. Only one of these properties can be changed at a time. NAME must be specified in the <filespec> to identify the file to be modified. If SIZE is specified, the new size must be larger than the current file size. FILENAME can be specified only for files in the tempdb database, and the new name does not take effect until Microsoft SQL Server is restarted.

To modify the logical name of a data file or log file, specify in NAME the logical file name to be renamed, and specify for NEWNAME the new logical name for the file.

Thus:

MODIFY FILE (NAME = logical_file_name, NEWNAME = new_logical_name...).