Automatically generate database creation script with default values for SIZE and FILEGROWTH

Question

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?

Answer 1

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 Server120円\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;

• Dan Guzman: @Yaroslav, I should have mentioned that if you are using the PowerShell ISE, you need to close and re-open the PowerShell ISE after making changes to the C# code in order to reload the .NET app domain. I'll add that detail to my answer.

Answer 2

If your production environment has autogrow ON, then the current size might change over time which could cause search/replace problems since you would not know what to search on. You might try creating a dummy database with the attributes you desire and select those attributes via metadata join during script generation. Or, you could simply hardcode the values during script generation.