Skip to content

Instantly share code, notes, and snippets.

@Oceanswave
Last active May 24, 2021 15:00
Show Gist options
  • Save Oceanswave/2119b07c616d9c4bdd535216d1cabdd2 to your computer and use it in GitHub Desktop.
Save Oceanswave/2119b07c616d9c4bdd535216d1cabdd2 to your computer and use it in GitHub Desktop.
Move SQL Files
--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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment