How to change Logical File Names for database?

[jballum]

I was given a full backup of a database and want to restore it. But I want to restore it with different logical file names then it was created with.

Is this possible during a database restore?

If not, any suggestions on the easiest way to recreate the existing one with different logical file names?

Thanx

JB

 

[Corran007]

I am unsure about renaming the logical during the restore, but i just do it afterwards.

is this sql7 or 2000 because they are changed differently.

2k
Alter database Database
Modify File
( NAME = 'Database_data', NEWNAME = 'Databasenew_Data')

7 i use this.
/************************************************************************************************************************
* sp_rename_logical_filename *
* *
* This stored procedure was created to allow one to change logical file names of datbase files in a 7.0 database. *
* After doing a restore with the move option, one can change the physical file path and physcal file name, but not *
* the logical name. This posed a problem because we (GMAC) have internal standards for database names and for *
* logical and physical file names. *
* *
* This stored procedure does change the configuration paramater 'allow updates' so it can directly update the *
* sysfiles1 table in the database in question. I thought it was very interesting that I was actually able to make *
* this change from within a storeded procedure. By the way, I've never run this on a production server ... yet. *
* *
* Usage: use database in question *
* go *
* exec sp_rename_logical_filename current_logical_name, new_name *
* *
* You will have to execute this stored procedure for every logical name you wish to change in the database. It was *
* interesting that even with 'allow updates' set, I could not update the sysfiles table ... so I settled for *
* updating sysfiles1 instead. From within the database, I used sp_helpfile and sp_helpdb to verify the changes. *
* *
* I'm looking for ideas to improve this stored procedure ... this is just a first run. Please feel free to *
* contact me with any ideas or constructive criticism. You could go nuts and add checks for all of the db *
* options and maybe only allow this if the db is in single user mode ? What if the db is marked suspect ? To be *
* more official, we could have more raiserrors and less prints. *
* *
* Original Author: Joe Toscano 6/1/2000 (jtoscano@ix.netcom.com) *
* Revision History: *
* *
************************************************************************************************************************/

if exists (select 1 from sysobjects where name = 'sp_rename_logical_filename' and type = 'P')
drop proc sp_rename_logical_filename
go

create proc sp_rename_logical_filename
@current_name varchar(64)= null,
@new_name varchar(64)= null
as

declare @msg varchar(200),
@cmd varchar(240),
@err int

set nocount on

if @current_name is null or @new_name is null
begin
select @msg = 'Usage: exec sp_rename_logical_filename ' + char(39)
+ 'current_name' + char(39) + ',' + char(39) + 'new_name' + char(39)
print @msg
print ''
print '(While in the database in question)'
return (1)
end

if db_name() ='master'
begin
print 'You must be in the database that contains the logical filenames you wish to modify ... not in master'
return (1)
end

/*
** Only the SA can execute sp_configure to change config parameters
*/
if (not is_srvrolemember('serveradmin') = 1)
begin
print 'In order to execute this stored procedure, you must be permitted to directly update system tables'
raiserror(15125,-1,-1)
return (1)
end
/*
* We are not going to let you be in a transaction ... at least for now.
*/

if @@trancount > 0
begin
raiserror(15002,-1,-1, 'sp_rename_logical_filename')
print 'Please perform a commit or rollback and then execute this stored procedure'
return(1)
end

/*
* Let's make sure the logical file name you specify exists
*/

if not exists (select 1 from sysfiles1 where name = @current_name)
begin
select @msg = 'Logical File Name: ' + @current_name + ', not found in ' + db_name() + '. Please check the name'
print @msg
return (1)
end

/*
* Change the option to allow updates to system tables ... this is a server wide setting ... that's a bit scarry!
*/

select @cmd = 'exec sp_configure '+ char(39) + 'allow updates' + char(39) + ', 1' + char(13)
exec (@cmd)

select @cmd = 'reconfigure WITH override'
exec (@cmd)

select @err = 0

update sysfiles1
set name = @new_name
where name = @current_name

if @@error <> 0
begin
print 'sp_rename_logical_filename failed: Unable to update sysfiles1 table with new logical name'
select @err = 1
end


/*
* Regardless of the status of our updates ... change the option to allow updates to system tables back to 0
*/

select @cmd = 'exec sp_configure '+ char(39) + 'allow updates' + char(39) + ', 0' + char(13)
exec (@cmd)

select @cmd = 'reconfigure WITH override'
exec (@cmd)


select @msg = 'sp_rename_logical_filename: Changed filename: ' + @current_name + ' to : ' + @new_name
print @msg

set nocount off

return @err

grant exec on sp_rename_logical_filename to public

 

[jballum]

Great!

Thanx