SQL Server Database Mobile database path script Example _mssql

Copy Code code as follows:

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