public string CreateSqlBackup()
{
var server = new Server(_databaseServer);
var dependencyWalker = new DependencyWalker(server);
var database = server.Databases[_databaseName];
var tables = (database.Tables.Cast <Table>().Where(table => table.IsSystemObject == false || table.Name == "sysdiagrams").Select(table => table.Urn)).ToArray();
var dependencyTree = dependencyWalker.DiscoverDependencies(tables, true);
var dependencyCollection = dependencyWalker.WalkDependencies(dependencyTree);
var strings = new List <string>();
var schemaScripter = new Scripter(server)
{
Options =
new ScriptingOptions
{
ClusteredIndexes = true,
Default = true,
DriAll = true,
ExtendedProperties = true,
IncludeHeaders = false,
Indexes = true,
}
};
var schemaScripts = dependencyCollection.SelectMany(x => schemaScripter.Script(new[] { x.Urn }).Cast <string>()).ToList();
strings.AddRange(schemaScripts);
strings.Add("GO");
var dataScripter = new Scripter(server)
{
Options = new ScriptingOptions {
ScriptData = true, ScriptSchema = false
}
};
var dataScripts = dataScripter.EnumScriptWithList(dependencyCollection);
strings.AddRange(dataScripts);
return(String.Join("\r\n", strings));
}
public void GetSmoObject(DatabaseObject dbObject)
{
if (_server == null)
{
_server = new Server();
_server.ConnectionContext.LoginSecure = true;
_server.ConnectionContext.ServerInstance = GetServerNameFromDatabaseName(dbObject.DatabaseName);
}
if (_database == null)
{
//Assume all databases are the same for now....
_database = _server.Databases[dbObject.DatabaseName];
}
if (_scripter == null)
{
_scripter = new Scripter(_server);
_scripter.Options.ScriptDrops = false;
_scripter.Options.ScriptData = false;
_scripter.Options.ScriptSchema = true;
_scripter.Options.WithDependencies = false;
_scripter.Options.DriAllConstraints = false;
_scripter.Options.DriAllKeys = true;
_scripter.Options.DriNonClustered = true;
_scripter.Options.DriUniqueKeys = true;
_scripter.Options.ScriptBatchTerminator = true;
_scripter.Options.NoCommandTerminator = false;
_scripter.Options.Statistics = true;
}
List <string> script = null;
switch (dbObject.TypeCode)
{
case DatabaseObjectTypeCode.Table:
if (_database.Tables[dbObject.ObjectName, dbObject.ObjectSchema] != null)
{
script = _scripter.EnumScriptWithList(new[] { _database.Tables[dbObject.ObjectName, dbObject.ObjectSchema].Urn }).ToList();
}
break;
case DatabaseObjectTypeCode.StoredProcedure:
if (_database.StoredProcedures[dbObject.ObjectName, dbObject.ObjectSchema] != null)
{
script = _scripter.EnumScriptWithList(new[] { _database.StoredProcedures[dbObject.ObjectName, dbObject.ObjectSchema].Urn }).ToList();
}
break;
case DatabaseObjectTypeCode.View:
if (_database.Views[dbObject.ObjectName, dbObject.ObjectSchema] != null)
{
script = _scripter.EnumScript(new[] { _database.Views[dbObject.ObjectName, dbObject.ObjectSchema].Urn }).ToList();
}
break;
case DatabaseObjectTypeCode.Function:
if (_database.UserDefinedFunctions[dbObject.ObjectName, dbObject.ObjectSchema] != null)
{
script = _scripter.EnumScript(new[] { _database.UserDefinedFunctions[dbObject.ObjectName, dbObject.ObjectSchema].Urn }).ToList();
}
break;
default:
throw new ArgumentException($"Method GetSmoObject; Invalid category '{dbObject.Category}'");
}
if (script != null)
{
dbObject.DbSql = $"{string.Join("\nGO\n", script)}\nGO".Replace(
@"SET ANSI_NULLS ON\nGO\nSET QUOTED_IDENTIFIER ON\nGO",
@"SET ANSI_NULLS ON\r\nSET QUOTED_IDENTIFIER ON\r\nGO").Replace("\n", "").Replace("SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO",
@"SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
").Replace("\r\n\r\n", "\r\n");
}
}