Renaming the logical name of data and log files in a database

Question

Post reply

Renaming the logical name of data and log files in a database

black.clouds.999

Mr or Mrs. 500

Points: 501
More actions
May 1, 2010 at 3:46 am

#222084

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 !!!!!!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

to rename a files logical name you need to use the following

Get the logical names for your chosen database using

select f.name from sys.databases d

inner join sys.master_files f on d.database_id = f.database_id

where d.name = 'AdventureWorks'

Change the logical names using (you need one for each file you want to change)

ALTER DATABASE yourdb MODIFY FILE (NAME=currentname, NEWNAME=newlogicalname)

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

black.clouds.999 Mr or Mrs. 500 Points: 501 More actions · Answer 2

black.clouds.999

Mr or Mrs. 500

Points: 501

May 1, 2010 at 9:19 am

#1160905

Thanks Perry for the quick and clear response.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

hey, you're welcome!

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

black.clouds.999 Mr or Mrs. 500 Points: 501 More actions · Answer 4

Hi Perry,

Are you available online in any of the chat boxes like yahoo,gmail etc.

If so and if you dont mind can you give me your email id please.

With Kindest Regards,

Sudarsan Madhavan