ScriptingOptions sql smo does not support scripting data

Question

I'm generating sql database script using c# code.

following code works fine for create table but when I try to use scriptOptions.ScriptData = true; it is throwing following exception.

An unhandled exception of type 'Microsoft.SqlServer.Management.Smo.FailedOperationException' occurred in Microsoft.SqlServer.Smo.dll

Additional information: This method does not support scripting data.

code

 public static string ScriptDatabase(string dbConnectionString, string databaseName)
        {

            SqlConnection conn = new SqlConnection(dbConnectionString);
            ServerConnection serverConn = new ServerConnection(conn);
            var server = new Server(serverConn);
            var database = server.Databases[databaseName];

            var scripter = new Scripter(server);
           // I tried this code also       
           // scripter.Options.ScriptData = true;
            ScriptingOptions scriptOptions = new ScriptingOptions();
            scriptOptions.ScriptDrops = false;
            scriptOptions.ScriptData = true;
            scriptOptions.ScriptSchema = true;


            scriptOptions.IncludeIfNotExists = true;
            string scrs = "";
            string tbScr = "";
            foreach (Table myTable in database.Tables)
            {
                /* Generating IF EXISTS and DROP command for tables */
                StringCollection tableScripts = myTable.Script(scriptOptions);
                foreach (string script in tableScripts)
                    scrs += script + "\n\n";

                /* Generating CREATE TABLE command */
                tableScripts = myTable.Script();
                foreach (string script in tableScripts)
                    tbScr += script + "\n\n";
            }
 return (scrs + "\n\n" + tbScr);
}

Answer 1

Updated 17-Dec-2019: Check with latest .NET version; Added required References; Clean-up example code; Added sample conn string

Updated 15-Aug-2023: Check with .NET 7.0 version; Added required NuGet packages; Added export View example

The following was tested on:

• Win 7.0, .NET 4.0, VS 2010, SQL Server 2008R2
• Win 7.0, .NET 4.6.1, VS 2017, SQL Server 2014
• Win 11, .NET 7.0 VS 2022, SQL Server 2022

NuGet packages:

• Microsoft.SqlServer.SqlManagementObjects
• Microsoft.Data.SqlClient

Required assembly references (for .NET 4.*):

• Microsoft.SqlServer.ConnectionInfo
• Microsoft.SqlServer.Management.Sdk.Sfc
• Microsoft.SqlServer.Smo

Note: it seems that SMO is not fully implemented for .NET Core framework family. See this question for an example.

I created a simple Console app and called below function.

public static string ScriptDatabase( string dbConnectionString, string databaseName )
{
    SqlConnection conn = new SqlConnection( dbConnectionString );
    ServerConnection serverConn = new ServerConnection( conn );
    var server = new Server( serverConn );
    var database = server.Databases[databaseName];

    var scripter = new Scripter( server );
    scripter.Options.IncludeIfNotExists = true;
    scripter.Options.ScriptSchema = true;
    scripter.Options.ScriptData = true;

    string scrs = "";
    //Script out Tables
    foreach( Table myTable in database.Tables )
    {
        foreach( string s in scripter.EnumScript( new Urn[] { myTable.Urn } ) )
            scrs += s + "\n\n"; ;
    }
    
    //Script out Views
    foreach( View myView in database.Views )
    {
        //Skip system views
        //There is a scripter.Options.AllowSystemObjects = false; setting that does the same but it is glacially slow
        if( myView.IsSystemObject == true ) continue;
        foreach( string s in scripter.EnumScript( new Urn[] { myView.Urn } ) )
            scrs += s + "\n\n";
    }
    
    return ( scrs );
}

Function would be called as follows:

// Connection string for local SQL Server default instance
ScriptDatabase( "Server=.;Database=PlayGround;Trusted_Connection=True;", "PlayGround" );

Note: See this question if you get the following error

"A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)"

Output:

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tBlah]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tBlah](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [tID] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
    [Value] [varchar](20) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
END

SET IDENTITY_INSERT [dbo].[tBlah] ON 


INSERT [dbo].[tBlah] ([ID], [tID], [Value]) VALUES (1, N'2', N'1234')

INSERT [dbo].[tBlah] ([ID], [tID], [Value]) VALUES (2, N'2', N'345.6')

MSDN References:

• Microsoft.SqlServer.Management.Smo.Scripter
• Microsoft.SqlServer.Management.Common.ServerConnection
• Microsoft.SqlServer.Management.Smo.Server
• Microsoft.SqlServer.Management.Smo.Database