error while renaming logical name of a db.

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 2008 Forums

SQL Server Administration (2008)

error while renaming logical name of a db.

Author

Topic

blackclouds999
Starting Member

2 Posts

Posted - 2010-05-01 : 05:07:53

Guys out there,I need your help in this ! Plz help me out.

I am actually trying to rename logical names of the data and log files of any particular db.The first file gets automatically renamed.However the second file doesnt change.It throws the below error :

exec udp_renamedb @old_dbname='roof',@new_dbname='cure'
error :
The file name 'cure' has been set.
Msg 1828, Level 16, State 3, Line 1
The logical file name "cure" is already in use. Choose a different name.

The following are the stored procedures used :

create procedure udp_change_logical_name
@logical_name varchar(25),
@old_dbname varchar(25),
@new_dbname varchar(25)
as
begin
set nocount on

declare @cmd varchar(150)
set @cmd='alter database '+@old_dbname+' modify file(name="'+@logical_name+'",newname="'+ replace(@logical_name,@logical_name,@new_dbname) +'" )'
exec(@cmd)

end
-----------------------------------------------------

create procedure udp_renamedb
@old_dbname varchar(25),
@new_dbname varchar(25)
as
begin
set nocount on
declare @cu cursor

declare @cmd1 varchar(550)

set @cmd1='declare @log_name nvarchar(50);declare @cu cursor;set @cu=CURSOR for select name from '+@old_dbname+'.sys.database_files;open @cu;fetch next from @cu
into @log_name;
--exec @cmd1;

while(@@fetch_status=0)
begin
execute udp_change_logical_name @logical_name=@log_name,@old_dbname='+@old_dbname+',@new_dbname='+@new_dbname+';
--print @log_name;

fetch next from @cu
into @log_name
end
close @cu
deallocate @cu'

exec (@cmd1)

end

Plz help out !!!!!!

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-05-02 : 22:32:28

Have you tried

ALTER DATABASE oldName MODIFY NAME = newName

Also, have you tried right clicking in SSMS and selecting Rename?

===
http://www.ElementalSQL.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources