public static IEnumerable<string> CreateTablesWithData(this Database db)
{
var scr = new Scripter(GenServer);
var resultScript = new List<string> { Comment("Create tables + insert data") };
scr.Options.IncludeIfNotExists = true;
scr.Options.ScriptSchema = true;
scr.Options.DriPrimaryKey = true;
scr.Options.DriWithNoCheck = true;
scr.Options.DriUniqueKeys = true;
foreach (var table in db.Tables.Cast<Table>().Where(x => x.IsSystemObject == false))
{
resultScript.AddRange(scr.Script(new[] { table.Urn }).Cast<string>());
scr.Options.ScriptData = true;
scr.Options.ScriptSchema = false;
resultScript.AddRange(scr.EnumScript(new[] { table.Urn }));
scr.Options.ScriptData = false;
scr.Options.ScriptSchema = true;
}
return resultScript;
}
bool Export()
{
Scripter scr = new Scripter(Srv);
ScriptingOptions dropOptions = new ScriptingOptions
{
DriAll = true,
ClusteredIndexes = true,
Default = true,
Indexes = true,
IncludeHeaders = true,
AppendToFile = true,
ToFileOnly = true,
ScriptData = false,
ScriptSchema = true,
ScriptDrops = true,
IncludeDatabaseContext = true,
FileName = @"e:\Temp\test.sql"
};
ScriptingOptions createInsertOptions = new ScriptingOptions
{
// TODO: TargetServerVersion https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.sqlserverversion.aspx
DriAll = true,
ClusteredIndexes = true,
Default = true,
Indexes = true,
IncludeHeaders = true,
AppendToFile = true,
ToFileOnly = true,
ScriptData = true,
ScriptSchema = true,
ScriptDrops = false,
ContinueScriptingOnError = false,
IncludeDatabaseContext = true,
FileName = @"e:\Temp\test.sql"
};
Database db = Srv.Databases[DbComboBox.Text];
foreach (DbObject dbo in DbTables)
{
var tb = db.Tables.ItemById(dbo.Id);
Debug.WriteLine(tb.Name);
if (tb.IsSystemObject == true || dbo.Selected == false)
{
continue;
}
scr.Options = dropOptions;
scr.EnumScript(new Urn[] { tb.Urn });
scr.Options = createInsertOptions;
scr.EnumScript(new Urn[] { tb.Urn });
}
return(true);
}
static void Main(string[] args)
{
const string databaseName = "MyDatabase";
const string scriptFile = @"D:\MyDatabase.sql";
var server = new Server(new ServerConnection
{
ConnectionString = new SqlConnectionStringBuilder
{
DataSource = @"LOCALHOST\SQLEXPRESS",
IntegratedSecurity = true
}.ToString()
});
server.ConnectionContext.Connect();
var database = server.Databases[databaseName];
var output = new StringBuilder();
foreach (Table table in database.Tables)
{
var scripter = new Scripter(server)
{
Options = { ScriptData = true }
};
var script = scripter.EnumScript(new SqlSmoObject[] { table });
foreach (var line in script)
{
output.AppendLine(line);
}
}
File.WriteAllText(scriptFile, output.ToString());
}
static void Main(string[] args)
{
const string dbName = "RealizeDemo300";
var server = new Server();
var database = server.Databases[dbName];
var scripter = new Scripter(server)
{
Options =
{
ScriptData = true,
ScriptSchema = false
}
};
foreach (Table databaseTable in database.Tables)
{
var scriptStringCollection = scripter.EnumScript(new []
{
databaseTable.Urn
});
foreach (var str in scriptStringCollection)
{
Console.WriteLine(str);
}
}
}
public void GenerateData(DbInfo dbInfo, TextWriter textWriter)
{
var serverConn = new ServerConnection(dbInfo.Server, dbInfo.UserId, dbInfo.Password);
Server srv = new Server(serverConn);
Database db = srv.Databases[dbInfo.DbName];
if (db == null)
{
throw new Exception($"Database {dbInfo.DbName} not exists.");
}
Scripter scripter = new Scripter(srv);
scripter.Options.ScriptData = true;
scripter.Options.ScriptSchema = false;
textWriter.WriteLine("-----------------------------------------");
textWriter.WriteLine("-----------------data--------------------");
textWriter.WriteLine("-----------------------------------------");
foreach (Urn table in getTableUrnWithDependence(db))
{
Console.WriteLine();
var sc = scripter.EnumScript(new Urn[] { table });
foreach (string st in sc)
{
Console.WriteLine(st);
textWriter.WriteLine(st);
}
if (sc.Count() > 0)
{
textWriter.WriteLine("GO");
}
}
}
static void Main(string[] args)
{
var server = new Server(new ServerConnection {
ConnectionString = new SqlConnectionStringBuilder {
DataSource = @"Your Server Name", UserID = "Your User Id", Password = "******"
}.ToString()
});
server.ConnectionContext.Connect();
var database = server.Databases["Your Database Name"];
using (FileStream fs = new FileStream(@"H:\database_scripts\Gaurav.sql", FileMode.Append, FileAccess.Write))
using (StreamWriter sw = new StreamWriter(fs))
{
for each (Table table in database.Tables)
{
if (table.Name == "Your Table Name")
{
var scripter = new Scripter(server)
{
Options = { ScriptData = true }
};
var script = scripter.EnumScript(new SqlSmoObject[] { table });
for each (string line in script)
{
sw.WriteLine(line);
Console.WriteLine(line);
}
}
}
}
}
public Migration MakeCreateTableMigration(Server myServer, Table tbl)
{
StringBuilder sb = new StringBuilder();
Scripter createScrp = new Scripter(myServer);
createScrp.Options.ScriptSchema = true;
createScrp.Options.ScriptBatchTerminator = true;
createScrp.Options.DriAll = true;
createScrp.Options.IncludeIfNotExists = true;
createScrp.Options.ExtendedProperties = true;
foreach (string s in createScrp.EnumScript(new Urn[] { tbl.Urn }))
{
sb.AppendLine(s);
}
string MigrationName = $@"Create_{tbl.Name}";
int postIndex = 0;
string postFix = "";
while (File.Exists(MigrationName + postFix))
{
postIndex++;
postFix = $@"({postIndex})";
}
MigrationName += postFix;
return(new Migration()
{
Name = MigrationName, Script = sb.ToString()
});
}
public string GenerateTableData(ServerModel server, DataGenType gentype, List <TableModel> tables)
{
try
{
var output = new StringBuilder();
if (gentype == DataGenType.Truncate)
{
foreach (var t in tables)
{
var scpt = GetDataHeaderQuery(gentype, t.TableName);
output.AppendLine(scpt);
}
}
else
{
Server srv = new Server();
srv.ConnectionContext.LoginSecure = false;
srv.ConnectionContext.Login = server.Username;
srv.ConnectionContext.Password = server.Password;
srv.ConnectionContext.ServerInstance = server.ServerName;
Database genDb = srv.Databases[server.Database];
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.ScriptData = true;
scriptOptions.ScriptSchema = false;
Scripter scripter = new Scripter(srv)
{
Options = scriptOptions
};
foreach (var t in tables)
{
var gen = new StringBuilder();
var tbl = genDb.Tables[t.TableName, "dbo"];
var script = scripter.EnumScript(new SqlSmoObject[] { tbl });
foreach (var line in script)
{
gen.AppendLine(line);
}
var scpt = GetDataHeaderQuery(gentype, t.TableName);
scpt = scpt.Replace("{query}", gen.ToString());
output.AppendLine(scpt);
}
}
return(output.ToString());
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public void ScriptDatabase()
{
var DatabaseConfiguration = this.configuration
.GetSection("DatabaseConfiguration")
.Get <DatabaseConfiguration>();
string _serverName = DatabaseConfiguration.ServerName;
string _database = DatabaseConfiguration.Database;
string _username = DatabaseConfiguration.Username;
string _password = DatabaseConfiguration.UserPassword;
bool _logSecure = DatabaseConfiguration.TipeLogingSecure;
SendEmail sendEmail = new SendEmail(this.configuration);
try
{
ServerConnection srvConn2 = new ServerConnection(_serverName, _username, _password); // connects to default instance
srvConn2.LoginSecure = _logSecure;
Server srv3 = new Server(srvConn2); // connect remote
var databse = srv3.Databases[_database]; // select database
var scripter = new Scripter(srv3);
//options generate database with OMS sql server example https://www.sqlservercentral.com/scripts/generate-all-table-script-by-using-smo-c
scripter.Options.IncludeIfNotExists = false;
scripter.Options.ScriptSchema = false;
scripter.Options.ScriptData = true;
scripter.Options.SchemaQualify = true;
scripter.Options.ScriptForCreateDrop = true;
//select compatibility in this case sql server 2017
scripter.Options.TargetServerVersion = SqlServerVersion.Version140;
scripter.Options.DriUniqueKeys = true;
scripter.Options.DriForeignKeys = true;
scripter.Options.Indexes = true;
scripter.Options.DriPrimaryKey = true;
scripter.Options.IncludeHeaders = true;
scripter.Options.OptimizerData = true;
string scrs = "";
//create stream with data generate
using var stream = new MemoryStream();
using var writer = new StreamWriter(stream);
foreach (Table myTable in databse.Tables)
{
foreach (string s in scripter.EnumScript(new Urn[] { myTable.Urn }))
{
scrs += s + "\n\n";
}
}
writer.WriteLine(scrs);
writer.Flush();
stream.Position = 0;
//send stream in email
sendEmail.Send(string.Format("Backup data generate succeful. Name Database:{0}", _database), stream);
}
catch (Exception ex)
{
throw;
}
}
private void Output(NamedSmoObject obj, TextWriter tw, Scripter scrp, LinkedList <string> outputAtEnd, ref String header)
{
if (
(!obj.Properties.Contains("IsSystemObject") || !(bool)obj.Properties["IsSystemObject"].Value || IncludeSysObject(obj))
&&
IncludeObject(obj)
)
{
// Don't include CLR objects (they can't be scripted)
if (
obj.Discover().Count > 0 &&
obj.Discover()[0].GetType().GetProperty("ImplementationType") != null &&
obj.Discover()[0].GetType().GetProperty("ImplementationType").GetValue(obj.Discover()[0], null) is ImplementationType &&
(ImplementationType)obj.Discover()[0].GetType().GetProperty("ImplementationType").GetValue(obj.Discover()[0], null) == ImplementationType.SqlClr
)
{
return;
}
bool hasContent = false;
bool hasOutputAtEnd = false;
foreach (string s in scrp.EnumScript(new Urn[] { obj.Urn }))
{
if (outputAtEnd != null && OutputAtEnd(obj, s))
{
outputAtEnd.AddLast(s.TrimEnd() + ";");
hasOutputAtEnd = true;
}
else
{
if (header != null)
{
tw.WriteLine(header);
header = null;
}
tw.WriteLine(s.TrimEnd() + ";");
if ((s.Contains("CREATE TABLE") && obj.Properties.Contains("IsSystemObject") && ((bool)obj.Properties["IsSystemObject"].Value) && IncludeSysObject(obj)))
{
tw.WriteLine(MarkSystemObject(obj.Name));
}
hasContent = true;
}
}
if (hasContent && arguments.IncludeBatchSeparator)
{
tw.WriteLine("GO");
}
if (hasOutputAtEnd && arguments.IncludeBatchSeparator)
{
outputAtEnd.AddLast("GO");
}
}
}
private IEnumerable <string> ScriptDatabase(Database database)
{
ScriptingOptions options = new ScriptingOptions();
options.DriAll = true;
options.ClusteredIndexes = true;
options.Default = true;
options.Indexes = true;
options.IncludeHeaders = true;
options.DriDefaults = true;
options.IncludeHeaders = false;
//options.FileName = Path.GetTempFileName();
//Console.WriteLine("Scripted database to: {0}", options.FileName);
Scripter scripter = new Scripter(ServerSmo)
{
Options = options
};
return(scripter.EnumScript(database.Tables.OfType <Table>().ToArray())
.Concat(scripter.EnumScript(database.UserDefinedTableTypes.OfType <UserDefinedTableType>().ToArray())));
}
private void HandleUserDefinedTableType(UserDefinedTableType userDefinedTableType)
{
var scripter = new Scripter(_server)
{
Options = { ScriptData = true, IncludeHeaders = false }
};
string[] script = scripter.EnumScript(new SqlSmoObject[] { userDefinedTableType }).ToArray();
if (script.Length != 1)
{
throw new InvalidOperationException(string.Format(CultureInfo.CurrentCulture, "Could not script user defined table type. Expected one line but was: {0}", string.Join(Environment.NewLine, script)));
}
AppendLine("db.Execute(@\"{0}\");", 3, script[0]);
}
private void HandleTableData(Table table)
{
var scripter = new Scripter(_server)
{
Options = { ScriptData = true, IncludeHeaders = false }
};
IEnumerable <string> script = scripter.EnumScript(new SqlSmoObject[] { table });
foreach (var line in script
.Where(l => l.ToUpperInvariant().StartsWith("INSERT", StringComparison.Ordinal) ||
l.ToUpperInvariant().StartsWith("SET IDENTITY_INSERT", StringComparison.Ordinal)))
{
AppendLine("db.Execute(@\"{0}\");", 3, line.Replace("\"", "\"\""));
}
}
public string ScriptTableData(Microsoft.SqlServer.Management.Smo.Table tableToScript)
{
StringBuilder resultScript = new StringBuilder(string.Empty);
String targetFile = CurrentDirectoryPath + @"\DataScript_" + tableToScript.Name + ".sql";
//Always delete a previous file.
if (File.Exists(targetFile))
{
File.Delete(targetFile);
}
// Generate script
// Include content in script
// Exclude table schema (table creation etc.) and Dri (foreignkeys etc.)
// Only insert data when table is empty
resultScript.AppendFormat(LogToFile(targetFile, ("IF NOT EXISTS (SELECT 1 FROM [dbo].[" + tableToScript.Name + "])" + Environment.NewLine)));
resultScript.AppendLine(LogToFile(targetFile, "BEGIN"));
Scripter scripter = new Scripter(_sqlServer);
ScriptingOptions options = new ScriptingOptions();
options.DriAll = false;
options.ScriptSchema = false;
options.ScriptData = true;
scripter.Options = options;
// Add script to file content
foreach (string scriptLine in scripter.EnumScript(new Urn[] { tableToScript.Urn }))
{
string line = scriptLine;
line = line.Replace("SET ANSI_NULLS ON", string.Empty);
line = line.Replace("SET QUOTED_IDENTIFIER ON", string.Empty);
line = line.Replace("SET ANSI_NULLS OFF", string.Empty);
line = line.Replace("SET QUOTED_IDENTIFIER OFF", string.Empty);
resultScript.AppendLine(line.Trim());
LogToFile(targetFile, line);
}
resultScript.AppendLine(LogToFile(targetFile, "END"));
return(resultScript.ToString());
}
public void GenerateScriptFile(string destinationPath, Action <int> percentCompleteCallback, Action <Exception> errorCallback)
{
var server = GetDbServer();
var db = server.Databases[this.DatabaseName];
var scripter = new Scripter(server);
SetScriptOptions(destinationPath, scripter);
var smoObjects = new List <Urn>();
foreach (Table tb in db.Tables)
{
if (!tb.IsSystemObject)
{
smoObjects.Add(tb.Urn);
}
}
scripter.ScriptingError += new ScriptingErrorEventHandler((s, e) =>
{
if (errorCallback != null)
{
errorCallback(e.InnerException);
}
});
scripter.ScriptingProgress += new ProgressReportEventHandler((s, e) =>
{
int percent = Convert.ToInt32(((double)e.TotalCount / (double)e.Total) * 100.0);
if (percentCompleteCallback != null)
{
percentCompleteCallback(percent);
}
});
//var sc = scripter.Script(smoObjects.ToArray());
foreach (var sc in scripter.EnumScript(smoObjects.ToArray()))
{
}
}
public static void ExportFilesToScriptAndManifest(this Scripter scp, Urn[] objects, string manifestName, HashSet <Urn> hash = null, string outputDir = "output")
{
const string fileNameFormat = "{0}_{1}.SQL";
using (var file = System.IO.File.CreateText(System.IO.Path.Combine(outputDir, manifestName)))
{
foreach (var item in scp.GetObjectsInDepedencyOrder(objects))
{
if (hash == null || !hash.Contains(item))
{
if (hash != null)
{
hash.Add(item);
}
string objName = null;
var objEntity = scp.Server.GetSmoObject(item);
if (objEntity as View != null)
{
objName = (objEntity as View).Name;
}
else if (objEntity as Table != null)
{
objName = (objEntity as Table).Name;
}
else
{
throw new Exception(string.Format("Urn Type Not Expected: {0}", objEntity));
}
if (objName != null)
{
var onlyData = scp.Options.ScriptData;
var fileName = string.Format(fileNameFormat, onlyData ? "DATA" : item.Type, objName);
file.WriteLine(fileName);
scp.Options.FileName = System.IO.Path.Combine(outputDir, fileName);
scp.EnumScript(new[] { item });
}
}
}
}
}
public override string GenerateSqlScript(bool includeData)
{
var serverConn = new ServerConnection((SqlConnection)connection);
server = new Server(serverConn);
var db = new Database(server, connection.Database);
List <Urn> list = new List <Urn>();
DataTable dataTable = db.EnumObjects(DatabaseObjectTypes.Table);
foreach (DataRow row in dataTable.Rows)
{
list.Add(new Urn((string)row["Urn"]));
}
Scripter scripter = new Scripter();
scripter.Server = server;
scripter.Options.IncludeDatabaseContext = false;
scripter.Options.IncludeHeaders = true;
scripter.Options.SchemaQualify = true;
scripter.Options.ScriptData = includeData;
scripter.Options.SchemaQualifyForeignKeysReferences = true;
scripter.Options.NoCollation = true;
scripter.Options.DriAllConstraints = true;
scripter.Options.DriAll = true;
scripter.Options.DriAllKeys = true;
scripter.Options.Triggers = true;
scripter.Options.DriIndexes = true;
scripter.Options.ClusteredIndexes = true;
scripter.Options.NonClusteredIndexes = true;
scripter.Options.ToFileOnly = false;
var scripts = scripter.EnumScript(list.ToArray());
string result = "";
foreach (var script in scripts)
{
result += script + Environment.NewLine;
}
serverConn.Disconnect();
return(result);
}
private void ScriptData()
{
if (!this.VerifyDatabase())
{
return;
}
if (this.OutputFilePath == null)
{
this.Log.LogError("OutputFilePath is required");
return;
}
this.LogTaskMessage(string.Format(CultureInfo.CurrentCulture, "Scripting Data for Database: {0} to: {1}", this.DatabaseItem.ItemSpec, this.OutputFilePath.GetMetadata("FullPath")));
Microsoft.SqlServer.Management.Smo.Database db = this.sqlServer.Databases[this.DatabaseItem.ItemSpec];
var scrp = new Scripter(this.sqlServer)
{
Options = { ScriptSchema = this.ScriptSchema, ScriptData = true, ScriptDrops = this.ScriptDrops }
};
Regex filter = new Regex(this.RegexPattern, RegexOptions.Compiled);
if (File.Exists(this.OutputFilePath.GetMetadata("FullPath")))
{
File.Delete(this.OutputFilePath.GetMetadata("FullPath"));
System.Threading.Thread.Sleep(2000);
}
// Iterate through the tables in database and script each one. Display the script.
foreach (Table tb in db.Tables)
{
// check if the table is not a system table
if (tb.IsSystemObject == false && filter.IsMatch(tb.Name) && tb.RowCount > 0)
{
this.LogTaskMessage(string.Format(CultureInfo.CurrentCulture, "\tScripting: {0}. {1} rows", tb.Name, tb.RowCount));
var sc = scrp.EnumScript(new[] { tb.Urn });
System.IO.File.AppendAllLines(this.OutputFilePath.GetMetadata("FullPath"), sc);
}
}
}
private static StringBuilder GenerateScript(Scripter scripter, List <Urn> urns)
{
Console.WriteLine("Building script");
var retval = new StringBuilder();
foreach (var str in scripter.EnumScript(urns.ToArray()))
{
if (str.StartsWith("INSERT [dbo].[umbracoServer]"))
{
Console.WriteLine("Ignoring umbracoServer insert");
}
else
{
retval.AppendLine(str);
retval.AppendLine("GO");
}
}
return(retval);
}
private static void WriteScriptToFile(string fullFileName, Microsoft.SqlServer.Management.Sdk.Sfc.Urn schemaObject, ref Scripter scripter)
{
StringBuilder script = new StringBuilder();
// Add script to file content
foreach (string scriptLine in scripter.EnumScript(new Urn[] { schemaObject }))
{
string line = scriptLine;
if (!line.Contains("SET ANSI_NULLS ON") &&
!line.Contains("SET ANSI_NULLS OFF") &&
!line.Contains("SET QUOTED_IDENTIFIER ON") &&
!line.Contains("SET QUOTED_IDENTIFIER OFF")
)
{
script.AppendLine(line);
}
}
using (StreamWriter sw = new StreamWriter(fullFileName, false))
{
sw.Write(script.ToString());
}
}
private void ClearTable(Table table)
{
Logger.WriteLine("Clearing table {0}", table.Urn);
var script = new Scripter(_sourceServer)
{
Options = new ScriptingOptions
{
ScriptData = true,
ScriptDrops = true,
ScriptSchema = false
}
};
Logger.WriteLine(" - Generating scripts");
IEnumerable <string> scripts = script.EnumScript(new SqlSmoObject[] { table });
foreach (string scr in scripts)
{
Logger.WriteLine(" - Executing script on {0}: {1}", _targetDatabase.Urn, scr);
_targetDatabase.ExecuteNonQuery(scr);
}
}
public string generarScript()
{
var server = new Server(new ServerConnection {
ConnectionString = this.CadenaConexion()
});
server.ConnectionContext.Connect();
var database = server.Databases["LaObra"];
var sb = new StringBuilder();
foreach (Table table in database.Tables)
{
var scripter = new Scripter(server)
{
Options = { ScriptData = true }
};
var script = scripter.EnumScript(new SqlSmoObject[] { table });
foreach (var line in script)
{
sb.AppendLine(line);
}
}
return(sb.ToString());
}
public string GetTableScript(string Table)
{
try
{
var res = new StringBuilder();
var table = server.Databases[this.sqlConnection.Database].Tables.Cast <Table>().Where(a => a.Name == Table && !a.IsSystemObject).FirstOrDefault();
scripter = new Scripter(server)
{
Options = { ScriptData = false }
};
var script = scripter.EnumScript(new SqlSmoObject[] { table });
foreach (var line in script)
{
res.AppendLine(line);
}
return(res.ToString());
}
catch (System.Exception ex)
{
return(ex.Message);
}
}
public override string GenerateSqlScript(bool includeData)
{
var serverConn = new ServerConnection((SqlConnection)connection);
server = new Server(serverConn);
var db = new Database(server, connection.Database);
List<Urn> list = new List<Urn>();
DataTable dataTable = db.EnumObjects(DatabaseObjectTypes.Table);
foreach (DataRow row in dataTable.Rows)
{
list.Add(new Urn((string)row["Urn"]));
}
Scripter scripter = new Scripter();
scripter.Server = server;
scripter.Options.IncludeDatabaseContext = false;
scripter.Options.IncludeHeaders = true;
scripter.Options.SchemaQualify = true;
scripter.Options.ScriptData = includeData;
scripter.Options.SchemaQualifyForeignKeysReferences = true;
scripter.Options.NoCollation = true;
scripter.Options.DriAllConstraints = true;
scripter.Options.DriAll = true;
scripter.Options.DriAllKeys = true;
scripter.Options.Triggers = true;
scripter.Options.DriIndexes = true;
scripter.Options.ClusteredIndexes = true;
scripter.Options.NonClusteredIndexes = true;
scripter.Options.ToFileOnly = false;
var scripts = scripter.EnumScript(list.ToArray());
string result = "";
foreach (var script in scripts)
result += script + Environment.NewLine;
serverConn.Disconnect();
return result;
}
public void ScriptTable(Scripter scripter, Table table, string outputPath, bool append)
{
// alternatively
// _scripter.Options.ToFileOnly = true;
// _scripter.Options.FileName = _fileName;
// _options.NoCommandTerminator = true;
// _scripter.EnumScript(new Urn[] { _table.Urn });
using (StreamWriter _writer = new StreamWriter(outputPath, append, this.Encoding))
{
int _row = 0;
foreach (string _scriptLine in scripter.EnumScript(new Urn[] { table.Urn }))
{
// FIXME: IDENTITY_INSERT ON ends with [space][cr+lf] when scripted by SMO
// but not when scripted by SSMS 10.50
string _trimmedScriptLine = _scriptLine.TrimEnd('\r', '\n', ' ');
if (_row > 0 && _row % this.CommandTerminatorInterval == 0)
{
_writer.WriteLine(Exporter.CommandTerminator);
if (this.PrintProcessedRecords)
{
int _processedRows = _row;
//FIXME: Find out why is this necessary
if (this.OffByOne)
{
_processedRows -= _row / this.CommandTerminatorInterval;
}
_writer.WriteLine(Exporter.PrintProcessedRecordsFormat, _processedRows);
}
}
_writer.WriteLine(_trimmedScriptLine);
_row++;
}
}
}
public string ScriptDatabase(string ServerName, string DatabaseName, ProgressDelegate progress)
{
var sb = new StringBuilder();
string scrs = "";
try
{
var server = new Server(ServerName);
var database = server.Databases[DatabaseName];
var scripter = new Scripter(server);
scripter.Options.BatchSize = 1;
scripter.Options.ScriptDrops = true;
scripter.Options.ScriptBatchTerminator = true;
scripter.Options.NoCommandTerminator = false;
scripter.Options.ConvertUserDefinedDataTypesToBaseType = false;
scripter.Options.Default = true;
scripter.Options.ScriptSchema = true;
scripter.Options.ScriptData = true;
scripter.Options.WithDependencies = true;
scripter.Options.IncludeHeaders = false;
scripter.Options.ClusteredIndexes = true;
scripter.Options.DriAllKeys = true;
scripter.Options.DriDefaults = true;
scripter.Options.DriIndexes = true;
scripter.Options.DriNonClustered = true;
scripter.Options.DriPrimaryKey = true;
scripter.Options.DriUniqueKeys = true;
scripter.Options.FullTextIndexes = true;
scripter.Options.Triggers = false;
scripter.ScriptingProgress += scripter_ScriptingProgress;
scripter.Options.WithDependencies = false;
//var smoObjects = new Urn[1];
//foreach (Table t in database.Tables)
//{
// smoObjects[0] = t.Urn;
// if (t.IsSystemObject == false)
// {
// StringCollection sc = scripter.Script(smoObjects);
// foreach (var st in sc)
// {
// sb.Append(st);
// sb.Append("\r\n\r\n");
// }
// }
//}
string GO = "GO\r\n";
string crnl = "\r\n";
scrs = String.Format("USE [{0}]", DatabaseName) + crnl;
scrs += GO + crnl + crnl;
foreach (Table myTable in database.Tables)
{
if (myTable.IsSystemObject == false)
{
IEnumerable <string> tableScripts = scripter.EnumScript(new Urn[] { myTable.Urn });
string tmp = "";
foreach (string script in tableScripts)
{
tmp += script + crnl;
}
scrs += tmp + GO;
progress(tmp);
}
}
scrs += "\r\n\r\n";
scripter.Options.ScriptDrops = false;
foreach (Table myTable in database.Tables)
{
if (myTable.IsSystemObject == false)
{
IEnumerable <string> tableScripts = scripter.EnumScript(new Urn[] { myTable.Urn });
string tmp = "";
foreach (string script in tableScripts)
{
tmp += script + crnl;
}
scrs += tmp + GO;
progress(tmp);
}
}
progress("*******************************************************************************");
progress(" Filtering system stored procedures...");
progress("*******************************************************************************");
//List<StoredProcedure> filteredList = database.StoredProcedures.Where(x => x.IsSystemObject == false).ToList();
List <StoredProcedure> list = (List <StoredProcedure>)database.StoredProcedures.OfType <StoredProcedure>().Where(x => x.IsSystemObject == false).ToList <StoredProcedure>();
scripter.Options.ScriptDrops = true;
foreach (StoredProcedure sproc in list)
{
IEnumerable <string> sprocScripts = scripter.EnumScript(new Urn[] { sproc.Urn });
string tmp = "";
foreach (string script in sprocScripts)
{
tmp += script + crnl;
}
scrs += tmp + GO;
progress(tmp);
}
scripter.Options.ScriptDrops = false;
foreach (StoredProcedure sproc in list)
{
IEnumerable <string> sprocScripts = scripter.EnumScript(new Urn[] { sproc.Urn });
string tmp = "";
foreach (string script in sprocScripts)
{
tmp += script + crnl;
}
scrs += tmp + GO;
progress(tmp);
}
progress("*******************************************************************************");
progress(" Filtering system functions...");
progress("*******************************************************************************");
List <UserDefinedFunction> listFunctions = (List <UserDefinedFunction>)database.UserDefinedFunctions.OfType <UserDefinedFunction>().Where(x => x.IsSystemObject == false).ToList <UserDefinedFunction>();
scripter.Options.ScriptDrops = true;
foreach (UserDefinedFunction func in listFunctions)
{
IEnumerable <string> sprocScripts = scripter.EnumScript(new Urn[] { func.Urn });
string tmp = "";
foreach (string script in sprocScripts)
{
tmp += script + crnl;
}
scrs += tmp + GO;
progress(tmp);
}
scripter.Options.ScriptDrops = false;
foreach (UserDefinedFunction func in listFunctions)
{
IEnumerable <string> sprocScripts = scripter.EnumScript(new Urn[] { func.Urn });
string tmp = "";
foreach (string script in sprocScripts)
{
tmp += script + crnl;
}
scrs += tmp + GO;
progress(tmp);
}
}
catch (Exception ex)
{
return(ex.Message);
}
//return sb.ToString();
scrs = scrs.Replace("SET ANSI_NULLS ON", "SET ANSI_NULLS ON\r\nGO");
scrs = scrs.Replace("SET QUOTED_IDENTIFIER ON", "SET QUOTED_IDENTIFIER ON\r\nGO");
return(scrs);
}
static int Main(string[] args)
{
if (args.Length < 2) { ShowUsage(); return 1; }
Server sqlServer = new Server(args[0]);
Database db = default(Database);
db = sqlServer.Databases[args[1]];
string filePath = args[2];
DateTime now = DateTime.Now;
// set up text file
string filename = string.Format("{0:0000}{1:00}{2:00}{3:00}{4:00}_{5}.sql", now.Year, now.Month, now.Day, now.Hour, now.Minute, args[1]);
filename = Path.Combine(filePath, filename);
Scripter scrp = default(Scripter);
scrp = new Scripter(sqlServer);
scrp.Options.ScriptSchema = true;
scrp.Options.WithDependencies = true;
scrp.Options.ScriptData = false;
Urn[] smoObjects = new Urn[2];
int objectCount = 0;
// write each table
foreach (Table tb in db.Tables)
{
if (tb.IsSystemObject == false)
{
Console.WriteLine("Table: {0}", tb.Urn);
smoObjects = new Urn[1];
smoObjects[0] = tb.Urn;
using (StreamWriter w = File.AppendText(filename))
{
foreach (string s in scrp.EnumScript(new Urn[] { tb.Urn }))
{
w.WriteLine(s);
Console.Write(".");
objectCount++;
}
w.Close();
}
}
Console.WriteLine();
Console.Write("-Indexes: ");
// write each index
foreach (Index ix in tb.Indexes)
{
if (ix.IsSystemObject == false)
{
Console.Write(".");
objectCount++;
using (StreamWriter w = File.AppendText(filename))
{
StringCollection indexScript = ix.Script();
foreach (string s in indexScript)
{
w.WriteLine(s);
}
w.Close();
}
}
}
Console.WriteLine();
Console.Write("-Triggers: ");
// write each trigger
foreach (Trigger trig in tb.Triggers)
{
if (trig.IsSystemObject == false)
{
Console.Write(".");
objectCount++;
smoObjects = new Urn[1];
smoObjects[0] = trig.Urn;
using (StreamWriter w = File.AppendText(filename))
{
foreach (string s in scrp.EnumScript(new Urn[] { trig.Urn }))
{
w.WriteLine(s);
}
w.Close();
}
}
}
Console.WriteLine();//finished triggers
//next table
Console.WriteLine();
}
// write each view
Console.Write("Views: ");
foreach (View vw in db.Views)
{
if (vw.IsSystemObject == false)
{
Console.Write(".");
objectCount++;
smoObjects = new Urn[1];
smoObjects[0] = vw.Urn;
using (StreamWriter w = File.AppendText(filename))
{
foreach (string s in scrp.EnumScript(new Urn[] { vw.Urn }))
{
w.WriteLine(s);
}
w.Close();
}
}
}
Console.WriteLine();
Console.Write("Stored Procedures: ");
// write each stored procedure
foreach (StoredProcedure sp in db.StoredProcedures)
{
if (sp.IsSystemObject == false)
{
Console.Write(".");
objectCount++;
smoObjects = new Urn[1];
smoObjects[0] = sp.Urn;
using (StreamWriter w = File.AppendText(filename))
{
foreach (string s in scrp.EnumScript(new Urn[] { sp.Urn }))
{
w.WriteLine(s);
}
w.Close();
}
}
}
Console.WriteLine();
// write each user defined funtion
Console.Write("UserDefinedFunctions: ");
foreach (UserDefinedFunction udf in db.UserDefinedFunctions)
{
if (udf.IsSystemObject == false)
{
smoObjects = new Urn[1];
smoObjects[0] = udf.Urn;
using (StreamWriter w = File.AppendText(filename))
{
foreach (string s in scrp.EnumScript(new Urn[] { udf.Urn }))
{
w.WriteLine(s);
Console.Write(".");
}
w.Close();
}
objectCount++;
}
}
Console.WriteLine();
ReportProgress(objectCount);
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("File written: {0}", filename);
return 0;
}
public void ChangeCollation(string collation)
{
string filename;
FileStream fileStream = GetTempFileStreamWriter(out filename);
fileStream.Close();
Server server = new Server(new ServerConnection(Database.Instance.Connection));
Microsoft.SqlServer.Management.Smo.Database smoDatabase = server.Databases[Database.Instance.DatabaseName];
ScriptingOptions scriptingOptionDBStructure = new ScriptingOptions
{
ToFileOnly = true,
NoCollation = true,
DriAll = true,
ExtendedProperties = true,
Indexes = true,
Triggers = true,
AppendToFile = true,
FileName = filename
};
ScriptingOptions scriptingOptionData = new ScriptingOptions
{
ToFileOnly = true,
NoCollation = true,
ScriptData = true,
ScriptSchema = false,
AppendToFile = true,
FileName = filename
};
Transfer transfer = new Transfer(smoDatabase)
{
CopyAllObjects = false,
CopyAllTables = true,
CopyAllViews = true,
CopyAllUserDefinedFunctions = true,
Options = scriptingOptionDBStructure
};
transfer.ScriptTransfer();
StringBuilder noCheckConstraints = new StringBuilder();
StringBuilder witchCheckConstraints = new StringBuilder();
foreach (DatabaseObject constraint in _databaseOperations.GetActiveForeignKeys())
{
noCheckConstraints.AppendLine($"ALTER TABLE {constraint.Schema} NOCHECK CONSTRAINT {constraint.Name};");
witchCheckConstraints.AppendLine($"ALTER TABLE {constraint.Schema} WITH CHECK CHECK CONSTRAINT {constraint.Name};");
}
using (StreamWriter writer = new StreamWriter(filename, true, Encoding.Unicode))
{
writer.Write(noCheckConstraints.ToString());
writer.WriteLine("GO");
}
Scripter scripter = new Scripter(server);
scripter.Options = scriptingOptionData;
foreach (Table table in smoDatabase.Tables)
{
scripter.EnumScript(new[] { table.Urn });
}
using (StreamWriter writer = new StreamWriter(filename, true, Encoding.Unicode))
{
writer.Write(witchCheckConstraints.ToString());
}
Database database = Database.Instance;
foreach (DatabaseObject index in _databaseOperations.GetIndexes())
{
database.ExecuteNonResultQuery($"DROP INDEX [{index.Name}] ON {index.Schema};");
}
foreach (DatabaseObject constraint in _databaseOperations.GetConstraints())
{
database.ExecuteNonResultQuery($"ALTER TABLE {constraint.Schema} DROP CONSTRAINT [{constraint.Name}];");
}
foreach (DatabaseObject view in _databaseOperations.GetViews())
{
database.ExecuteNonResultQuery($"DROP VIEW {view.NameWithSchemaBrackets};");
}
foreach (DatabaseObject table in _databaseOperations.GetTables())
{
database.ExecuteNonResultQuery($"DROP TABLE {table.NameWithSchemaBrackets};");
}
foreach (DatabaseObject function in _databaseOperations.GetFunctions())
{
database.ExecuteNonResultQuery($"DROP FUNCTION {function.NameWithSchemaBrackets};");
}
database.ExecuteNonResultQuery($"ALTER DATABASE {database.DatabaseName} SET SINGLE_USER WITH ROLLBACK IMMEDIATE;");
database.ExecuteNonResultQuery($"ALTER DATABASE {database.DatabaseName} COLLATE {collation};");
database.ExecuteNonResultQuery($"ALTER DATABASE {database.DatabaseName} SET MULTI_USER;");
using (StreamReader file = File.OpenText(filename))
{
string line;
StringBuilder buffer = new StringBuilder();
while ((line = file.ReadLine()) != null)
{
if (line == "GO")
{
database.ExecuteNonResultQuery(buffer.ToString());
buffer.Clear();
}
else
{
buffer.AppendLine(line);
}
}
if (buffer.Length > 0)
{
database.ExecuteNonResultQuery(buffer.ToString());
buffer.Clear();
}
}
File.Delete(filename);
}
private string _scriptContentDatabase(string databaseName, bool getSchemaEntity = true, string connectionString = "")
{
//List<string> _dataBaseParams = new List<string>() { "ANSI_NULLS ON", "QUOTED_IDENTIFIER ON" };
var _scriptSql = new StringBuilder();
Server _server;
if (connectionString.Trim(' ') != "")
{
_server = new Server(new ServerConnection(new SqlConnection(connectionString)));
}
else
{
_server = new Server(new ServerConnection(new SqlConnection(ConnectionString)));
}
var _database = _server.Databases[databaseName];
var _scripter = new Scripter(_server);
_scripter.Options.WithDependencies = true;
_scripter.Options.IncludeHeaders = true;
_scripter.Options.ScriptSchema = true;
_scripter.Options.ScriptData = true;
_scripter.Options.ScriptDrops = false;
_scripter.Options.IncludeIfNotExists = true;
var smoObjects = new Urn[1];
foreach (Table t in _database.Tables)
{
if (getSchemaEntity == true)
{
smoObjects[0] = t.Urn;
if (t.IsSystemObject == false)
{
foreach (string s in _scripter.EnumScript(new Urn[] { t.Urn }))
{
string str = s;
//foreach (string dbParams in _dataBaseParams)
//{
// if (s.Contains(dbParams))
// {
// str = s.Insert(s.IndexOf(dbParams) + dbParams.Length, " ");
// }
//}
_scriptSql.Append(str + Environment.NewLine);
}
}
}
else
{
if (t.Name.StartsWith("en_") == false)
{
smoObjects[0] = t.Urn;
if (t.IsSystemObject == false)
{
foreach (string s in _scripter.EnumScript(new Urn[] { t.Urn }))
{
string str = s;
//foreach (string dbParams in _dataBaseParams)
//{
// if (s.Contains(dbParams))
// {
// str = s.Insert(s.IndexOf(dbParams) + dbParams.Length, " ");
// }
//}
_scriptSql.Append(str);
}
}
}
}
}
return(_scriptSql.ToString());
}
public void SQLServerExportPodaci(string ConnectionString, string sourceDbPath, string destinationScriptPath)
{
SqlConnection sqlConn = new SqlConnection(ConnectionString);
ServerConnection serverConn = new ServerConnection(sqlConn);
Server server = new Server(serverConn);
Database database = server.Databases[sourceDbPath];
var output = new StringBuilder();
foreach (Table table in database.Tables)
{
var scripter = new Scripter(server) { Options = { ScriptSchema = false, ContinueScriptingOnError = true, DriAllConstraints = false, DriPrimaryKey = false, DriUniqueKeys = false, DriForeignKeys = false, SchemaQualify = true, SchemaQualifyForeignKeysReferences = false, ScriptData = true, TargetDatabaseEngineType = DatabaseEngineType.SqlAzureDatabase } };
var script = scripter.EnumScript(new SqlSmoObject[] { table });
foreach (var line in script)
output.AppendLine(line);
}
File.WriteAllText(destinationScriptPath, output.ToString());
}
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");
}
}
private void ScriptTableDataNative(Table table, string dataDirectory, Server server)
{
if (!Directory.Exists(dataDirectory)) Directory.CreateDirectory(dataDirectory);
var fileName = Path.ChangeExtension(Path.Combine(dataDirectory, GetScriptFileName(table)), "sql");
var scripter = new Scripter(server)
{
Options =
{
ScriptData = true,
ScriptSchema = false
}
};
using (TextWriter writer = GetStreamWriter(fileName, false))
{
foreach (var script in scripter.EnumScript(new[] { table }))
{
writer.WriteLine(script);
}
}
}
/// <summary>
/// Executa o backup do sistema e retorna um arquivo .sql
/// </summary>
/// <param name="requisicaoDto"></param>
/// <param name="retornoDto"></param>
/// <returns></returns>
public bool FazerBackupSistema(BaseRequisicaoDto requisicaoDto, ref RetornoObterArquivoDto retornoDto)
{
string mensagemErro = "";
if (!UtilitarioBll.ValidarIdentificacao(requisicaoDto.Identificacao, requisicaoDto.IdUsuario, ref mensagemErro))
{
retornoDto.Retorno = false;
retornoDto.Mensagem = mensagemErro;
logBll.ResgistrarLog(requisicaoDto, LogRecursos.BaseIncluir, Guid.Empty, mensagemErro);
return(false);
}
PizzaByteContexto context = new PizzaByteContexto();
try
{
StringBuilder deletes = new StringBuilder(string.Empty);
StringBuilder inserts = new StringBuilder(string.Empty);
StringBuilder insertsFk = new StringBuilder(string.Empty);
ScriptingOptions scriptOptions = new ScriptingOptions();
SqlConnection conn = new SqlConnection(context.Database.Connection.ConnectionString);
Server srv1 = new Server(new ServerConnection(conn));
Database db1 = srv1.Databases[context.Database.Connection.Database];
deletes.AppendLine("Use " + context.Database.Connection.Database);
deletes.AppendLine("GO ");
deletes.AppendLine("BEGIN TRY ");
deletes.AppendLine("BEGIN TRANSACTION ");
List <string> tabelasComChaveEstrangeira = new List <string>();
Scripter scr = new Scripter(srv1);
foreach (Table table in db1.Tables)
{
for (int i = 0; i < table.ForeignKeys.Count; i++)
{
if (!tabelasComChaveEstrangeira.Contains(table.ForeignKeys[i].ReferencedTable))
{
if (table.ForeignKeys[i].ReferencedTable == "Pedidos")
{
tabelasComChaveEstrangeira.Insert(0, table.ForeignKeys[i].ReferencedTable);
}
else
{
tabelasComChaveEstrangeira.Add(table.ForeignKeys[i].ReferencedTable);
}
}
}
}
// Tabelas que não fazer a fk de outras tabelas
foreach (Table table in db1.Tables)
{
if (table.Name != "__MigrationHistory" && !tabelasComChaveEstrangeira.Contains(table.Name))
{
ScriptingOptions options = new ScriptingOptions();
options.DriAll = false;
options.ScriptSchema = false;
options.ScriptData = true;
scr.Options = options;
deletes.AppendLine("DELETE FROM [PizzaByte].[" + table.Name + "]");
// Add script to file content
foreach (string scriptLine in scr.EnumScript(new Urn[] { table.Urn }))
{
string line = scriptLine;
line = line.Replace("SET ANSI_NULLS ON", string.Empty);
line = line.Replace("SET QUOTED_IDENTIFIER ON", string.Empty);
line = line.Replace("SET ANSI_NULLS OFF", string.Empty);
line = line.Replace("SET QUOTED_IDENTIFIER OFF", string.Empty);
inserts.AppendLine(line.Trim());
}
}
}
deletes.AppendLine("DELETE FROM [PizzaByte].[Pedidos]");
// Depois as tabelas que são fk
foreach (Table table in db1.Tables)
{
if (table.Name != "__MigrationHistory" && tabelasComChaveEstrangeira.Contains(table.Name))
{
ScriptingOptions options = new ScriptingOptions();
options.DriAll = false;
options.ScriptSchema = false;
options.ScriptData = true;
scr.Options = options;
if (table.Name != "Pedidos")
{
deletes.AppendLine("DELETE FROM [PizzaByte].[" + table.Name + "]");
}
// Add script to file content
foreach (string scriptLine in scr.EnumScript(new Urn[] { table.Urn }))
{
string line = scriptLine;
line = line.Replace("SET ANSI_NULLS ON", string.Empty);
line = line.Replace("SET QUOTED_IDENTIFIER ON", string.Empty);
line = line.Replace("SET ANSI_NULLS OFF", string.Empty);
line = line.Replace("SET QUOTED_IDENTIFIER OFF", string.Empty);
insertsFk.AppendLine(line.Trim());
}
}
}
deletes.Append(insertsFk.ToString());
deletes.Append(inserts.ToString());
deletes.AppendLine("COMMIT TRAN ");
deletes.AppendLine("END TRY ");
deletes.AppendLine("BEGIN CATCH ");
deletes.AppendLine("ROLLBACK TRAN ");
deletes.AppendLine("END CATCH ");
string backupCriptografado = "";
if (!UtilitarioBll.CriptografarString(deletes.ToString(), ref backupCriptografado))
{
retornoDto.Retorno = false;
retornoDto.Mensagem = "Erro ao criptografar o backup";
logBll.ResgistrarLog(requisicaoDto, LogRecursos.Backup, Guid.Empty, retornoDto.Mensagem);
return(false);
}
byte[] bytes = Encoding.UTF8.GetBytes(backupCriptografado);
string base64 = Convert.ToBase64String(bytes);
retornoDto.ArquivoBase64 = base64;
return(true);
}
catch (Exception ex)
{
retornoDto.Retorno = false;
retornoDto.Mensagem = "Erro ao executar o backup: " + ex.Message;
logBll.ResgistrarLog(requisicaoDto, LogRecursos.Backup, Guid.Empty, retornoDto.Mensagem);
return(false);
}
}
private IEnumerable<string> ScriptDatabase(Database database)
{
ScriptingOptions options = new ScriptingOptions();
options.DriAll = true;
options.ClusteredIndexes = true;
options.Default = true;
options.Indexes = true;
options.IncludeHeaders = true;
options.DriDefaults = true;
options.IncludeHeaders = false;
//options.FileName = Path.GetTempFileName();
//Console.WriteLine("Scripted database to: {0}", options.FileName);
Scripter scripter = new Scripter(ServerSmo) { Options = options };
return scripter.EnumScript(database.Tables.OfType<Table>().ToArray())
.Concat(scripter.EnumScript(database.UserDefinedTableTypes.OfType<UserDefinedTableType>().ToArray()));
}
private StringBuilder GenerateScriptFromdatabase(Server server, Database db)
{
try
{
if (server == default(Server) || db == default(Database))
{
//
}
StringBuilder output = new StringBuilder();
//if (db.Tables == null || db.Tables.Count == 0)
//{
// MessageBox.Show("No tables selected","BP Utility",MessageBoxButton.OK);
// return;
//}
foreach (Table dbTables in db.Tables)
{
//TableSource.Where(z=>z.IsChecked).ToList().ForEach(y => {
//if (SelectedTables == null)
//{
// MessageBox.Show("No tables selected", "BP Utility", MessageBoxButton.OK);
// return;
//}
SelectedTables.ToList().ForEach(y =>
{
if (dbTables.Name == y.Name)
{
var scripter = new Scripter(server)
{
Options = { ScriptData = IsInsertStatement, Default = true, DriAll = true }
};
var scriptRows = scripter.EnumScript(new SqlSmoObject[] { dbTables });
int totalLines = scriptRows.Count();
if (!IsVO)
{
foreach (var line in scriptRows)
{
output.AppendLine(line);
}
}
else
{
//ScriptingOptions options = new ScriptingOptions
//{
//// ClusteredIndexes = true,
// Default = true,
// DriAll = true,
// // Indexes = true,
// IncludeHeaders = true,
// // ScriptData = IsInsertStatement
//};
//StringCollection scriptRows = dbTables.Script(options);
//int totalLines = scriptRows.Count;
for (int i = 0; i < totalLines; i++)
{
if (i >= 2)
{
// string scripText = scriptRows[i];
string scripText = scriptRows.ElementAt(i);
//string FormmatedString = "";
if (i == 2)
{
var scriptArray = scripText.Split('\n');
if (scriptArray.Any())
{
for (int j = 0; j < scriptArray.Count(); j++)
{
if (!String.IsNullOrEmpty(scriptArray.ElementAt(j)))
{
if (j == 0)
{
scripText = String.Format("{0} {1} {2} {1} {3}", "sStatement :=", '"', scriptArray.ElementAt(j), "+ CRLF ");
}
else
{
scripText = String.Format("{0} {1} {2} {1} {3}", "sStatement +=", '"', scriptArray.ElementAt(j), "+ CRLF ");
}
scripText = scripText.Replace(CollateString, "");
output.Append(scripText);
output.Append(Environment.NewLine);
}
}
}
else
{
scripText = String.Format("{0} {1} {2} {1} {3}", "sStatement :=", '"', scripText, "+ CRLF ");
output.Append(scripText);
output.Append(Environment.NewLine);
}
}
else
{
scripText = String.Format("{0} {1} {2} {1} {3}", "sStatement +=", '"', scripText, "+ CRLF ");
output.Append(scripText);
output.Append(Environment.NewLine);
}
}
}
output.Append("AAdd(aStatements, sStatement)");
}
output.Append(Environment.NewLine);
output.Append(Environment.NewLine);
}
});
}
return(output);
}
catch (Exception)
{
throw;
}
}
static int Main(string[] args)
{
if (args.Length < 2)
{
ShowUsage(); return(1);
}
Server sqlServer = new Server(args[0]);
Database db = default(Database);
db = sqlServer.Databases[args[1]];
string filePath = args[2];
DateTime now = DateTime.Now;
// set up text file
string filename = string.Format("{0:0000}{1:00}{2:00}{3:00}{4:00}_{5}.sql", now.Year, now.Month, now.Day, now.Hour, now.Minute, args[1]);
filename = Path.Combine(filePath, filename);
Scripter scrp = default(Scripter);
scrp = new Scripter(sqlServer);
scrp.Options.ScriptSchema = true;
scrp.Options.WithDependencies = true;
scrp.Options.ScriptData = false;
Urn[] smoObjects = new Urn[2];
int objectCount = 0;
// write each table
foreach (Table tb in db.Tables)
{
if (tb.IsSystemObject == false)
{
Console.WriteLine("Table: {0}", tb.Urn);
smoObjects = new Urn[1];
smoObjects[0] = tb.Urn;
using (StreamWriter w = File.AppendText(filename))
{
foreach (string s in scrp.EnumScript(new Urn[] { tb.Urn }))
{
w.WriteLine(s);
Console.Write(".");
objectCount++;
}
w.Close();
}
}
Console.WriteLine();
Console.Write("-Indexes: ");
// write each index
foreach (Index ix in tb.Indexes)
{
if (ix.IsSystemObject == false)
{
Console.Write(".");
objectCount++;
using (StreamWriter w = File.AppendText(filename))
{
StringCollection indexScript = ix.Script();
foreach (string s in indexScript)
{
w.WriteLine(s);
}
w.Close();
}
}
}
Console.WriteLine();
Console.Write("-Triggers: ");
// write each trigger
foreach (Trigger trig in tb.Triggers)
{
if (trig.IsSystemObject == false)
{
Console.Write(".");
objectCount++;
smoObjects = new Urn[1];
smoObjects[0] = trig.Urn;
using (StreamWriter w = File.AppendText(filename))
{
foreach (string s in scrp.EnumScript(new Urn[] { trig.Urn }))
{
w.WriteLine(s);
}
w.Close();
}
}
}
Console.WriteLine();//finished triggers
//next table
Console.WriteLine();
}
// write each view
Console.Write("Views: ");
foreach (View vw in db.Views)
{
if (vw.IsSystemObject == false)
{
Console.Write(".");
objectCount++;
smoObjects = new Urn[1];
smoObjects[0] = vw.Urn;
using (StreamWriter w = File.AppendText(filename))
{
foreach (string s in scrp.EnumScript(new Urn[] { vw.Urn }))
{
w.WriteLine(s);
}
w.Close();
}
}
}
Console.WriteLine();
Console.Write("Stored Procedures: ");
// write each stored procedure
foreach (StoredProcedure sp in db.StoredProcedures)
{
if (sp.IsSystemObject == false)
{
Console.Write(".");
objectCount++;
smoObjects = new Urn[1];
smoObjects[0] = sp.Urn;
using (StreamWriter w = File.AppendText(filename))
{
foreach (string s in scrp.EnumScript(new Urn[] { sp.Urn }))
{
w.WriteLine(s);
}
w.Close();
}
}
}
Console.WriteLine();
// write each user defined funtion
Console.Write("UserDefinedFunctions: ");
foreach (UserDefinedFunction udf in db.UserDefinedFunctions)
{
if (udf.IsSystemObject == false)
{
smoObjects = new Urn[1];
smoObjects[0] = udf.Urn;
using (StreamWriter w = File.AppendText(filename))
{
foreach (string s in scrp.EnumScript(new Urn[] { udf.Urn }))
{
w.WriteLine(s);
Console.Write(".");
}
w.Close();
}
objectCount++;
}
}
Console.WriteLine();
ReportProgress(objectCount);
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("File written: {0}", filename);
return(0);
}
public string GenerateTableData(ServerModel server, DataGenType gentype, List<TableModel> tables)
{
try
{
var output = new StringBuilder();
if (gentype == DataGenType.Truncate)
{
foreach (var t in tables)
{
var scpt = GetDataHeaderQuery(gentype, t.TableName);
output.AppendLine(scpt);
}
}
else
{
Server srv = new Server();
srv.ConnectionContext.LoginSecure = false;
srv.ConnectionContext.Login = server.Username;
srv.ConnectionContext.Password = server.Password;
srv.ConnectionContext.ServerInstance = server.ServerName;
Database genDb = srv.Databases[server.Database];
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.ScriptData = true;
scriptOptions.ScriptSchema = false;
Scripter scripter = new Scripter(srv) { Options = scriptOptions };
foreach (var t in tables)
{
var gen = new StringBuilder();
var tbl = genDb.Tables[t.TableName, "dbo"];
var script = scripter.EnumScript(new SqlSmoObject[] { tbl });
foreach (var line in script)
gen.AppendLine(line);
var scpt = GetDataHeaderQuery(gentype, t.TableName);
scpt = scpt.Replace("{query}", gen.ToString());
output.AppendLine(scpt);
}
}
return output.ToString();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
private void ScriptData()
{
if (!this.VerifyDatabase())
{
return;
}
if (this.OutputFilePath == null)
{
this.Log.LogError("OutputFilePath is required");
return;
}
this.LogTaskMessage(string.Format(CultureInfo.CurrentCulture, "Scripting Data for Database: {0} to: {1}", this.DatabaseItem.ItemSpec, this.OutputFilePath.GetMetadata("FullPath")));
Microsoft.SqlServer.Management.Smo.Database db = this.sqlServer.Databases[this.DatabaseItem.ItemSpec];
var scrp = new Scripter(this.sqlServer) { Options = { ScriptSchema = this.ScriptSchema, ScriptData = true, ScriptDrops = this.ScriptDrops } };
Regex filter = new Regex(this.RegexPattern, RegexOptions.Compiled);
if (File.Exists(this.OutputFilePath.GetMetadata("FullPath")))
{
File.Delete(this.OutputFilePath.GetMetadata("FullPath"));
System.Threading.Thread.Sleep(2000);
}
// Iterate through the tables in database and script each one. Display the script.
foreach (Table tb in db.Tables)
{
// check if the table is not a system table
if (tb.IsSystemObject == false && filter.IsMatch(tb.Name) && tb.RowCount > 0)
{
this.LogTaskMessage(string.Format(CultureInfo.CurrentCulture, "\tScripting: {0}. {1} rows", tb.Name, tb.RowCount));
var sc = scrp.EnumScript(new[] { tb.Urn });
System.IO.File.AppendAllLines(this.OutputFilePath.GetMetadata("FullPath"), sc);
}
}
}
public IEnumerable <ThreadInfo> ScriptDatabase(ThreadManager TM, string connectionString, string dbName, string destination, DateTime start, string user)
{
var currentThread = TM.CurrentThread;
yield return(TM.StartNewThread(currentThread, () => {
using (var connection = new SqlConnection(connectionString))
using (var output = new StreamWriter(destination)) {
connection.Open();
var server = new Server(new ServerConnection(connection));
var database = server.Databases[dbName];
var scripter = new Scripter(server);
scripter.Options.ScriptSchema = true;
scripter.Options.ScriptData = true;
scripter.Options.ScriptDrops = false;
scripter.Options.WithDependencies = true;
scripter.Options.Indexes = true;
scripter.Options.DriAllConstraints = true;
// Not scripting: DdlTrigger, UnresolvedEntity
var urns = new List <Urn>();
urns.AddRange(database.UserDefinedFunctions.Cast <UserDefinedFunction>().Where(f => !f.IsSystemObject).Select(f => f.Urn));
foreach (UserDefinedFunction function in database.UserDefinedFunctions)
{
if (!function.IsSystemObject)
{
urns.Add(function.Urn);
}
else
{
break;
}
}
foreach (View view in database.Views)
{
if (!view.IsSystemObject)
{
urns.Add(view.Urn);
}
else
{
break;
}
}
foreach (Table table in database.Tables)
{
if (!table.IsSystemObject)
{
urns.Add(table.Urn);
}
else
{
break;
}
}
foreach (StoredProcedure proc in database.StoredProcedures)
{
if (!proc.IsSystemObject)
{
urns.Add(proc.Urn);
}
else
{
break;
}
}
foreach (Default def in database.Defaults)
{
urns.Add(def.Urn);
}
foreach (Microsoft.SqlServer.Management.Smo.Rule rule in database.Rules)
{
urns.Add(rule.Urn);
}
foreach (Trigger trigger in database.Triggers)
{
if (!trigger.IsSystemObject)
{
urns.Add(trigger.Urn);
}
else
{
break;
}
}
foreach (UserDefinedAggregate aggregate in database.UserDefinedAggregates)
{
urns.Add(aggregate.Urn);
}
foreach (Synonym synonym in database.Synonyms)
{
urns.Add(synonym.Urn);
}
foreach (UserDefinedDataType type in database.UserDefinedDataTypes)
{
urns.Add(type.Urn);
}
foreach (XmlSchemaCollection xsc in database.XmlSchemaCollections)
{
urns.Add(xsc.Urn);
}
foreach (UserDefinedType type in database.UserDefinedTypes)
{
urns.Add(type.Urn);
}
foreach (UserDefinedTableType type in database.UserDefinedTableTypes)
{
urns.Add(type.Urn);
}
foreach (PartitionScheme scheme in database.PartitionSchemes)
{
urns.Add(scheme.Urn);
}
foreach (PartitionFunction function in database.PartitionFunctions)
{
urns.Add(function.Urn);
}
foreach (PlanGuide guide in database.PlanGuides)
{
urns.Add(guide.Urn);
}
foreach (SqlAssembly assembly in database.Assemblies)
{
if (!assembly.IsSystemObject)
{
urns.Add(assembly.Urn);
}
else
{
break;
}
}
output.WriteLine("-- Script generated at {0:MM/dd/yyyy HH:mm:ss} by {1} --", start, user);
var strings = scripter.EnumScript(urns.ToArray());
foreach (string s in strings)
{
if (s.Contains("CREATE") || s.StartsWith("SET ANSI_NULLS"))
{
output.WriteLine("GO");
}
output.WriteLine(s);
}
output.WriteLine("-- Scripting complete --");
return "Successfully scripted database.";
}
}));
yield return(TM.Return(currentThread, new JsonResponse(new MessageResponse(TM.GetResult <string>(currentThread)))));
}
public void GenerateScriptFile(string destinationPath, Action<int> percentCompleteCallback, Action<Exception> errorCallback)
{
var server = GetDbServer();
var db = server.Databases[this.DatabaseName];
var scripter = new Scripter(server);
SetScriptOptions(destinationPath, scripter);
var smoObjects = new List<Urn>();
foreach (Table tb in db.Tables)
{
if (!tb.IsSystemObject)
{
smoObjects.Add(tb.Urn);
}
}
scripter.ScriptingError += new ScriptingErrorEventHandler((s, e) =>
{
if (errorCallback != null)
{
errorCallback(e.InnerException);
}
});
scripter.ScriptingProgress += new ProgressReportEventHandler((s, e) =>
{
int percent = Convert.ToInt32(((double)e.TotalCount / (double)e.Total) * 100.0);
if (percentCompleteCallback != null)
{
percentCompleteCallback(percent);
}
});
//var sc = scripter.Script(smoObjects.ToArray());
foreach (var sc in scripter.EnumScript(smoObjects.ToArray()))
{
}
}
static void Main(string[] args)
{
Server sqlServer = new Server(args[0]);
Database db = default(Database);
db = sqlServer.Databases[args[1]];
string filePath = args[2];
// set up text file
string filename = filePath + DateTime.Now.Year.ToString() + pad(DateTime.Now.Month.ToString(), 2) + pad(DateTime.Now.Day.ToString(), 2) + pad(DateTime.Now.Hour.ToString(), 2) + pad(DateTime.Now.Minute.ToString(), 2) + "_" + args[1] + ".sql";
Scripter scrp = default(Scripter);
scrp = new Scripter(sqlServer);
scrp.Options.ScriptSchema = true;
scrp.Options.WithDependencies = true;
scrp.Options.ScriptData = false;
Urn[] smoObjects = new Urn[2];
// write each table
foreach (Table tb in db.Tables)
{
if (tb.IsSystemObject == false)
{
smoObjects = new Urn[1];
smoObjects[0] = tb.Urn;
using (StreamWriter w = File.AppendText(filename))
{
foreach (string s in scrp.EnumScript(new Urn[] { tb.Urn }))
{
w.WriteLine(s);
}
w.Close();
}
}
// write each index
foreach (Index ix in tb.Indexes)
{
if (ix.IsSystemObject == false)
{
using (StreamWriter w = File.AppendText(filename))
{
StringCollection indexScript = ix.Script();
foreach (string s in indexScript)
{
w.WriteLine(s);
}
w.Close();
}
}
}
// write each trigger
foreach (Trigger trig in tb.Triggers)
{
if (trig.IsSystemObject == false)
{
smoObjects = new Urn[1];
smoObjects[0] = trig.Urn;
using (StreamWriter w = File.AppendText(filename))
{
foreach (string s in scrp.EnumScript(new Urn[] { trig.Urn }))
{
w.WriteLine(s);
}
w.Close();
}
}
}
}
// write each view
foreach (View vw in db.Views)
{
if (vw.IsSystemObject == false)
{
smoObjects = new Urn[1];
smoObjects[0] = vw.Urn;
using (StreamWriter w = File.AppendText(filename))
{
foreach (string s in scrp.EnumScript(new Urn[] { vw.Urn }))
{
w.WriteLine(s);
}
w.Close();
}
}
}
// write each stored procedure
foreach (StoredProcedure sp in db.StoredProcedures)
{
if (sp.IsSystemObject == false)
{
smoObjects = new Urn[1];
smoObjects[0] = sp.Urn;
using (StreamWriter w = File.AppendText(filename))
{
foreach (string s in scrp.EnumScript(new Urn[] { sp.Urn }))
{
w.WriteLine(s);
}
w.Close();
}
}
}
// write each user defined funtion
foreach (UserDefinedFunction udf in db.UserDefinedFunctions)
{
if (udf.IsSystemObject == false)
{
smoObjects = new Urn[1];
smoObjects[0] = udf.Urn;
using (StreamWriter w = File.AppendText(filename))
{
foreach (string s in scrp.EnumScript(new Urn[] { udf.Urn }))
{
w.WriteLine(s);
}
w.Close();
}
}
}
}