Move SQL Files

move.sql

--config variables
DECLARE @logpath NVARCHAR(260) = 'C:\SitecoreDBs\logs\'
DECLARE @datapath NVARCHAR(260) = 'C:\SitecoreDBs\data\'
DECLARE @movelogs BIT = 1
DECLARE @movedata BIT = 1
  
--runtime variables
DECLARE @STMT NVARCHAR(4000)
  
--uncomment the predicates to include and exclude databases as required
DECLARE Files CURSOR STATIC FORWARD_ONLY
FOR
SELECT DB_NAME(database_id)
    ,type
    ,name
    ,REVERSE(SUBSTRING(REVERSE(physical_name), 0, CHARINDEX('\', REVERSE(physical_name))))
FROM sys.master_files
WHERE type IN (0,1)
--AND DB_NAME(database_id) IN ('sqlundercover')                         --uncomment to include databases
AND DB_NAME(database_id) NOT IN ('master','tempdb','msdb','model')
  
DECLARE @DBName SYSNAME
DECLARE @type TINYINT
DECLARE @logicalname SYSNAME
DECLARE @physicalname NVARCHAR(260)
  
--check filepaths finish with a \ and add if they don't
IF (SUBSTRING(@datapath, LEN(@datapath), 1) != '\')
    SET @datapath += N'\'
  
IF (SUBSTRING(@logpath, LEN(@logpath), 1) != '\')
    SET @logpath += N'\'

OPEN Files

FETCH NEXT
FROM Files
INTO @DBName
    ,@type
    ,@logicalname
    ,@physicalname
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @STMT = N'ALTER DATABASE ' + QUOTENAME(@DBName) + N' SET OFFLINE WITH ROLLBACK IMMEDIATE'

	IF @type = 0
	  BEGIN
		PRINT @STMT
	  END
  
    FETCH NEXT
    FROM Files
    INTO @DBName
        ,@type
        ,@logicalname
        ,@physicalname
END

CLOSE Files

OPEN Files

FETCH NEXT
FROM Files
INTO @DBName
    ,@type
    ,@logicalname
    ,@physicalname
  
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @STMT = N'ALTER DATABASE ' + QUOTENAME(@DBName) + N' MODIFY FILE (NAME = ' +  QUOTENAME(@logicalname) + N', FILENAME = '''
    SET @STMT += CASE
            WHEN @type = 0 AND @movedata = 1
                THEN @datapath + @physicalname + ''')'
            WHEN @type = 1 AND @movelogs = 1
                THEN @logpath + @physicalname + ''')'
            END
	PRINT @STMT
  
    FETCH NEXT
    FROM Files
    INTO @DBName
        ,@type
        ,@logicalname
        ,@physicalname
END
  
CLOSE Files

OPEN Files

FETCH NEXT
FROM Files
INTO @DBName
    ,@type
    ,@logicalname
    ,@physicalname
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @STMT = N'ALTER DATABASE ' + QUOTENAME(@DBName) + N' SET ONLINE'

	IF @type = 0
	  BEGIN
		PRINT @STMT
	  END
  
    FETCH NEXT
    FROM Files
    INTO @DBName
        ,@type
        ,@logicalname
        ,@physicalname
END

CLOSE Files
  
DEALLOCATE Files