SQL SERVER 7 LOGICAL FILE NAMES

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SQL SERVER 7 LOGICAL FILE NAMES

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-01 : 23:24:06

Mike writes "I have a quick question for you on restoring a SQL Server 7 DB..
I have a backup from another SQL SERVER 7 NT 4 Server...it has a setup similar to below:-

Logical File Name Physical Filename
File1 d:\data\temp1.mdf
File2 d:\log\temp1.ldf

I am renaming the files using the Force Restore Over Existing Database option to

Logical File Name Physical Filename

FileX d:\keepdata\temp1.mdf
FileY d:\keeplog\temp1.ldf

Whilst it is happy for me to change the Physical
Filename it complains about the logical file name.

If I leave the logical file names as File 1 and File2
everything is ok...but if I try to rename them I get an error saying:-

FileX is not a database file for database XXX back or restore terminated.

How can I change the Logical File name ?

Thanks in advance"

izaltsman
A custom title

1139 Posts

Posted - 2002-01-02 : 09:17:13

Restore the database, keeping the original logical filenames.
Then issue an ALTER DATABASE command like the one below:

ALTER DATABASE mydb
MODIFY FILE (NAME = 'file1', NEWNAME = 'brand_spanking_new_name_1')
--Then do the same thing for the other file.

Edited by - izaltsman on 01/02/2002 09:17:36

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2002-01-07 : 18:05:15

I am having the same issue, as originally posted. When I try izaltsman's solutions I get the following error.

'NEWNAME' is not a recognized CREATE/ALTER DATABASE option.

Any ideas?

Jeff Banschbach
Consultant, MCDBA

izaltsman
A custom title

1139 Posts

Posted - 2002-01-07 : 19:22:04

For some reason I thought that SQL7 allowed this (SQL2k definitely does). But I guess with SQL7 you are gonna have to go directly against system tables.
WARNING:
Although this appears to work (I tried it on a SQL7 box), I can not guarantee this is a safe thing to do.

Having said that, here's the code:


USE mydb
GO
exec sp_configure 'allow updates', 1
GO 

RECONFIGURE WITH OVERRIDE

GO 

DECLARE @new_name varchar (100)
DECLARE @old_name varchar (100)

SELECT @old_name = 'old_name'
       , @new_name = 'new_name'

UPDATE sysfiles1
     set name = @new_name
     where name = @old_name

GO

exec sp_configure 'allow updates', 0
GO 

RECONFIGURE WITH OVERRIDE

GO

Edited by - izaltsman on 01/07/2002 19:26:34

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2002-01-08 : 11:02:31

That's what I was afraid of. I can do it on my SQL 2K boxes but not on 7.0. I was hoping some one knew of a way all leagal style on 7.0. This is a production database server so I don't think hacking at the system tables is the way to go. I think we will just have to live with incorrect logical file names. Thanks for your help!

Jeff Banschbach
Consultant, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources