SQL Server Script – Automatically Generate Database Creation Script with Default Values for SIZE and FILEGROWTH

sql serversql server 2014

By using a mix of several PoSH scripts I managed to get a working script that generates, copies and remotely executes a script to create the basic structure for a given database with all its objects, logins, etc. But is using for SIZE and FILEGROWTH the values stated on the production environment (say 1GB and 512MB). The goal is to create the database on a dev environment for developers to play with it. So an initial default size and filegrowth will be more than enough. For SQL2016 those values are 8MB and 64MB respectively as stated here by MSSQL Tiger Team.

Is there a way to generate the script with those values instead of the ones configured for the database? Or should I search and replace the values on the script?

Best Answer

One method to modify SQL scripts is with the Microsoft.SqlServer.TransactSql.ScriptDom. Below is a PowerShell script example that changes the file sizes using a TSqlFragmentVisitor implemented in C# that you can extend for your needs. This same technique may be used to change database names, file paths, size, etc.

Note that if you are using the PowerShell ISE for script development, you'll need to close and re-open the PowerShell ISE after making changes to the C# code in order to reload the .NET app domain.

# C-Sharp TSqlFragmentVisitor visitor class for ExplicitVisit(SizeFileDeclarationOption node)
Add-Type @"
    using System;
    using Microsoft.SqlServer.TransactSql.ScriptDom;
    using System.Collections.Generic;

    public class CreateDatabaseVisitor : TSqlFragmentVisitor
    {

        public Action<CreateDatabaseVisitor, TSqlFragment> Visited;

        public override void ExplicitVisit(SizeFileDeclarationOption node)
        {
            onVisit(node);
        }

        public override void ExplicitVisit(FileGrowthFileDeclarationOption node)
        {
            onVisit(node);
        }

        void onVisit(TSqlFragment node)
        {
            if(Visited != null)
            {
                Visited(this, node);
                node.AcceptChildren(this);
            }
        }

    }
"@ -ReferencedAssemblies "C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.TransactSql.ScriptDom.dll"

# create new class instance
$visitor = New-Object CreateDatabaseVisitor

# callback method for each visit
$visitor.Visited = {
    param($obj, $node)
    # Write-host "Visited node type: $($node.GetType())"

    # change SizeFileDeclarationOption as desired
    if($node.GetType().Name -eq "SizeFileDeclarationOption") {
        $newSize = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.IntegerLiteral
        $newSize.Value = 100
        $node.Size = $newSize
        $Node.Units = [Microsoft.SqlServer.TransactSql.ScriptDom.MemoryUnit]::MB
    }

    if($node.GetType().Name -eq "FileGrowthFileDeclarationOption") {
        $newSize = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.IntegerLiteral
        $newSize.Value = 10
        $node.GrowthIncrement = $newSize
        $Node.Units = [Microsoft.SqlServer.TransactSql.ScriptDom.MemoryUnit]::MB
    }

}

$parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql120Parser($true)
$parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]
$originalScript = [System.IO.File]::ReadAllText("C:\temp\CreateDatabase.sql")
$scriptReader = New-Object System.IO.StringReader($originalScript)

$frament = $parser.Parse($scriptReader, [ref]$parseErrors)
if($parseErrors.Count -gt 0)
{
    throw "$($parseErrors.Count) parsing errors. First error is: $($parseErrors[0].Message)"
}

$frament.Accept($visitor)

Write-Host "Original Script:"
Write-Host $originalScript

#generate new script
$options = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.SqlScriptGeneratorOptions
$options.SqlVersion = [Microsoft.SqlServer.TransactSql.ScriptDom.SqlVersion]::Sql120
$options.IncludeSemicolons = $true
$generator = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.Sql120ScriptGenerator($options)
[String]$newScript = ""
$generator.GenerateScript($frament, [ref]$newScript)

Write-Host "Modified Script:"
Write-Host $newScript

Example output:

Original Script:
CREATE DATABASE [YourDatabase]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'YourDatabase', FILENAME = N'D:\SqlDataFiles\YourDatabase.mdf' , SIZE = 15360 , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB ), 
 FILEGROUP [YourDatabase]  DEFAULT
( NAME = N'Data', FILENAME = N'D:\SqlDataFiles\YourDatabase_Data.mdf' , SIZE = 78561280 KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB )
 LOG ON 
( NAME = N'YourDatabase_Log', FILENAME = N'D:\SqlLogFiles\YourDatabase.ldf' , SIZE = 4308416KB , MAXSIZE = 2048GB , FILEGROWTH = 10240KB )
GO

ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 120
GO

Modified Script:
CREATE DATABASE [YourDatabase] CONTAINMENT = NONE
    ON 
    PRIMARY(NAME = N'YourDatabase', FILENAME = N'D:\SqlDataFiles\YourDatabase.mdf', SIZE = 100 MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10 MB), 
    FILEGROUP [YourDatabase] DEFAULT(NAME = N'Data', FILENAME = N'D:\SqlDataFiles\YourDatabase_Data.mdf', SIZE = 100 MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10 MB)
    LOG ON (NAME = N'YourDatabase_Log', FILENAME = N'D:\SqlLogFiles\YourDatabase.ldf', SIZE = 100 MB, MAXSIZE = 2048 GB, FILEGROWTH = 10 MB);


GO
ALTER DATABASE [YourDatabase]
    SET COMPATIBILITY_LEVEL = 120;
Related Question