[回馈CSDN网友]贴个SQL小工具--移动数据库物理文件

USE masterGODECLARE@DBName sysname,@DestPath varchar(256)DECLARE@DB table( name sysname, physical_name sysname)BEGIN TRYSELECT@DBName = 'FF',--input database name@DestPath = 'E:\'--input destination path--kill database processesDECLARE@SPID varchar(20)DECLARE curProcessCURSOR FORSELECT spidFROM sys.sysprocessesWHEREDB_NAME(dbid)= @DBNameOPEN curProcessFETCH NEXT FROM curProcessINTO @SPID WHILE@@FETCH_STATUS =0 BEGINEXEC('KILL' +@SPID) FETCH NEXTFROM curProcessENDCLOSE curProcessDEALLOCATE curProcess--query physical nameINSERT@DB( name, physical_name)SELECT A.name, A.physical_nameFROM sys.master_files AINNERJOIN sys.databases BON A.database_id= B.database_idAND 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 curMoveCURSOR FORSELECT name, physical_nameFROM@DBOPEN curMoveFETCH NEXT FROM curMoveINTO @login_name,@physical_nameWHILE @@FETCH_STATUS =0 BEGINSET @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 curMoveINTO @login_name,@physical_nameENDCLOSE curMoveDEALLOCATE curMove--set onlineEXEC('ALTER DATABASE' +@DBName + ' SET ONLINE')--show resultSELECT A.name, A.physical_nameFROM sys.master_files AINNERJOIN sys.databases BON A.database_id= B.database_idAND B.name = @DBNameEND TRYBEGIN CATCH SELECT ERROR_MESSAGE()AS ErrorMessageEND CATCH