Rename MDF and Log file on 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

General SQL Server Forums

New to SQL Server Administration

Rename MDF and Log file on a Database

Author

Topic

nmcl72
Starting Member

1 Post

Posted - 2010-01-19 : 05:33:35

Hi

I'm a novice to SQL. I restored a database on to SQL 2005 which is working okay. But I've noticed the mdf and ldf files don't have the extension on them and are listed in the directory as unknown file.

What is the easist and quickest way to put the extension on to the file. I want to keep the filename same, just add the .mdf and ldf extensions.

Thanks.

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-01-19 : 11:30:40

Syntax
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] )

MODIFY FILE
Specifies the given file should be modified, including the FILENAME, SIZE, FILEGROWTH, and MAXSIZE options. Only one of these properties can be changed at a time. NAME must be specified in the <filespec> to identify the file to be modified. If SIZE is specified, the new size must be larger than the current size of the file. FILENAME can be specified only for files in the tempdb database, and the new name does not take effect until Microsoft® SQL Server™ is restarted.

http://msdn.microsoft.com/en-us/library/ms174269.aspx

Edit: I tried this and it did not work... Try detaching, renaming via explorer, the reattaching.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-19 : 11:59:12

you modify logical name with alter database.

to modify physical name, detach, use OS level rename, attach.

can also use backup/restore with MOVE option

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources