Skip to content

Instantly share code, notes, and snippets.

@LitKnd
Last active March 26, 2019 22:54
Embed
What would you like to do?
IF NOT EXISTS (SELECT * FROM sys.filegroups WHERE name='USERDATA')BEGIN
PRINT N'Adding filegroup USERDATA to database';
ALTER DATABASE CURRENT ADD FILEGROUP USERDATA;
END;
GO
IF @@ERROR<>0 SET NOEXEC ON;
GO
IF NOT EXISTS (SELECT * FROM sys.database_files WHERE name='WWI_UserData')BEGIN
IF @@SERVERNAME='GATEBASE01\DEV01' BEGIN
DECLARE @fname nvarchar(1000) = N'', @dsql NVARCHAR(MAX) = N'';
SELECT @fname = N'BUILD_WideWorldImporters_SOC_CI_UserData_' + DB_NAME() + '.ndf';
SET @dsql = N'
ALTER DATABASE CURRENT
ADD FILE(NAME=WWI_UserData, FILENAME=''C:\MSSQL\Data\' + @fname + N''')
TO FILEGROUP USERDATA;'
PRINT N'Adding file WWI_UserData to filegroup USERDATA';
EXEC ( @dsql)
END;
END;
GO
IF @@ERROR<>0 SET NOEXEC ON;
GO
IF NOT EXISTS (SELECT * FROM sys.filegroups WHERE name='WWI_InMemory_Data')BEGIN
PRINT N'Adding filegroup WWI_InMemory_Data to database';
ALTER DATABASE CURRENT
ADD FILEGROUP WWI_InMemory_Data
CONTAINS MEMORY_OPTIMIZED_DATA;
END;
GO
IF @@ERROR<>0 SET NOEXEC ON;
GO
IF NOT EXISTS (SELECT * FROM sys.database_files WHERE name='WWI_InMemory_Data_1')BEGIN
IF @@SERVERNAME='GATEBASE01\DEV01' BEGIN
DECLARE @fname nvarchar(1000) = N'', @dsql NVARCHAR(MAX) = N'';
SELECT @fname = N'BUILD_WideWorldImporters_InMemory_Data_1_SOC_CI_' + DB_NAME();
SET @dsql = N'
ALTER DATABASE CURRENT
ADD FILE ( NAME=''WWI_InMemory_Data_1'', FILENAME=''C:\MSSQL\Data\' + @fname + ''')
TO FILEGROUP WWI_InMemory_Data;'
PRINT N'Adding file WWI_InMemory_Data_1 to filegroup WWI_InMemory_Data';
EXEC sp_executesql @dsql
END;
END;
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment