Rename DB Files

[francox]

In EM, when you right click a database and go to properties you can see on the general tab the file name,the location,the space allocated in MB and the file group.
Is it possible to change the file name and how you achieve it?
Where are these names stored?
I really appeciate any suggestions.
Thank you very much!
Franco

 

[RickCole]

You can alter the properties using the alter databse command from an ISQL window. Syntax as follows. Check BOL for more in depth details.

Rick.

ALTER DATABASE database
{ ADD FILE <filespec> [,...n] [TO FILEGROUP filegroup_name]
| ADD LOG FILE <filespec> [,...n]
| REMOVE FILE logical_file_name
| ADD FILEGROUP filegroup_name
| REMOVE FILEGROUP filegroup_name
| MODIFY FILE <filespec>
| MODIFY FILEGROUP filegroup_name filegroup_property
}


<filespec> ::=
(NAME = logical_file_name
[, FILENAME = 'os_file_name' ]
[, SIZE = size]
[, MAXSIZE = { max_size | UNLIMITED } ]
[, FILEGROWTH = growth_increment] )

 

[tlbroadbent]

Here is a sample script that will rename database files. Note that the files must be freed from SQL Server, using sp_detachDBin this case, before renaming. Database file names are stored in sysfiles1 on each database. The MDF file name is stored in sysdatabases on master.

Use master
go
if exists (Select * from sysdatabases Where name=N'addressbook')
exec sp_detach_db AddressBook
go
exec xp_cmdshell 'ren C:\&quot;Program Files&quot;\&quot;Microsoft SQL Server&quot;\MSSQL\Data\AddressBook_Data.mdf Address_Data.mdf'
exec xp_cmdshell 'ren C:\&quot;Program Files&quot;\&quot;Microsoft SQL Server&quot;\MSSQL\Data\AddressBook_Log.ldf Address_Log.ldf'
go
exec sp_attach_db
@dbname = AddressBook,
@filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Address_Data.MDF',
@filename2 = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Address_Log.LDF'
Go Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[francox]

Thank you Rick.
Best Regards.
Franco

 

[francox]

Thank you Terry!
Franco