Can anyone tell me how to change the physical filename of the datafiles? There doesn't seem to be any documentation on this, yet its quite easy to change the logical filename.
There are several ways to make this change, however to rename the physical database files at operating system level you will have to take the database offline
1. Use SSMS to take the database Offline (right-click on Database, select Tasks, Take Offline), change the name of the files at the OS level and then Bring it Online.
2. You could Detach the database, rename the files and then Attach the database pointing to the renamed files to do so.
3. You could Backup the database and then restore, changing the file location during the restore process.
4. using T SQL
ALTER DATABASE databaseName SET OFFLINE
GO
ALTER DATABASE databaseNAme MODIFY FILE (NAME =db, FILENAME = 'C:\Program
Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\db.mdf')
GO
ALTER DATABASE databaseNAme MODIFY FILE (NAME = db_log, FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\db.ldf')
GO
ALTER DATABASE databaseName SET ONLINE
GO
for more info http://technet.microsoft.com/en-us/library/ms174269.aspx
- Edited by Chirag Shah Tuesday, February 02, 2010 4:20 PM typo
- Proposed as answer by LekssEditor Tuesday, February 02, 2010 4:27 PM
- Unproposed as answer by LekssEditor Tuesday, February 02, 2010 4:32 PM
- Proposed as answer by LekssEditor Tuesday, February 02, 2010 4:35 PM
- Marked as answer by Alex Feng (SQL)Moderator Tuesday, February 09, 2010 2:32 AM
Before you set the database online , you would have to manually change the filename at the OS level.
Failing on this part will not let you to start up the database.
Hello
I have using T SQL to rename on SQL Server 2008 R2 64bit as described in alternative 4 but I could not get it to work. Is there something else that I need to do first?
Thanks
Hello
I have using T SQL to rename on SQL Server 2008 R2 64bit as described in alternative 4 but I could not get it to work. Is there something else that I need to do first?
Where it says NAME=db and NAME=db_log these aren't the names of your database but the logical names of your files for the database.
Al