SQL-Serverexport using SMO-Assemblies (Powershell) - How to make seperate files per Statement?

31 Views Asked by At

I'm using a powershell-script to export a database in the form of SQL-Statements using SMO-assemblies. My problem is that as is, for example all the create-statements for the whole database get put into one big .sql file. I would like to have one seperate file per create-statement though (same with views, etc.). Is there any databaseTransfer.Option that allows me to do that? Or can you think of any workarounds? I thought about taking the big file and make a cutoff each time the word "END" comes up, but that seems like a very unclean solution. So if you can think of any ways to split my big "create-statement file" into one file for each statement I would be very thankful

Right now I'm using these settings in a Powershell script

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

        #Exportoptions

        $databaseTransfer.Options.EnforceScriptingOptions = $true
        $databaseTransfer.Options.ToFileOnly = $true # Ausgabe nur in die Datei
        $databaseTransfer.Options.Indexes = $true
        $databaseTransfer.Options.DriAllKeys = $true
        $databaseTransfer.Options.IncludeIfNotExists = $true
        $databaseTransfer.Options.WithDependencies = $false 

        #Transferkonfiguration
        
        $databaseTransfer.CopyAllObjects = $false 

        #Schemas
        $databaseTransfer.CopyAllSchemas = $true
        $databaseTransfer.CopyAllTables = $false
        $databaseTransfer.CopyAllUserDefinedFunctions = $false
        $databaseTransfer.CopyAllStoredProcedures = $false
        $databaseTransfer.CopyAllViews = $false
        $databaseTransfer.Options.Filename = "$exportPath\$databaseName\CreateSchema.sql"
        $databaseTransfer.EnumScriptTransfer()
  
    
        #Tables
        $databaseTransfer.CopyAllSchemas = $false
        $databaseTransfer.CopyAllTables = $true
        $databaseTransfer.CopyAllUserDefinedFunctions = $false
        $databaseTransfer.CopyAllStoredProcedures = $false
        $databaseTransfer.CopyAllViews = $false
        $databaseTransfer.Options.Filename = "$exportPath\$databaseName\CreateTables.sql"
        $databaseTransfer.Options.NoCollation = $true;
        $databaseTransfer.EnumScriptTransfer()
    
        #UserDefinedFunctions
        $databaseTransfer.CopyAllSchemas = $false
        $databaseTransfer.CopyAllTables = $false
        $databaseTransfer.CopyAllUserDefinedFunctions = $true
        $databaseTransfer.CopyAllStoredProcedures = $false
        $databaseTransfer.CopyAllViews = $false
        $databaseTransfer.Options.Filename = "$exportPath\$databaseName\CreateUserDefinedFunctions.sql"
        $databaseTransfer.EnumScriptTransfer()

        #StoredProcedures
        $databaseTransfer.CopyAllSchemas = $false
        $databaseTransfer.CopyAllTables = $false
        $databaseTransfer.CopyAllUserDefinedFunctions = $false
        $databaseTransfer.CopyAllStoredProcedures = $true
        $databaseTransfer.CopyAllViews = $false
        $databaseTransfer.Options.Filename = "$exportPath\$databaseName\CreateStoredProcedures.sql"
        $databaseTransfer.EnumScriptTransfer()

        #Views
        $databaseTransfer.CopyAllSchemas = $false
        $databaseTransfer.CopyAllTables = $false
        $databaseTransfer.CopyAllUserDefinedFunctions = $false
        $databaseTransfer.CopyAllStoredProcedures = $false
        $databaseTransfer.CopyAllViews = $true
        $databaseTransfer.Options.Filename = "$exportPath\$databaseName\CreateViews.sql"
        $databaseTransfer.EnumScriptTransfer()

        #StoredProcedures
        $databaseTransfer.CopyAllSchemas = $false
        $databaseTransfer.CopyAllTables = $false
        $databaseTransfer.CopyAllUserDefinedFunctions = $false
        $databaseTransfer.CopyAllStoredProcedures = $false
        $databaseTransfer.CopyAllViews = $true
        $databaseTransfer.Options.Filename = "$exportPath\$databaseName\CreateViews.sql"
        $databaseTransfer.EnumScriptTransfer()

        #User
        $databaseTransfer.CopyAllSchemas = $false
        $databaseTransfer.CopyAllTables = $false
        $databaseTransfer.CopyAllUserDefinedFunctions = $false
        $databaseTransfer.CopyAllStoredProcedures = $false
        $databaseTransfer.CopyAllViews = $false
        $databaseTransfer.CopyAllUsers = $false
        $databaseTransfer.Options.Filename = "$exportPath\$databaseName\CreateDBUser.sql"
        $databaseTransfer.EnumScriptTransfer()

And the output looks like this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[com].[TableName1]') AND type in (N'U'))
BEGIN
CREATE TABLE [com].[TableName](
    [Code] [nvarchar](10) NOT NULL,
    [Description] [nvarchar](100) NULL,
    [SG_MandantCode] [nvarchar](5) NOT NULL,
 CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED 
(
    [Code] ASC,
    [SG_MandantCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[com].[TableName]') AND type in (N'U'))
BEGIN
CREATE TABLE [com].[TableName](
    [Code] [nvarchar](10) NOT NULL,
    [ISO Code] [nvarchar](3) NULL,
    [Description] [nvarchar](30) NULL,
    [Symbol] [nvarchar](10) NULL,
    [SG_MandantCode] [nvarchar](5) NOT NULL,
 CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED 
(
    [Code] ASC,
    [SG_MandantCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END

... and so forth. But when I made a script that cuts off each Table after "END" I ended up with less files than expected so that method doesn't seem to be the best.

0

There are 0 best solutions below