sqlserver数据库移动数据库路径的脚本示例

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 processes
DECLARE @SPID varchar(20)
DECLARE curProcess CURSOR FOR
SELECT spid
FROM sys.sysprocesses
WHERE DB_NAME(dbid) = @DBName
OPEN curProcess
 FETCH NEXT FROM curProcess INTO @SPID
 WHILE @@FETCH_STATUS = 0
 BEGIN
 EXEC('KILL ' + @SPID)
 FETCH NEXT FROM curProcess
 END
CLOSE curProcess
DEALLOCATE curProcess
-- query physical name
INSERT @DB(
 name,
 physical_name)
SELECT
 A.name,
 A.physical_name
FROM sys.master_files A
INNER JOIN sys.databases B
 ON A.database_id = B.database_id
 AND B.name = @DBName
WHERE A.type <=1
--set offline
EXEC('ALTER DATABASE ' + @DBName + ' SET OFFLINE')
--move to dest path
DECLARE
 @login_name sysname,
 @physical_name sysname,
 @temp_name varchar(256)
DECLARE curMove CURSOR FOR
SELECT
 name,
 physical_name
FROM @DB
OPEN 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
 END
CLOSE curMove
DEALLOCATE curMove
-- set online
EXEC('ALTER DATABASE ' + @DBName + ' SET ONLINE')
-- show result
SELECT
 A.name,
 A.physical_name
FROM sys.master_files A
INNER JOIN sys.databases B
 ON A.database_id = B.database_id
 AND B.name = @DBName
END TRY
BEGIN CATCH
 SELECT ERROR_MESSAGE() AS ErrorMessage
END CATCH
GO

以上就是sqlserver数据库移动数据库路径的脚本示例。大器不必晚成，趁着年轻，努力让自己的才能创造最大的价值。更多关于sqlserver数据库移动数据库路径的脚本示例请关注haodaima.com其它相关文章！