USE
master
GO
DECLARE @DBName sysname,
@DestPath varchar ( 256 )
DECLARE @DB table (
name sysname,
physical_name sysname)
BEGIN TRY
SELECT
@DBName = ' FF ' , -- input database name
@DestPath = ' E:/ ' -- 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
DECLARE @DBName sysname,
@DestPath varchar ( 256 )
DECLARE @DB table (
name sysname,
physical_name sysname)
BEGIN TRY
SELECT
@DBName = ' FF ' , -- input database name
@DestPath = ' E:/ ' -- 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