Modify and view the logical file name of the MSSQL database

What is the logical file name?

When you use a program to connect to the database, the database name is used. When you right-click the attribute of your database, click "file" in the upper left corner, the database name and logical file name are different. You can view the database files in the path of your database.

Example: SQL query and modify the logical file name of a database, and mobile database storage path example .

The code is as follows:

Copy code

---------------------------------------------------------------------------------- -- Author: htl258 (Tony) -- Date: 2010-06-26 21:51:30 -- Version: Microsoft SQL Server 2008 (RTM)-10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2) -- Blog: http://blog.111cn.net/htl258 (reprinted to retain this information) -- Subject: SQL queries and modifies the logical file name of a database. Example of a mobile database storage path ---------------------------------------------------------------------------------- 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 use the following statement to query: -- SELECT name FROM sys. database_files /* File Name 1 File Name 2 --------------------------------------- Mydb mydb_log   (One row is affected) */ -- 2. Modify the logical name of the data file or log file 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   (One row is affected) */   -- 3. If you want to MOVE the data file or log file to a new location, MOVE the file to the corresponding location 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 Bytes ------------------------------------------------------------------------------------------- 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 close 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 the database to go offline Alter database [mydb] SET OFFLINE GO -- 3.2.3 move the data file and log file (to ensure data security, we recommend that you move the file to copy and delete the source file after the copy operation is successful) 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 a 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 to be online Alter database [mydb] SET ONLINE GO -- 3.2.6 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) */