SQL查询与修改数据库逻辑文件名，移动数据库存储路径示例(二)

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 设置新的存储路径

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 设置数据库联机

ALTER DATABASE [mydb] SET ONLINE

GO

--3.2.6 查询新的路径

USE mydb

GO

SELECT physical_name FROM sys.database_files

/*

physical_name

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

E:\DBTEST\mydb.mdf

E:\DBTEST\mydb_log.ldf

(2 行受影响)

*/