SQL Server Database Mobile database path script Example

       I did one of those things before, move the file to D on all databases (except SQL Server system files) that were originally on C, for easy follow-up management and reduced disk I/O blocking (c, D is 2 standalone disks). Script needs to enter 2 parameters: Target database name and target directory

  Code as follows: Use master go   DECLARE     @DBName sysname,     @DestPath varchar (256) DECLARE @DB Table (    name sysname,     physical_name sysname)     BEGIN TRY   SELECT   &NBS P @DBName = ' TargetDatabaseName ',  --input database name     @DestPath = ' d:sqldata '       &N Bsp --input Destination Path    --Kill database processes DECLARE @SPID varchar (a) DECLARE curprocess for   SELECT spid from sys.sysprocesses WHERE db_name (dbid) = @DBName   OPEN curprocess     FETCH NEXT FRO M curprocess into @SPID     while @ @FETCH_STATUS = 0     BEGIN           &NBS P EXEC (' KILL ' + @SPID)             FETCH NEXT from curprocess     Curproc ESS deallocate curprocess  --Query physical name INSERT @DB (    name,     physical_name) Selec T   &nbsP 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 Databa SE ' + @DBName + ' SET OFFLINE ')  --move to dest path DECLARE     @login_name sysname,     @phys Ical_name sysname,     @temp_name varchar (256) DECLARE Curmove CURSOR for SELECT     name,   &NBS P Physical_name from @DB OPEN curmove     FETCH NEXT from Curmove into @login_name, @physical_name     &N Bsp   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 DA Tabase ' + @DBname + ' MODIFY FILE (NAME = ' + @login_name                     + ', FILENAME = "+ @DestPath + @temp_name + '")             FETCH NEXT from Curmove into @l Ogin_name, @physical_name         End close Curmove deallocate curmove  --Set online EXEC (' ALTER DATABASE ' + @DBName + ' SET ONLINE ')  --show result SELECT     A.name,     A.physical_name FR OM sys.master_files A INNER JOIN sys.databases B     on a.database_id = b.database_id       &NBSP ; and b.name = @DBName End TRY-BEGIN CATCH     SELECT error_message () as ErrorMessage end CATCH go