SQL Server database Mobile Database path script example

Copy codeThe Code is 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

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
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
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_mongoshell ''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
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