主页 / dba / 问题 / 214157
Accepted
Yaroslav
Asked: 2018-08-07 02:49:43 +0800 CST

使用 SIZE 和 FILEGROWTH 的默认值自动生成数据库创建脚本

  • 772

通过混合使用多个 PoSH 脚本，我设法获得了一个工作脚本，该脚本生成、复制和远程执行脚本，为给定数据库及其所有对象、登录名等创建基本结构。但是用于SIZE和FILEGROWTH生产环境中规定的值（比如 1GB 和 512MB）。目标是在开发环境中创建数据库供开发人员使用。因此，初始默认大小和文件增长将绰绰有余。对于 SQL2016，这些值分别为 8MB 和 64MB，如MSSQL Tiger Team此处所述。

有没有办法用这些值而不是为数据库配置的值生成脚本？还是我应该搜索并替换脚本中的值？

sql-server sql-server-2014

2 个回答

  1. Best Answer

    修改 SQL 脚本的一种方法是使用Microsoft.SqlServer.TransactSql.ScriptDom. 下面是一个 PowerShell 脚本示例，它使用在 C# 中实现的TSqlFragmentVisitor更改文件大小，您可以根据需要对其进行扩展。同样的技术可用于更改数据库名称、文件路径、大小等。

    请注意，如果您使用 PowerShell ISE 进行脚本开发，则需要在更改 C# 代码后关闭并重新打开 PowerShell ISE，以便重新加载 .NET 应用程序域。

    # 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

    示例输出：

    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;
    • 3

  2. 如果您的生产环境启用了自动增长，那么当前大小可能会随着时间的推移而变化，这可能会导致搜索/替换问题，因为您不知道要搜索什么。您可以尝试创建一个具有所需属性的虚拟数据库，并在脚本生成期间通过元数据连接选择这些属性。或者，您可以在脚本生成期间简单地对值进行硬编码。

    • 1

相关问题