June 14, 2002 at 4:37 am
Does anyone know how to rename a logical file name in SQL Server v 7.0?
In 2000 you can use the ALTER DATABASE MODIFY FILE (name = 'xyz', newname = 'abc') but there is no equivalent for 7.0.
Thanks
Deon
June 14, 2002 at 5:50 am
You could update sysdatabases, you'll have to allow updates.
Andy
June 14, 2002 at 6:11 am
How would that work? I want to update the name column in MyDB..sysfiles as I need to change the logical name of the data file and log file.
June 14, 2002 at 6:29 am
If the file is small the easiest and surest way I found was to do a file backup of the database then restore. When you do the restore in EM under options you can set the name of the destination file(s).
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 14, 2002 at 7:04 am
Thanks for the reply, but I have tried this but it does not work. The only time a logical file name appears when restoring is if you restore from device. I have tried this but it didn't work.
From what I gather, backup / restore won't really work as the data I need to update is held in MyDB..sysfiles. So if you restore a database, this data will get restored.
Cheers
Deon
June 14, 2002 at 8:01 am
Sorry I was thinking physical file not logical file. Brain on vacation. As for being able to rename logical I don't believe you can. Even if you run EXEC master.dbo.sp_configure 'allow updates', '1' RECONFIGURE WITH OVERRIDE to alow system table modifications you get this error
quote:
Object 'sysfiles' cannot be modified.
when you try. The only other way would be to create a new DB with logical filenames like you want and copy all objects and data to it with permissions at the works using Import/Export wizard. Then drop the original DB and rename the new back to the old.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 14, 2002 at 8:05 am
I thought as much... thanks for the help anyway.
Another option we are looking at is to restore the database to SQL 2000, use alter database to set the logical names, dump the database and restore it back to SQL 7.0.
Thanks again
Deon
June 14, 2002 at 11:31 am
Structure changes occurr to DB's when restored to a 2000 server from a 7 backup that I believe will prevent this from being able to be moved back. I suggest test but do not restore over your old db.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply