SQL Server 数据库移动数据库路径的脚本

这篇文章主要为大家详细介绍了SQL Server 数据库移动数据库路径的脚本，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

代码如下:

USE master
GO

DECLARE @DBName sysname, @DestPath varchar(256)DECLARE @DB table( name sysname, physical_name sysname)

BEGIN TRY

SELECT @DBName = 'TargetDatabaseName', --input database name @DestPath = 'D:\SqlData\' --input destination path

-- kill database processesDECLARE @SPID varchar(20)DECLARE curProcess CURSOR FOR

SELECT spidFROM sys.sysprocessesWHERE DB_NAME(dbid) = @DBName

OPEN curProcess FETCH NEXT FROM curProcess INTO @SPID WHILE @@FETCH_STATUS = 0 BEGIN EXEC('KILL ' + @SPID) FETCH NEXT FROM curProcess ENDCLOSE curProcessDEALLOCATE curProcess

-- query physical nameINSERT @DB( name, physical_name)SELECT A.name, A.physical_nameFROM sys.master_files AINNER JOIN sys.databases B ON A.database_id = B.database_id AND B.name = @DBNameWHERE A.type <=1

--set offlineEXEC('ALTER DATABASE ' + @DBName + ' SET OFFLINE')

--move to dest pathDECLARE @login_name sysname, @physical_name sysname, @temp_name varchar(256)DECLARE curMove CURSOR FORSELECT name, physical_nameFROM @DBOPEN curMove FETCH NEXT FROM curMove INTO @login_name,@physical_name WHILE @@FETCH_STATUS = 0 BEGIN SET @temp_name = RIGHT(@physical_name,CHARINDEX('\',REVERSE(@physical_name)) - 1) EXEC('exec xp_cmdshell ''move "' + @physical_name + '" "' + @DestPath + '"''') EXEC('ALTER DATABASE ' + @DBName + ' MODIFY FILE ( NAME = ' + @login_name + ', FILENAME = ''' + @DestPath + @temp_name + ''')') FETCH NEXT FROM curMove INTO @login_name,@physical_name ENDCLOSE curMoveDEALLOCATE curMove

-- set onlineEXEC('ALTER DATABASE ' + @DBName + ' SET ONLINE')

-- show resultSELECT A.name, A.physical_nameFROM sys.master_files AINNER JOIN sys.databases B ON A.database_id = B.database_id AND B.name = @DBNameEND TRYBEGIN CATCH SELECT ERROR_MESSAGE() AS ErrorMessageEND CATCHGO

注：关于SQL Server 数据库移动数据库路径的脚本的内容就先介绍到这里，更多相关文章的可以留意