How to Change The Logical File Names of SQL Server Databases
If you query the SysFiles by running the below sql select statement, you will get the result shown in the below table.
select * from sysfiles
| fileid | groupid | size | maxsize | growth | status | perf | name | filename |
| 1 | 1 | 106224 | -1 | 10 | 1048578 | 0 | ProductTest_Data | E:\data\Product.mdf |
| 2 | 0 | 128 | -1 | 10 | 1048642 | 0 | ProductTest_Log | E:\data\Product_log.ldf |
Here, you see the database file names as ProductTest_Data and ProductTest_Log
Since you may want to move this database on a production server, or change the database name because of any reason, you may also want to change the names of the database files. You may want the database names consistent with the database names or ant other rules you obey for standardization.
You can not change the database file name using the GUI (Graphical User
Interface) of the SQL Management Studio in SQL 2005 or the Enterprise Manager in
SQL 2000.
But you can use the ALTER DATABASE sql command as shown below :
ALTER DATABASE Product
MODIFY FILE (NAME = ProductTest_Data, NEWNAME = Product_Data)
This command returns the below message if it is successfull
The file name 'Product_Data' has been set.
ALTER DATABASE Product
MODIFY FILE (NAME = ProductTest_Log, NEWNAME = Product_Log)
This command returns the below message if it is successfull
The file name 'Product_Log' has been set.