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