How to rename physical database files

Question

I used tsql to detach the database as follows:

EXEC sp_detach_db @dbname = 'my_db' 

Then I used PHP to rename the physical files. I managed to rename the mdf file, but not the ldf file! I even tried the dos REN command, but that didn't work for the ldf file either!

I wanted to ask if there is anything special in the physical log files that allow it not to be renamed?

Is there a better way to do this?

Thanks everyone

Answer 1

Detach the database, rename the files, insert it again.

Answer 2

You can do this with the ALTER DATABASE statement - like this:

 ALTER DATABASE (your database) MODIFY FILE ( NAME = logical_file_name, FILENAME = ' new_path/os_file_name ' ) 

You need to modify each file separately, for example. if you have several data files, you need to modify each of them.

See the Technical Documentation on this subject for more details.

Answer 3

The "ALTER DATABASE (your database) MODIFY FILE" command will only rename the logical names. This post shows how to use xp_cmdshell to rename physical files: http://www.mssqltips.com/sqlservertip/1891/best-practice-for-renaming-a-sql-server-database/

Please note the following:
1. xp_cmdshell will run as a user who is running SQL Server, and may not have the file system permissions required to rename database files
2. For security reasons, be sure to disable xp_xmdshell

The following is an example of how you can rename based on the mentioned blog post. It will replace the MyDB database with the NewMyDB database. The original MyDB (renamed MyDB_OLD) will be left disconnected.

 -- Enable xp_cmdshell: sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE GO sp_configure 'xp_cmdshell', 1 RECONFIGURE WITH OVERRIDE GO -- Get physical file names: declare @MyDBOriginalFileName nvarchar(300) = (select physical_name FROM sys.master_files where name = 'MyDB') declare @MyDBLogOriginalFileName nvarchar(300) = (select physical_name FROM sys.master_files where name = 'MyDB_log') declare @NewMyDBOriginalFileName nvarchar(300) = (select physical_name FROM sys.master_files where name = 'NewMyDB') declare @NewMyDBLogOriginalFileName nvarchar(300) = (select physical_name FROM sys.master_files where name = 'NewMyDB_log') declare @Command nvarchar(500) declare @Sql nvarchar(2000) IF (EXISTS (select * from sys.databases where name = 'NewMyDB') AND EXISTS (select * from sys.databases where name = 'MyDB')) BEGIN USE master ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE ALTER DATABASE NewMyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE -- Set new database name ALTER DATABASE MyDB MODIFY NAME = MyDB_OLD ALTER DATABASE NewMyDB MODIFY NAME = MyDB -- Update logical names ALTER DATABASE MyDB_OLD MODIFY FILE (NAME=N'MyDB', NEWNAME=N'MyDB_OLD') ALTER DATABASE [MyDB] MODIFY FILE (NAME=N'NewMyDB', NEWNAME=N'MyDB') EXEC master.dbo.sp_detach_db @dbname = N'MyDB_Old' EXEC master.dbo.sp_detach_db @dbname = N'MyDB' -- Rename physical files SET @Command = 'RENAME "' + @MyDBOriginalFileName + '" "MyDB_OLD.mdf"'; PRINT @Command EXEC xp_cmdshell @Command SET @Command = 'RENAME "' + @MyDBLogOriginalFileName + '" "MyDB_OLD_log.mdf"'; PRINT @Command EXEC xp_cmdshell @Command SET @Command = 'RENAME "' + @NewMyDBOriginalFileName + '" "MyDB.mdf"'; PRINT @Command EXEC xp_cmdshell @Command SET @Command = 'RENAME "' + @NewMyDBLogOriginalFileName + '" "MyDB_log.mdf"'; PRINT @Command EXEC xp_cmdshell @Command -- Attach with new file names declare @NewMyDBFileNameAfterRename nvarchar(300) = replace(@NewMyDBOriginalFileName, 'NewMyDB', 'MyDB') declare @NewMyDBLogFileNameAfterRename nvarchar(300) = replace(@NewMyDBOriginalFileName, 'NewMyDB_log', 'MyDB_log') SET @Sql = 'CREATE DATABASE MyDB ON ( FILENAME = ''' + @NewMyDBFileNameAfterRename + '''), ( FILENAME = ''' + @NewMyDBLogFileNameAfterRename + ''') FOR ATTACH' PRINT @Sql EXEC (@Sql) ALTER DATABASE MyDB SET MULTI_USER END -- Disable xp_cmdshell for security reasons: GO sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE GO sp_configure 'xp_cmdshell', 0 RECONFIGURE WITH OVERRIDE GO 

Answer 4

The easiest way to rename SQL server physical database files :

• Open and connect to the SQL server where the database you want to rename is located.
• Run the following script in the query window to change the physical and logical names. Remember to replace all " OldDatabaseName " with the new database name (" NewDatabaseName ") for which you want to change your name. Replace all NewDatabaseName new name that you want to set for your database.

use OldDatabaseName

 ALTER DATABASE OldDabaseName MODIFY FILE (NAME='OldDatabaseName', FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NewDatabaseName.mdf'); ALTER DATABASE OldDatabaseName MODIFY FILE (NAME='OldDatabaseName_log', FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NewDatabaseName_log.ldf'); ALTER DATABASE OldDatabaseName MODIFY FILE (NAME = OldDatabaseName, NEWNAME = NewDatabaseName); ALTER DATABASE OldDatabaseName MODIFY FILE (NAME = OldDatabaseName_log, NEWNAME = NewDatabaseName_log); 

3. And then right-click on OldDatabaseName , select Tasks , and then select Take Offline

4. Go to the location ( C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\... ), where the physical files are, and rename them to NewDatabaseName , indicated in number 2. Remember to check the absolute path of these files to be used on your computer.
5. Return to Microsoft SQL Server Management Studio . Right-click on OldDatabaseName , select Tasks , and then select Bring Online .
6. Finally, go to OldDatabaseName and rename it NewDatabaseName . You are done :-)