how to change the logical file name - SQL 7.0

[wrbodine]

Hi,

How do I change the logical file name of a database's mdf and ldf file (File Name under Database Properties)? And can this be done in the restore process of a database?

I thought maybe Alter Database would work for this, but don't know how I would specify what file it was, since I would be changing the name property. In BOL it looks like the name property is the one used for identification.

Thanks,
Ray

 

[tlbroadbent]

SQL 2000 allows one to change the logical name using ALTER DATABASE. As you know SQL 7 doesn't provide this capability. I found a script ("Renaming the Logical Filename&quot on SWYNK.COM that creates a stored procedure you can use to change the name. Download the script from the following page.

SQL Server Script Library (for SQL Server 2000 and v7.x)

http://www.swynk.com/sqlscripts/tsql7.asp

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[naya]

By far the easiest way to rename a filename is to modify it through the system table sysfiles1 located in master.

Since this is a system table, you need to be careful that you don't make a mistake.

hth

 

[tlbroadbent]

Naya,

The script at SWYNK updates sysfile1. Using the script will prevent errors because it disallows certain updates, validates the files names, etc.

I recommend that you join Tek-Tips rather than continuing to post as a visitor. It is free and there are several benefits to membership including email notification of replies to your posts and easy access to all posts that you have made. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.