ScriptingOptions sql smo does not support scripting data

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

Required assembly references:

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

I created a simple Win forms app with OK button and called below function.

Note: If scripting out views you need to add

if( myView.IsSystemObject == true ) continue;

to ensure that system views are not being scripted; I have not tested this. For old versions of SQL Server you may also need this check for tables as well.

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 = "";    foreach( Table myTable in database.Tables )    {        foreach( string s in scripter.EnumScript( new Urn[] { myTable.Urn } ) )            scrs += s + "\n\n"; ;    }    return ( scrs );}

Function would be called as follows:

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

Output:

SET ANSI_NULLS ONSET QUOTED_IDENTIFIER ONIF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tBlah]') AND type in (N'U'))BEGINCREATE 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]ENDSET 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

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