SQL Server 9: SQL query and modification of database logical file names, mobile database storage path

Reference: http://blog.csdn.net/htl258/article/details/4106591,it is very useful.

Use mydb go -- 1. query the logical File Name of the current database: Select file_name (1) as 'file name 1', file_name (2) as 'file name 2'; -- or query by the following statement: -- Select name from sys. database_files/* file name 1 file name 2 ------------------- -------------------- mydb mydb_log (1 row affected) */-- 2. alter database [mydb] Modify file (name = mydb, newname = new_mydb) alter database [mydb] Modify file (name = mydb_log, newname = new_mydb_log) -- query the changed name: Select file_name (1) as 'file name 1', file_name (2) as 'file name 2 '; /* file name 1 file name 2 ------------------- -------------------- new_mydb new_mydb_log (1 row affected) */-- 3. to move the data file or log file to a new location, move the file to the corresponding location by offline first, and then online -- For details, refer to the following steps -- 3.1 query the current storage path of the data file or log file: Select physical_name from sys. database_files/* physical_name development D:/program files/Microsoft SQL Server/mssql10.mssqlserver/MSSQL/data/mydb. mdf d:/program files/Microsoft SQL Server/mssql10.mssqlserver/MSSQL/data/mydb_log.ldf (2 rows affected) */-- 3.2 mobile path use [Master] Go -- 3.2.1 disable all processes declare @ STR varchar (500); Set @ STR = ''; select @ STR = @ STR + 'Kill '+ rtrim (spid) +'; '+ char (13) + char (10) from master. DBO. sysprocesses where dbid = db_id ('mydb'); Exec (@ Str ); go -- 3.2.2 set offline database alter database [mydb] Set offline go -- 3.2.3 to move data files and log files. (To ensure data security, move to copy, copy the source file) exec master .. xp_mongoshell 'move "D:/program files/Microsoft SQL Server/mssql10.mssqlserver/MSSQL/data/mydb. MDF "" E:/dbtest "', no_output exec master .. xp_mongoshell 'move "D:/program files/Microsoft SQL Server/mssql10.mssqlserver/MSSQL/data/mydb_log.ldf" "E:/dbtest "', no_output go -- 3.2.4 set the new storage path alter database [mydb] Modify file (name = new_mydb, filename = 'e:/dbtest/mydb. MDF ') alter database [mydb] Modify file (name = new_mydb_log, filename = 'e:/dbtest/mydb_log.ldf ') go -- 3.2.5 set the database online alter database [mydb] Set Online go -- 3.2.6 to query the new path use mydb go select physical_name from sys. database_files/* physical_name ------------------------------ E:/dbtest/mydb. mdf e:/dbtest/mydb_log.ldf (2 rows affected )*/