Rename database data file in SQL Server 2005

Question

I need to rename a databases data file. Is this possible through SQL Server Management Studio?

FYI, I do not have permissions to the underlying box.

Edit: I also need to change the location of the file.

Answer 1

you can use sp_detach_db to take the database offline. find the files and rename them, move them whatever. then use sp_attach_db to reattach them from a new location. that's how i do it anyway.

best regards, don

Answer 2

1. Take a full backup of the database.
2. Drop it.
3. Restore it, specifying different file names and paths in the restore dialog.

You can do this with no permissions on the underlying file system, because you are not moving a physical file around, you are asking SQL Server to generate a new file on your behalf and copy data into it from the backup.

To set the new filenames and paths, go to the Options tab of the Restore dialog. You even get a folder browse dialog that shows you SQL Server's view of the file system, not yours.

Minimum permissions required for this procedure:

• db_backupoperator role in the database
• dbcreator role in the server

Answer 3

Yes and No. You can change the database file object filename:

ALTER DATABASE <dbname> MODIFY FILE (NAME=<logicalname>, FILENAME=<newfilename>);

This will update the master catalog so that at first next database open event the new filename will be looked up. But there is no direct way to rename/move the file in Transact-SQL.

You can though use xp_cmdshell to rename the file, or you can deploy a CLR assembly with EXTERNAL_ACCESS enabled that can do the file rename/move operation.

Answer 4

Yes, you can do this, as long as you have the right to detach and re-attach the database, and as long as you find a way to physically rename the files on disk:

1) issues these commands

ALTER DATABASE yourdatabase
MODIFY FILE (NAME = logical_file_name, FILENAME = 'your-new-file-on-disk.mdf' )

2) detach the database

3) rename the files on disk

4) re-attach the database again

Answer 5

Try:

ALTER DATABASE <DBName> 
MODIFY FILE (NAME = '<logicalFile>', FILENAME = '<physicalname>') 

This will only alter SQL Server's internal definition of the filename, it will not change the actual name of the file in the OS file system.