I have used tsql to detach a database like this:
EXEC sp_detach_db @dbname = 'my_db'
I then made use of PHP to rename the physical files. I was able to rename the mdf file but not the ldf file! I even tried a dos command REN
but that didn't work for the ldf file either!
I wanted to ask, is there something special about the physical log files that allow it not to be renamed?
Is there a better way of doing this?
Thanks all
7条答案
按热度按时间ecbunoof1#
Detach the Database, Rename the files, Attach it again.
vnzz0bqm2#
ippsafx73#
The "ALTER DATABASE (your database) MODIFY FILE" command will only rename the logical names. This post shows how to use xp_cmdshell to also rename the physical files: http://www.mssqltips.com/sqlservertip/1891/best-practice-for-renaming-a-sql-server-database/
Please note the following:
The following is an example of how the renaming can be done based on the mentioned blog post. It will replace the database MyDB with the database NewMyDB. The original MyDB (renamed to MyDB_OLD) will be left detached.
dsf9zpds4#
You can do it using an
ALTER DATABASE
statement - like this:You need to modify each file separately, e.g. if you have multiple data files, you need to modify each of those.
For details, see the Technet documentation on this topic .
jtw3ybtb5#
The simplest way to rename
SQL server physical database files
is:SQL server
where the database you wanted to rename is located.OldDatabaseName
" with the new name of the database ("NewDatabaseName
") you want to change its name to. Replace allNewDatabaseName
with the new name you want to set for your databaseuse OldDatabaseName
OldDatabaseName
, selectTasks
and then chooseTake Offline
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\...
) where the physical files are located and rename them to theNewDatabaseName
you specified in number 2. Remember to check the absolute path of these files to be used on your computer.Microsoft SQL Server Management Studio
. Right click on theOldDatabaseName
, selectTasks
and then chooseBring Online
.OldDatabaseName
to theNewDatabaseName
. You are done :-)6yjfywim6#
gpfsuwkq7#
I came across this old thread, and none of the answers quite did what I wanted, and I overlooked Sverre's for some reason. I put this together since I needed to clean up a lot of horrible inconsistent lower-env SQL server database file placement and consolidate disk/folder structures. Hope this helps someone.
Please note that the same CAVEAT applies, I didn't use XP_CMDSHELL because permissions on our filesystems are NUTS!! So, I just generated MOVE commands to be run as a step to this work. Sadly a requirement for my environment until we get a handle on this inherited environment.