SQL query and modify database logical file name, example of moving database storage path

Use MyDB

Go

--1. Querying 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 with the following statement:

--select name from Sys.database_files

/*

File name 1 file name 2

------------------- --------------------

MyDB Mydb_log

(1 rows affected)

*/

--2. Modifying the logical name of a 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 name after the change:

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 rows affected)

*/

--3. To move a data file or log file to a new location, first offline move the file to the appropriate location, and then online

--refer to the following steps in detail

--3.1 query data file or log file current storage path:

SELECT Physical_name from Sys.database_files

/*

Physical_name

-------------------------------------------------------------------------------------------

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 Move Path

Use [master]

GO

--3.2.1 Close All Processes

DECLARE @str VARCHAR (500);

SET @str = ';

SELECT @str = @str + ' KILL ' +rtrim (spid) + '; ' +char (+char) (10)

From master.dbo.sysprocesses

WHERE dbid=db_id (' mydb ');

EXEC (@str);

GO

--3.2.2 setting up the database offline

ALTER DATABASE [mydb] SET OFFLINE

GO

--3.2.3 mobile data files and log files (to ensure data security, it is recommended to change the move to copy, copy the successful and then delete the source file)

EXEC Master. xp_cmdshell ' move ' d:/program files/microsoft SQL server/mssql10. Mssqlserver/mssql/data/mydb.mdf "" E:/dbtest "', no_output

EXEC Master. xp_cmdshell ' move ' d:/program files/microsoft SQL server/mssql10. Mssqlserver/mssql/data/mydb_log. LDF "" E:/dbtest "', no_output

GO

--3.2.4 setting 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 setting up the database online

ALTER DATABASE [mydb] SET ONLINE

GO

--3.2.6 Querying for new paths

Use MyDB

GO

SELECT Physical_name from Sys.database_files

/*

Physical_name

------------------------------

E:/dbtest/mydb.mdf

E:/dbtest/mydb_log.ldf

(2 rows affected)

*/

SQL query and modify database logical file name, example of moving database storage path