changing the logical name of a database

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)

changing the logical name of a database

Author

Topic

burbakei
Yak Posting Veteran

80 Posts

Posted - 2002-11-13 : 09:09:59

whenever i what to change the logical name of a database i go through the following routine:

USE master
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE

then i go to the purposed database and then <name> column of undocumented system table sysfiles1 and again go the following:

USE master
EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE

but i want to it with an easier and safer way especially with ALTER TABLE command. can anybody help me?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-13 : 09:44:20

You could restore a backup to with a new name ....

Do you really rename databases that often? Why?

Jay White
{0}

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2002-11-13 : 11:11:41

What's wrong with good old sp_renamedb? Here's the set I usually use when having to rename a DB... which isn't very often btw.


sp_dboption 'old_name', 'single user', 'TRUE'
sp_renamedb 'old_name', 'new_name'
sp_dboption 'new_name', 'single user', 'FALSE'

Jeff Banschbach
Consultant, MCDBA

burbakei
Yak Posting Veteran

80 Posts

Posted - 2002-11-13 : 12:11:42

i do not want to rename a database name. i want to change the logical name of
a database.
open enterprise manager and right click a database an choose properties.
in datafile tab you will see filename, location, ...
<file name> is the logical name of the database.
if you use sp_renamedb only the database name will be renamed and the entry in
the sysdatabases of master database will be changed. logcal name remains intact.
suppose you have a backup of a database named 'TestDb' with the logical name
'TestDb_Data' and you want to restore it on a database named 'NewDb' with the
logical name 'NewDb_Data'. after restoring the database the logical name of the
restored database (NewDb) will be changed to the backedup database (TestDb).
according to Books online you can change the logical name of a database with
ALTER DATABASE command:

ALTER DATABASE database
MODIFY FILE (NAME = logical_file_name, NEWNAME = new_logical_name...)

what i was wrong about is that i thought with

ALTER DATABASE database MODIFY NAME = new_dbname

can do the job. so you can change the logical name of a database with
MODIFY FILE clause of ALTER DATABASE command.

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2002-11-13 : 12:22:27

So you don't want to change the name of the database. You want to change logical names of database files. That's quite the difference. This can not be done in SQL 7 and can be done in SQL 2000 with the following syntax.

ALTER DATABASE MYDB MODIFY FILE NAME = logical_file_name, NEWNAME = new_logical_name

Jeff Banschbach
Consultant, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources