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

Source: Internet
Author: User

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.