New-HYDConfigMgrDatabase.sql

SET NOCOUNT ON;
GO
USE [master];
GO
/************************************************************************************************************************
    REQUIRED VARIABLES TO UPDATE:
    Make sure to update the following variables!
************************************************************************************************************************/
DECLARE  @CMSiteCode  nchar(3) = N'PS1' -- This is the Site Code you are going to use for this CM Site.
        ,@ExecuteCmds bit = 1; -- 0 = Testing; Change this to 1 in order to actually create the database!

/************************************************************************************************************************
    OPTIONAL VARIABLES:
    These variables are optional. You can use these to customize how you want your database created. If you don't update
    these then the default values will be used in the script.
    Variable Explanations:
    @NumTotalDataFiles   : This is the total number of data files you want to create for the database. 
                           The default is to create a total of 4 data files.
    @SecondDataDrive     : Updating this to a drive letter (such as "O") will tell the script to create half of the files on the default
                           drive and the other half of the files on the drive specified. If the default data file location is
                           the same drive as what is specified then all files will be created on the same disk.
                           The default is to create all files on the same disk.
    @InitialDataFileSize : This is where you control how large to create the data files initially.
                           This is the size of each data file, therefore, the total size of the db will be: (@InitialDataFileSize * @NumTotalDataFiles)+@InitialLogFileSize
                           Example of specifying a size: N'123456MB' (can use KB, MB, or GB).
                           The default is to create each data file 2.5 GB in size. (A total data size of 10 GB if using the default file numbers)
    @InitialLogFileSize  : This is where you control how large of a log file to create initially.
                           Example of specifying a size: N'123456MB' (can use KB, MB, or GB).
                           The default is to create the log file at 5 GB in size.
    @PriDataFileGrowth   : This is where you control how to grow your data files when they need more space. The default growth size
                           in SQL Server is awful so we're going to change it to something better. I'm not allowing percentage growths!
                           Example of specifying a size: N'123456MB' (can use KB, MB, or GB).
                           The default is to grow the data files by 1 GB.
    @LogFileGrowth       : This is where you control how to grow your log file when it needs more space. The default growth size
                           in SQL Server is awful so we're going to change it to something better. I'm not allowing percentage growths!
                           Example of specifying a size: N'123456MB' (can use KB, MB, or GB).
                           The default is to grow the data files by 1 GB.
************************************************************************************************************************/
DECLARE  @NumTotalDataFiles   tinyint = 4
        ,@SecondDataDrive     nchar(1) = N''
        ,@InitialDataFileSize nvarchar(50) = N'10240MB'
        ,@InitialLogFileSize  nvarchar(50) = N'1024MB'
        ,@PriDataFileGrowth   nvarchar(50) = N'1024MB'
        ,@LogFileGrowth       nvarchar(50) = N'1024MB';


/************************************************************************************************************************
    INTERNAL VARIABLES:
    These are NOT to be changed or updated!
************************************************************************************************************************/
DECLARE  @DefLog       nvarchar(512)
        ,@DefMdf       nvarchar(512)
        ,@SecNdf       nvarchar(512)
        ,@CreateDB     nvarchar(max)
        ,@AddtlFiles   nvarchar(max) = N''
        ,@LogScript    nvarchar(max)
        ,@AddtlFileNum tinyint = 1
        ,@TwoDrives    bit = 1
        ,@Mdfi         tinyint
        ,@Ldfi         tinyint
        ,@Arg          nvarchar(10)
        ,@MdlDtaFlSze  int
        ,@MdlLogFlSze  int;

/************************************************************************************************************************
    Make sure the Site Code is correct before continuing.
************************************************************************************************************************/
IF ISNULL(@CMSiteCode, N'') = N'' OR LEN(@CMSiteCode) != 3
GOTO IncorrectInputParameters;

/************************************************************************************************************************
    Make sure the database doesn't already exist before continuing.
************************************************************************************************************************/
SET @CMSiteCode = UPPER(@CMSiteCode);
IF DATABASEPROPERTYEX(N'CM_'+@CMSiteCode, 'status') IS NOT NULL
GOTO DBExists;

/************************************************************************************************************************
    Initialize/Check Working Variables
************************************************************************************************************************/
-- Remove any spaces from the size and growth definitions:
SET @PriDataFileGrowth = UPPER(REPLACE(@PriDataFileGrowth,N' ',N''));
SET @LogFileGrowth = UPPER(REPLACE(@LogFileGrowth,N' ',N''));
SET @InitialDataFileSize = UPPER(REPLACE(@InitialDataFileSize,N' ',N''));
SET @InitialLogFileSize = UPPER(REPLACE(@InitialLogFileSize,N' ',N''));
SET @SecondDataDrive = UPPER(@SecondDataDrive);

-- Ensure the initial file sizes aren't smaller than the model database size; if so update the size:
SELECT  @MdlDtaFlSze = size*8 FROM model.sys.database_files WHERE type = 0;
SELECT  @MdlLogFlSze = size*8 FROM model.sys.database_files WHERE type = 1;
IF CAST(LEFT(@InitialDataFileSize,LEN(@InitialDataFileSize)-2) AS int) < @MdlDtaFlSze
BEGIN
    PRINT N'The initial data file size is smaller than the model database; in order to proceed update the size to be the same as model.';
    SET @InitialDataFileSize = CAST(@MdlDtaFlSze AS nvarchar(48))+N'KB';
    PRINT N'@InitialDataFileSize has been set to: '+@InitialDataFileSize;
    PRINT N'';
END;

IF (CAST(LEFT(@InitialLogFileSize,LEN(@InitialLogFileSize)-2) AS int)) < @MdlLogFlSze
BEGIN
    PRINT N'The initial log file size is smaller than the model database; in order to proceed update the size to be the same as model.';
    SET @InitialLogFileSize = CAST(@MdlLogFlSze AS nvarchar(48))+N'KB';
    PRINT N'@InitialLogFileSize has been set to: '+@InitialLogFileSize;
    PRINT N'';
END;

-- Get the Default MDF location (from the registry):
PRINT N'Getting the default location for data files from the registry.';
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @DefMdf OUTPUT, 'no_output';
IF @DefMdf IS NULL -- if we couldn't get the key from this location for some reason then look at the startup parameters:
BEGIN
    SET @Mdfi = 0;
    WHILE @Mdfi < 100
    BEGIN
        SELECT @Arg = N'SQLArg' + CAST(@Mdfi AS nvarchar(4));
        EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', @Arg, @DefMdf OUTPUT, 'no_output';
        IF LOWER(LEFT(REVERSE(@DefMdf),10)) = N'fdm.retsam'
        BEGIN
            -- If we found the parameter for the master data file then set the variable and stop processing this loop:
            SELECT @DefMdf = SUBSTRING(@DefMdf,3,CHARINDEX(N'\master.mdf',@DefMdf)-3);
            BREAK;
        END;
        ELSE
        SET @DefMdf = NULL;

        SELECT @Mdfi += 1;
    END;
END;
PRINT N'Default Data File location found: '+@DefMdf;
PRINT N'';

-- Get the Default LDF location (from the registry):
PRINT N'Getting the default location for log files from the registry.';
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @DefLog OUTPUT, 'no_output';
IF @DefLog IS NULL -- if we couldn't get the key from this location for some reason then look at the startup parameters:
BEGIN
    SET @Ldfi = 0;
    WHILE @Ldfi < 100
    BEGIN
        SELECT @Arg = N'SQLArg' + CAST(@Ldfi AS nvarchar(4));
        EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', @Arg, @DefLog OUTPUT, 'no_output';
        IF LOWER(LEFT(REVERSE(@DefLog),11)) = N'fdl.goltsam'
        BEGIN
            -- If we found the parameter for the master log file then set the variable and stop processing this loop:
            SELECT @DefLog = SUBSTRING(@DefLog,3,CHARINDEX(N'\mastlog.ldf',@DefLog)-3);
            BREAK;
        END;
        ELSE
        SET @DefLog = NULL;

        SELECT @Ldfi += 1;
    END;
END;
PRINT N'Default Log File location found: '+@DefLog;
PRINT N'';

-- Determine whether data files will be stored on two drives or on the same drive:
IF (ISNULL(@SecondDataDrive, N'') = N'') OR (LEFT(@DefMdf,1) = @SecondDataDrive)
BEGIN
    SET @TwoDrives = 0;
    PRINT N'All data files will be stored on one drive.';
    PRINT N'If you intended for the files to be on different drives you can move them at a later time.';
    PRINT N'  -- to do so see this article: http://msdn.microsoft.com/en-us/library/ms345483.aspx';
    PRINT N'';
END;
ELSE
BEGIN
    SET @SecNdf = @SecondDataDrive + SUBSTRING(@DefMdf,2,LEN(@DefMdf)-1);
    PRINT N'Secondary Data File Location set to: '+@SecNdf;
    PRINT N'';
END;

/************************************************************************************************************************
    Ensure the Secondary data file location exists; if not create it:
************************************************************************************************************************/
IF @TwoDrives = 1
BEGIN
    DECLARE  @FileExists TABLE ( isFile       int NOT NULL
                                ,isDirectory  int NOT NULL
                                ,ParentExists int NOT NULL
                                );
    INSERT @FileExists
    EXECUTE master..xp_fileexist @SecNdf;

    IF (SELECT isDirectory FROM @FileExists) = 0
    BEGIN
        PRINT N'The directory "'+@SecNdf+N'" does not exist.';
        IF @ExecuteCmds = 1 PRINT N'We will create this directory before proceeding...';
        IF @ExecuteCmds = 1 EXECUTE master..xp_create_subdir @SecNdf;
        IF @ExecuteCmds = 1 PRINT N'...directory has been created.';
        PRINT N'';
    END
END;

/************************************************************************************************************************
    Create the statement that will create the database with all user input and defaults.
************************************************************************************************************************/
-- First, create the 'additional' data files portion of the statement:
WHILE @AddtlFileNum < @NumTotalDataFiles
BEGIN
IF @TwoDrives = 0
-- If we are storing all the files on the same drive then we only need to use this logic:
SELECT @AddtlFiles += N'              ,( NAME = N''CM_'+@CMSiteCode+N'_'+CAST(@AddtlFileNum AS nvarchar(3))+N'''
                ,FILENAME = N'''+@DefMdf+N'\CM_'+@CMSiteCode+N'_'+CAST(@AddtlFileNum AS nvarchar(3))+N'.ndf''
                ,SIZE = '+@InitialDataFileSize+N'
                ,FILEGROWTH = '+@PriDataFileGrowth+N'
                )
';
ELSE -- else, we'll be storing data files in two locations
    BEGIN
    IF @AddtlFileNum % 2 = 1
    -- The "odd" number data files will be stored on the second drive:
    SELECT @AddtlFiles += N'              ,( NAME = N''CM_'+@CMSiteCode+N'_'+CAST(@AddtlFileNum AS nvarchar(3))+N'''
                    ,FILENAME = N'''+@SecNdf+N'\CM_'+@CMSiteCode+N'_'+CAST(@AddtlFileNum AS nvarchar(3))+N'.ndf''
                    ,SIZE = '+@InitialDataFileSize+N'
                    ,FILEGROWTH = '+@PriDataFileGrowth+N'
                    )
    ';
    ELSE
    -- The even numbered files will be stored on the default drive:
    SELECT @AddtlFiles += N'              ,( NAME = N''CM_'+@CMSiteCode+N'_'+CAST(@AddtlFileNum AS nvarchar(3))+N'''
                    ,FILENAME = N'''+@DefMdf+N'\CM_'+@CMSiteCode+N'_'+CAST(@AddtlFileNum AS nvarchar(3))+N'.ndf''
                    ,SIZE = '+@InitialDataFileSize+N'
                    ,FILEGROWTH = '+@PriDataFileGrowth+N'
                    )
    ';
    END;
SELECT @AddtlFileNum += 1;
END;

-- Second, create the log file portion of the statement:
SET @LogScript = N'LOG ON ( NAME = N''CM_'+@CMSiteCode+N'_Log''
        ,FILENAME = N'''+@DefLog+N'\CM_'+@CMSiteCode+N'_Log.LDF''
        ,SIZE = '+@InitialLogFileSize+N'
        ,FILEGROWTH = '+@LogFileGrowth+N'
        );';

-- Third, create the beginning portion of the statement:
SET @CreateDB = N'CREATE DATABASE [CM_'+@CMSiteCode+N']
    ON PRIMARY ( NAME = N''CM_'+@CMSiteCode+N'''
                ,FILENAME = N'''+@DefMdf+N'\CM_'+@CMSiteCode+N'.mdf''
                ,SIZE = '+@InitialDataFileSize+N'
                ,FILEGROWTH = '+@PriDataFileGrowth+N'
                )
';

-- Finally, put all the statements together in one final statement:
SELECT @CreateDB += @AddtlFiles;
SELECT @CreateDB += @LogScript;

/************************************************************************************************************************
    Create the database using the statement built:
************************************************************************************************************************/
IF @ExecuteCmds = 1
BEGIN
    BEGIN TRY
        PRINT N'Attempting to create the database specified. This is the command we are running:';
        PRINT N'';
        PRINT @CreateDB;
        PRINT N'';
        EXECUTE sp_executesql @CreateDB;
        EXECUTE (N'ALTER AUTHORIZATION ON DATABASE::[CM_'+@CMSiteCode+N'] TO sa;');
        PRINT N'Database creation successful!';
        PRINT N'Check the database properties to see what files were created and where they were created.';
        PRINT N'If you want to move them to another location you can do so now.';
        PRINT N'';
        GOTO EndScript;
    END TRY
    BEGIN CATCH
        PRINT N'Error Creating Database';
        PRINT GETDATE();
        PRINT N'Error Message: '+ERROR_MESSAGE();
        PRINT N'Error Severity: '+CONVERT(nvarchar(25),ERROR_SEVERITY());
        PRINT N'Error State: '+CONVERT(nvarchar(25),ERROR_STATE());
        PRINT N'';
        GOTO ExitError;
    END CATCH;
END;
ELSE
BEGIN
    PRINT N'The variable "@ExecuteCmds" is not set to 1, therefore, we didn''t execute any actual commands.';
    PRINT N'The code below is what would''ve been run, or you can copy/paste it into a new window and execute it to create your database:';
    PRINT N'';
    PRINT @CreateDB;
    PRINT N'';
    PRINT N'';
    GOTO EndScript;
END;

/************************************************************************************************************************
    Exits and End of Script:
************************************************************************************************************************/
IncorrectInputParameters:
IF ISNULL(@CMSiteCode,N'') = N''
BEGIN
    PRINT N'The required input variable "@CMSiteCode" was not updated.';
    PRINT N'Update the variable to the three character Site Code you will use for your ConfigMgr site.';
    PRINT N'';
END;
ELSE
BEGIN
    PRINT N'The required input variable "@CMSiteCode" is not accurate.';
    PRINT N'Update the variable to the three character Site Code you will use for your ConfigMgr site.';
    PRINT N'';
END;
GOTO EndScript;

DBExists:
PRINT N'Database '+QUOTENAME(N'CM_'+@CMSiteCode)+N' already exists! Cannot create a database of the same name on the same instance!!';
GOTO EndScript;

ExitError:
PRINT N'Error returned; see previous messages for details. Ending script.';
GOTO EndScript;

EndScript:
PRINT N'End of Script.';
GO