Database renaming series (database name, logical name, physical file name)

Summary article: Http://www.cnblogs.com/dunitian/p/4822808.html#tsql

The design of a system is not very reasonable, many libraries, graphical operation of the separation have to do half a day, a variety of renaming is also a waste of time, so the introduction of the command ~ (SQL Server is now running in Linux, we have to keep up with the Times)

1. Before the database name is modified

ALTER DATABASE Test modify Name=newtest or exec sp_renamedb ' test ', ' newtest '

2. After the database name has been modified

3. Physical file name and logical name do not change

4. Before and after the logical name modification

ALTER DATABASE newtest modify file (name=n ' Test ', newname=n ' NetTest ')

5. Logical name changes the physical file name does not change

6. Many kinds of physical renaming (the essence of my side is to change after separation, because the occupancy status is not modifiable)

In fact, there is no new SQL, is a combination version of the

exec xp_cmdshell ' rename E:\SQL\Test.mdf newtest.mdf '

Effect:

Sql:

Use mastergo--1. Detach exec sp_detach_db newtestgo--2. Rename (This step can be replaced by manually changing the name) exec sp_configure ' show advanced options ', 1-- Show advanced options reconfigure with override--reconfigure exec sp_configure ' xp_cmdshell ', 1--1 for Allow, 0 for block reconfigure with Overrideexec xp_ Cmdshell ' rename E:\SQL\Test.mdf newtest.mdf ' goexec xp_cmdshell ' rename E:\SQL\Test_log.ldf newtest_log.ldf ' goexec sp_ Configure ' xp_cmdshell ', 0reconfigure with overrideexec sp_configure ' show advanced options ', 0reconfigure with override--3. Additional exec sp_attach_db newtest,n ' E:\SQL\NewTest.mdf ', N ' E:\SQL\NewTest_log.ldf '

Database renaming series (database name, logical name, physical file name)