public DatabaseEngineEdition TryGetEngineEdition(IDbConnection connection, out DatabaseEngineEdition engineEdition)
{
Validate.IsNotNull(nameof(connection), connection);
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connection.ConnectionString);
return(TryGetEngineEdition(builder, out engineEdition));
}
internal string GetTargetDatabaseEngineEdition()
{
DatabaseEngineEdition dbEngineEdition = this.serverConnection.DatabaseEngineEdition;
string dbEngineEditionString;
targetDatabaseEngineEditionMap.TryGetValue(dbEngineEdition, out dbEngineEditionString);
return((dbEngineEditionString != null) ? dbEngineEditionString : "SqlServerEnterpriseEdition");
}
private DatabaseEngineEdition GetOrReadCachedEngineEdition(IDbConnection conn)
{
if (CachedServerInfo.Instance.TryGetEngineEdition(conn, out _engineEdition) == DatabaseEngineEdition.Unknown)
{
_engineEdition = ReliableConnectionHelper.GetEngineEdition(conn);
CachedServerInfo.Instance.AddOrUpdateEngineEdition(conn, _engineEdition);
}
return(_engineEdition);
}
[InlineData("myDb", DatabaseEngineEdition.SqlOnDemand)] // is SqlOnDemand instance
public void AddOrUpdateEngineEditiopn(string dbName, DatabaseEngineEdition state)
{
// Set result into cache
DatabaseEngineEdition engineEdition;
SqlConnectionStringBuilder testSource = new SqlConnectionStringBuilder
{
DataSource = "testDataSource"
};
if (dbName != null)
{
testSource.InitialCatalog = dbName;
}
cache.AddOrUpdateCache(testSource, state, CachedServerInfo.CacheVariable.EngineEdition);
// Expect the same returned result
Assert.NotEqual(cache.TryGetEngineEdition(testSource, out engineEdition), DatabaseEngineEdition.Unknown);
Assert.Equal(engineEdition, state);
}
[InlineData(DatabaseEngineEdition.SqlOnDemand)] // is SqlOnDemand Instance
public void AddOrUpdateEngineEditionToggle(DatabaseEngineEdition state)
{
// Set result into cache
DatabaseEngineEdition engineEdition;
SqlConnectionStringBuilder testSource = new SqlConnectionStringBuilder
{
DataSource = "testDataSource"
};
cache.AddOrUpdateCache(testSource, state, CachedServerInfo.CacheVariable.EngineEdition);
// Expect the same returned result
Assert.NotEqual(cache.TryGetEngineEdition(testSource, out engineEdition), DatabaseEngineEdition.Unknown);
Assert.Equal(engineEdition, state);
DatabaseEngineEdition newState = state == DatabaseEngineEdition.SqlDataWarehouse ?
DatabaseEngineEdition.SqlOnDemand : DatabaseEngineEdition.SqlDataWarehouse;
cache.AddOrUpdateCache(testSource, newState, CachedServerInfo.CacheVariable.EngineEdition);
// Expect the opposite returned result
Assert.NotEqual(cache.TryGetEngineEdition(testSource, out engineEdition), DatabaseEngineEdition.Unknown);
Assert.Equal(engineEdition, newState);
}
public DatabasePrototypeAzure(CDataContainer context, DatabaseEngineEdition editionToCreate = DatabaseEngineEdition.SqlDatabase)
: base(context)
{
EditionToCreate = editionToCreate;
}
public DatabaseEngineEdition TryGetEngineEdition(SqlConnectionStringBuilder builder, out DatabaseEngineEdition engineEdition)
{
Validate.IsNotNull(nameof(builder), builder);
Validate.IsNotNullOrWhitespaceString(nameof(builder) + ".DataSource", builder.DataSource);
CachedInfo info;
bool hasFound = TryGetCacheValue(builder, out info);
if (hasFound)
{
engineEdition = info.EngineEdition;
return(engineEdition);
}
return(engineEdition = DatabaseEngineEdition.Unknown);
}
public void AddOrUpdateEngineEdition(IDbConnection connection, DatabaseEngineEdition engineEdition)
{
AddOrUpdateCache(connection, engineEdition, CacheVariable.EngineEdition);
}
/// <summary>
/// See https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.scriptingoptions_properties.aspx
/// </summary>
/// <param name="options">ScriptingOptions to modify - if null a new ScriptingOptions object will be used instead.</param>
/// <returns>A ScriptingOptions with default values set</returns>
private static ScriptingOptions DefaultOptions(SqlServerVersion targetServerVersion, DatabaseEngineEdition targetDatabaseEngineEdition, DatabaseEngineType targetDatabaseEngineType)
{
var o = new ScriptingOptions();
// General
o.AllowSystemObjects = false;
o.DdlHeaderOnly = false;
o.DdlBodyOnly = false;
// Here you could disable scripting absolute file paths
o.NoFileGroup = false;
o.IncludeFullTextCatalogRootPath = true;
// Output
o.ScriptBatchTerminator = true;
o.BatchSize = 100;
o.NoCommandTerminator = false;
o.AnsiFile = false;
o.Encoding = Encoding.UTF8;
o.ToFileOnly = true;
// "General" from the SSMS UI
o.AnsiPadding = false;
o.AppendToFile = true;
o.IncludeIfNotExists = false;
o.ContinueScriptingOnError = false;
o.ConvertUserDefinedDataTypesToBaseType = false;
o.WithDependencies = true;
o.IncludeHeaders = true;
o.DriIncludeSystemNames = false;
// Include unsupported statements - doesn't exist...
o.SchemaQualify = true;
o.Bindings = false;
o.NoCollation = true;
o.DriDefaults = true;
o.ScriptDrops = false;
o.ExtendedProperties = true;
o.TargetServerVersion = targetServerVersion;
o.TargetDatabaseEngineEdition = targetDatabaseEngineEdition;
o.TargetDatabaseEngineType = targetDatabaseEngineType;
o.LoginSid = false;
o.Permissions = false;
o.ScriptOwner = false;
o.Statistics = false;
o.IncludeDatabaseContext = true;
o.ScriptSchema = true;
o.ScriptData = true;
// "Table view options" from the SSMS UI
o.ChangeTracking = false;
o.DriChecks = true;
o.ScriptDataCompression = false;
o.DriForeignKeys = true;
o.FullTextIndexes = true;
o.Indexes = true;
o.PrimaryObject = true;
o.Triggers = true;
o.DriUniqueKeys = true;
o.NoIdentities = true;
o.IncludeDatabaseRoleMemberships = false;
o.NoMailProfilePrincipals = true;
o.NoMailProfileAccounts = true;
o.NoExecuteAs = true;
o.TimestampToBinary = false;
o.NoFileStreamColumn = false;
o.NoFileStream = false;
o.NoViewColumns = false;
o.NoVardecimal = false;
o.EnforceScriptingOptions = false;
o.OptimizerData = false;
o.NoXmlNamespaces = false;
o.NoTablePartitioningSchemes = false;
o.NoIndexPartitioningSchemes = false;
o.NonClusteredIndexes = true;
o.ClusteredIndexes = true;
o.XmlIndexes = true;
o.SchemaQualifyForeignKeysReferences = true;
o.FullTextStopLists = true;
o.FullTextCatalogs = true;
o.NoAssemblies = false;
o.AgentAlertJob = true;
o.AgentNotify = true;
o.AgentJobId = true;
o.Default = true;
o.DriAll = true;
o.DriAllConstraints = true;
o.DriAllKeys = true;
o.DriIndexes = true;
o.DriPrimaryKey = true;
o.DriClustered = true;
o.DriNonClustered = true;
o.DriWithNoCheck = false;
return(o);
}
public static void Main(string[] args)
{
var dataSource = args.Length > 0 ? args[0] : "localhost"; // 172.17.0.5,1433 192.168.56.1,1435, mydb.database.windows.net,1433
var dbName = args.Length > 1 ? args[1] : "master";
var user = args.Length > 2 ? args[2] : "sa";
var outputFolderSql = args.Length > 3 ? args[3] : "";
Console.WriteLine("Password for user {0} for database {1} at {2}: ", user, dbName, dataSource);
var password = GetPassword();
SqlServerVersion targetServerVersion = SqlServerVersion.Version140;
DatabaseEngineEdition targetDatabaseEngineEdition = DatabaseEngineEdition.Standard;
DatabaseEngineType targetDatabaseEngineType = DatabaseEngineType.Standalone;
String outputFileBase = dbName + "@" + @dataSource + "_" + DateTime.Now.ToString("yyyy-MM-dd_hh-mm-ss");
String outputFileSql = outputFileBase + ".sql";
String outputFilePathSql = Path.Combine(outputFolderSql, outputFileSql);
Console.WriteLine("Connecting to database... ");
var connectionString = new SqlConnectionStringBuilder
{
PersistSecurityInfo = true, // Needed - see https://github.com/Microsoft/sqltoolsservice/issues/372#issuecomment-307190530
DataSource = @dataSource,
UserID = user,
Password = password,
InitialCatalog = dbName,
PacketSize = 4096, // default is 8000, however 4096 gives much better performance when accessing azure database
//IntegratedSecurity = true, // Log in using SQL authentication or Windows authentication
//MultipleActiveResultSets = false,
//Encrypt = false,
//TrustServerCertificate = false,
};
var sqlConnection = new SqlConnection(connectionString.ConnectionString);
var serverConnection = new ServerConnection(sqlConnection);
var sqlServer = new Server(serverConnection);
//sqlServer.ConnectionContext.BatchSeparator = "GO";
// Improve performance by just loading fields we need
sqlServer.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");
sqlServer.SetDefaultInitFields(typeof(Table), "IsSystemObject");
sqlServer.SetDefaultInitFields(typeof(View), "IsSystemObject");
sqlServer.SetDefaultInitFields(typeof(UserDefinedFunction), "IsSystemObject");
sqlServer.SetDefaultInitFields(typeof(Trigger), "IsSystemObject");
sqlServer.SetDefaultInitFields(typeof(SqlAssembly), "IsSystemObject");
sqlServer.SetDefaultInitFields(typeof(Default), false);
sqlServer.SetDefaultInitFields(typeof(Rule), false);
sqlServer.SetDefaultInitFields(typeof(UserDefinedAggregate), false);
sqlServer.SetDefaultInitFields(typeof(Synonym), false);
sqlServer.SetDefaultInitFields(typeof(Sequence), false);
sqlServer.SetDefaultInitFields(typeof(SecurityPolicy), false);
sqlServer.SetDefaultInitFields(typeof(UserDefinedDataType), false);
sqlServer.SetDefaultInitFields(typeof(XmlSchemaCollection), false);
sqlServer.SetDefaultInitFields(typeof(UserDefinedType), false);
sqlServer.SetDefaultInitFields(typeof(UserDefinedTableType), false);
sqlServer.SetDefaultInitFields(typeof(PartitionScheme), false);
sqlServer.SetDefaultInitFields(typeof(PartitionFunction), false);
sqlServer.SetDefaultInitFields(typeof(PlanGuide), false);
sqlServer.SetDefaultInitFields(typeof(FullTextCatalog), false);
var db = sqlServer.Databases[dbName];
Console.WriteLine("Connected!");
Console.WriteLine("Generating script for database " + dbName + ". This may take a while...");
// ### First script "CREATE DATABASE ..."
var createDbScriptOptions = DefaultOptions(targetServerVersion, targetDatabaseEngineEdition, targetDatabaseEngineType);
createDbScriptOptions.FileName = outputFilePathSql;
createDbScriptOptions.ScriptData = false;
createDbScriptOptions.WithDependencies = false;
db.Script(createDbScriptOptions);
Console.WriteLine("Collecting objects...");
// ### Now script all the objects within the database
var urns = new UrnCollection();
Console.WriteLine("...Tables");
foreach (Table obj in db.Tables)
{
if (!obj.IsSystemObject)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
}
Console.WriteLine("...Views");
foreach (View obj in db.Views)
{
if (!obj.IsSystemObject)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
}
Console.WriteLine("...UserDefinedFunctions");
foreach (UserDefinedFunction obj in db.UserDefinedFunctions)
{
if (!obj.IsSystemObject)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
}
Console.WriteLine("...Defaults");
foreach (Default obj in db.Defaults)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...Rules");
foreach (Rule obj in db.Rules)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...Triggers");
foreach (Trigger obj in db.Triggers)
{
if (!obj.IsSystemObject)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
}
Console.WriteLine("...UserDefinedAggregates");
foreach (UserDefinedAggregate obj in db.UserDefinedAggregates)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...Synonyms");
foreach (Synonym obj in db.Synonyms)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...Sequences");
foreach (Sequence obj in db.Sequences)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...SecurityPolicies");
foreach (SecurityPolicy obj in db.SecurityPolicies)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...UserDefinedDataTypes");
foreach (UserDefinedDataType obj in db.UserDefinedDataTypes)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...XmlSchemaCollections");
foreach (XmlSchemaCollection obj in db.XmlSchemaCollections)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...UserDefinedTypes");
foreach (UserDefinedType obj in db.UserDefinedTypes)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...UserDefinedTableTypes");
foreach (UserDefinedTableType obj in db.UserDefinedTableTypes)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...PartitionSchemes");
foreach (PartitionScheme obj in db.PartitionSchemes)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...PartitionFunctions");
foreach (PartitionFunction obj in db.PartitionFunctions)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...PlanGuides");
foreach (PlanGuide obj in db.PlanGuides)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
Console.WriteLine("...Assemblies");
foreach (SqlAssembly obj in db.Assemblies)
{
if (!obj.IsSystemObject)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
}
Console.WriteLine("...StoredProcedures");
foreach (StoredProcedure obj in db.StoredProcedures)
{
if (!obj.IsSystemObject)
{
Console.WriteLine(" -> " + obj.Name);
urns.Add(obj.Urn);
}
}
var ftUrns = new UrnCollection();
Console.WriteLine("...FullTextCatalogs");
foreach (FullTextCatalog obj in db.FullTextCatalogs)
{
Console.WriteLine(" -> " + obj.Name);
ftUrns.Add(obj.Urn);
}
var scripter = new Scripter(sqlServer)
{
PrefetchObjects = true, // some sources suggest this may speed things up
};
scripter.Options = DefaultOptions(targetServerVersion, targetDatabaseEngineEdition, targetDatabaseEngineType);
scripter.Options.FileName = outputFilePathSql;
scripter.ScriptingProgress += (o, e) => Console.WriteLine("Scripting: {0}", e.Current);
scripter.ScriptingError += (o, e) => Console.WriteLine("Error: {0}", e.Current);
scripter.Options.WithDependencies = false;
scripter.EnumScript(ftUrns);
scripter.Options.WithDependencies = true;
scripter.EnumScript(urns);
Console.WriteLine("Scripting finished! See File:");
Console.WriteLine(outputFilePathSql);
// And now we also could create a backup (but be aware the file is saved ON THE SERVER
// (Maybe we could save it locally somehow via DeviceType.Pipe, DeviceType.Url, DeviceType.VirtualDevice, etc...)
//String outputFileBak = outputFileBase + ".bak";
//String outputFilePathBak = outputFileBak;
//Console.WriteLine();
//Console.WriteLine("And now we create a backup...");
//var backup = new Backup
//{
// Action = BackupActionType.Database,
// Database = dbName
//};
//backup.Devices.Add(new BackupDeviceItem(outputFilePathBak, DeviceType.File));
//backup.Initialize = true;
//backup.Checksum = true;
//backup.ContinueAfterError = false;
//backup.Incremental = false;
//backup.PercentCompleteNotification = 1;
//backup.LogTruncation = BackupTruncateLogType.Truncate;
//backup.PercentComplete += (s, e) => Console.WriteLine("Backup: {0}", e.Message);
//backup.Complete += (s, e) => Console.WriteLine("{0} See File: {1}", e.Error.Message, outputFilePathBak);
//backup.SqlBackupAsync(sqlServer);
// ----
// An alternative would be using the internal class ScriptPublishWizard via reflection (because we can't access internal stuff)
// ###################################################################
//new Microsoft.SqlServer.Management.SqlScriptPublish.ScriptPublishWizard();
//var assembly = Assembly.LoadFrom(@"C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Microsoft.SqlServer.Management.SqlScriptPublishUI.dll");
// ----
// Bonus: Display all the configuration options.
// ###################################################################
//foreach (ConfigProperty p in sqlServer.Configuration.Properties)
//{
// Console.WriteLine(p.DisplayName + " " + p.ConfigValue + " " + p.RunValue);
//}
//Console.WriteLine("There are " + sqlServer.Configuration.Properties.Count.ToString() + " configuration options.");
////Display the maximum and minimum values for ShowAdvancedOptions.
//int min = sqlServer.Configuration.ShowAdvancedOptions.Minimum;
//int max = sqlServer.Configuration.ShowAdvancedOptions.Maximum;
//Console.WriteLine("Minimum and Maximum values are " + min + " and " + max + ".");
sqlConnection.Close();
}