How to completely modify the name of a Database

 

/* How to modify the name of a database in the script example. the database is called db_beijing and needs to be modified to db_shanghainzperfect 2012.12.19 */-- to determine whether a database with the same name exists, so as to prevent accidental deletion of use mastergoif exists (Select name from sys. databases where name = n' dB _ Beijing ') Begin raiserror (' Note: The database already exists! ', 15,1) return -- drop database db_beijingendgouse mastergo -- create database [db_beijing] on primary (name = n'db _ Beijing', filename = n' X: \ data \ db_beijing.mdf ', size = 3072kb, filegrowth = 1024kb) log on (name = n' dB _ beijing_log', filename = n' W: \ log \ db_beijing_log.ldf ', size = 1024kb, filegrowth = 1024kb) go -- The following is the modification process -- Step 1: Modify the database name use mastergoalter database db_beijing set single_user with rollback immediategoexec master .. sp_renamedb 'db _ Beijing ', 'db _ Shanghai' goalter database db_shanghai set multi_user go -- Step 2: view the database logic name and physical file name after the modified name use mastergoselect name as [logical name], physical_name as [dB file path], type_desc as [file type], state_desc as [State] From sys. master_fileswhere database_id = db_id (n'db _ Shanghai') Go/* logical name dB file path file type statedb_beijing X: \ data \ db_beijing.mdf rows onlinedb_beijing_log W: \ log \ mongolog online */-- Step 3: Modify the name of the database logical file use mastergoalter database db_shanghai set single_user with rollback immediategoalter database db_shanghai Modify file (name = n'db _ Beijing ', newname = n' dB _ Shanghai') goalter database db_shanghai Modify file (name = n' dB _ beijing_log ', newname = n' dB _ shanghai_log ') goalter database db_shanghai set multi_user go -- Step 4: Open xp_mongoshell before modifying the database physical file name to support use mastergosp_configure 'show advanced options', 1 goreconfigure with overridegosp_configure 'xp _ mongoshell ', 1 goreconfigure with overridego -- Step 5: Rename the database physical file name use [Master] goalter database db_shanghai set offline with rollback immediategoexec xp_mongoshell 'rename "X: \ data \ db_beijing.mdf ", "db_shanghai.mdf" 'goexec xp_mongoshell 'rename "W: \ log \ db_beijing_log.ldf", "db_shanghai_log.ldf" 'Go -- Step 6: point the database logic name to a new physical file, and the database onlineuse [Master] goalter database db_shanghai Modify file (name = db_shanghai, filename = 'x: \ data \ db_shanghai.mdf ') goalter database db_shanghai Modify file (name = Taobao, filename = 'W: \ log \ db_shanghai_log.ldf ') goalter database db_shanghai set online -- Step 7: view the database status after all the modifications are completed. Use mastergoselect name as [logical name], physical_name as [dB file path], type_desc as [file type], state_desc as [State] From sys. master_fileswhere database_id = db_id (n'db _ Shanghai') Go/* logical name dB file path file type statedb_shanghai X: \ data \ db_shanghai.mdf rows onlinedb_shanghai_log W: \ log \ db_shanghai_log.ldf log online */-- step 8