复制代码 代码如下: 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\
复制代码 代码如下:USE masterGO
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
