public void ScriptJobs(Server JobServer, string ScriptFile)
{
SMOWrapper smoWrapper = new SMOWrapper();
ScriptingOptions oScriptingOptions;
oScriptingOptions = new ScriptingOptions();
oScriptingOptions.IncludeDatabaseContext = true;
oScriptingOptions.IncludeIfNotExists = false;
oScriptingOptions.ScriptDrops = false;
oScriptingOptions.IncludeDatabaseContext = true;
oScriptingOptions.DriForeignKeys = true;
oScriptingOptions.SchemaQualify = true;
oScriptingOptions.SchemaQualifyForeignKeysReferences = true;
oScriptingOptions.ScriptSchema = true;
//Since Alerts and Notifications are dependencies we script these first.
ScriptJobAlerts(JobServer, oScriptingOptions, ScriptFile);
// Script Notifications
ScriptNotifications(JobServer, oScriptingOptions, ScriptFile);
foreach (Job _Job in JobServer.JobServer.Jobs)
{
string JobScript = smoWrapper.ScriptObject <Job, ScriptingOptions, int>(_Job, oScriptingOptions, 2);
ScriptWriter(ScriptFile, JobScript);
}
}
public void ScriptTrigger(string SQLServer, string SQLDatabase, string ScriptFile)
{
// http://rajganesh-mountbatton.blogspot.com/2008/03/using-smo-to-generate-script-of.html
Server oServer = new Server(SQLServer);
Database _DB = null;
TextWriter oTextWriter = null;
SMOWrapper smoWrapper = new SMOWrapper();
ScriptingOptions oScriptingOptions;
oScriptingOptions = new ScriptingOptions();
oScriptingOptions.IncludeDatabaseContext = true;
oScriptingOptions.IncludeIfNotExists = false;
oScriptingOptions.ScriptDrops = false;
oScriptingOptions.IncludeDatabaseContext = true;
oScriptingOptions.DriForeignKeys = true;
oScriptingOptions.SchemaQualify = true;
oScriptingOptions.SchemaQualifyForeignKeysReferences = true;
oScriptingOptions.ScriptSchema = true;
try
{
_DB = oServer.Databases[SQLDatabase];
//oTextWriter = new StreamWriter(@"C:\DBScripts\DbScripts1.sql");
//if (oTextWriter != null)
//{
foreach (Table _Table in _DB.Tables)
{
TriggerCollection oTriggerCollect = _Table.Triggers;
if (oTriggerCollect.Count > 0)
{
//oSBText.Append("\n" + "--" + _DB.Name + "\n" + "--" + _Table.Name);
foreach (Trigger oTrg in oTriggerCollect)
{
try
{
string TriggerScript = smoWrapper.ScriptObject <Trigger, ScriptingOptions, int>(oTrg, oScriptingOptions, 2);
ScriptWriter(ScriptFile, TriggerScript);
}
catch (Exception Ex)
{
TextWriter oErrorWriter = new StreamWriter(@"c:\DBScripts\Error\Error.txt");
oErrorWriter.Write(Ex.Message);
oErrorWriter.Close();
}
}
}
}
} // End
catch (Exception ex)
{
throw ex;
}
}
public string Script_Indexes(Server SQLServer, string SQLDatabase, string Table, string ScriptFile)
{
// http://rajganesh-mountbatton.blogspot.com/2008/03/using-smo-to-generate-script-of.html
// Server oServer = new Server(SQLServer);
SQLServer.ConnectionContext.Connect();
Database _DB = null;
SMOWrapper smoWrapper = new SMOWrapper();
ScriptingOptions oScriptingOptions;
oScriptingOptions = new ScriptingOptions();
oScriptingOptions.IncludeDatabaseContext = true;
oScriptingOptions.IncludeIfNotExists = false;
oScriptingOptions.ScriptDrops = false;
// oScriptingOptions.
StringBuilder SWScript = new StringBuilder();
try
{
_DB = SQLServer.Databases[SQLDatabase];
//oTextWriter = new StreamWriter(@"C:\DBScripts\DbScripts1.sql");
//if (oTextWriter != null)
//{
foreach (Index _IDX in _DB.Tables[Table].Indexes)
{
try
{
string IndexScript = smoWrapper.ScriptObject <Index, ScriptingOptions, int>(_IDX, oScriptingOptions, 2);
if (!(string.IsNullOrEmpty(ScriptFile)))
{
ScriptWriter(ScriptFile, IndexScript);
}
else
{
SWScript.AppendLine(IndexScript);
}
}
catch (Exception Ex)
{
TextWriter oErrorWriter = new StreamWriter(@"c:\DBScripts\Error\Error.txt");
oErrorWriter.Write(Ex.Message);
oErrorWriter.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
return(SWScript.ToString());
}
private void ScriptNotifications(Server _JobServer, ScriptingOptions so, string ScriptFile)
{
SMOWrapper smoWrapper = new SMOWrapper();
string _ScriptFile = ScriptFile.Replace("Jobs", "Operators");
if (!(System.IO.Directory.Exists(System.IO.Path.GetDirectoryName(_ScriptFile))))
{
System.IO.Directory.CreateDirectory(System.IO.Path.GetDirectoryName(_ScriptFile));
}
foreach (Operator _Operator in _JobServer.JobServer.Operators)
{
string OperatorScript = smoWrapper.ScriptObject <Job, ScriptingOptions, int>(_Operator, so, 2);
ScriptWriter(_ScriptFile, OperatorScript);
}
}
private void ScriptJobAlerts(Server _JobServer, ScriptingOptions so, string ScriptFile)
{
SMOWrapper smoWrapper = new SMOWrapper();
string _ScriptFile = ScriptFile.Replace("Jobs", "Alerts");
if (!(System.IO.Directory.Exists(System.IO.Path.GetDirectoryName(_ScriptFile))))
{
System.IO.Directory.CreateDirectory(System.IO.Path.GetDirectoryName(_ScriptFile));
}
foreach (Alert _Alert in _JobServer.JobServer.Alerts)
{
string AlertScript = smoWrapper.ScriptObject <Job, ScriptingOptions, int>(_Alert, so, 2);
ScriptWriter(_ScriptFile, AlertScript);
}
}
public void ScriptUDFs(string SQLServer, string SQLDatabase, string ScriptFile)
{
// http://rajganesh-mountbatton.blogspot.com/2008/03/using-smo-to-generate-script-of.html
Server oServer = new Server(SQLServer);
Database _DB = null;
TextWriter oTextWriter = null;
SMOWrapper smoWrapper = new SMOWrapper();
ScriptingOptions oScriptingOptions = new ScriptingOptions();
oScriptingOptions.IncludeDatabaseContext = true;
oScriptingOptions.IncludeIfNotExists = false;
oScriptingOptions.ScriptDrops = false;
oScriptingOptions.IncludeDatabaseContext = true;
oScriptingOptions.DriForeignKeys = true;
oScriptingOptions.SchemaQualify = true;
oScriptingOptions.SchemaQualifyForeignKeysReferences = true;
oScriptingOptions.ScriptSchema = true;
RemoveScriptFileIfExists(ScriptFile);
_DB = oServer.Databases[SQLDatabase];
foreach (UserDefinedFunction _udf in _DB.UserDefinedFunctions)
{
try
{
if (!_udf.IsSystemObject)
{
string TriggerScript = smoWrapper.ScriptObject <UserDefinedFunction, ScriptingOptions, int>(_udf, oScriptingOptions, 2);
ScriptWriter(ScriptFile, TriggerScript);
}
}
catch (Exception Ex)
{
TextWriter oErrorWriter = new StreamWriter(@"c:\DBScripts\Error\Error.txt");
string UDFError = string.Format("{0} - {1} " + Ex.Message, SQLServer, SQLDatabase);
oErrorWriter.Write(UDFError);
oErrorWriter.Close();
}
}
}
public void Script_Logins(string SQLServer, string SQLDatabase, string ScriptFile)
{
Server oServer = new Server(SQLServer);
Database _DB = null;
SMOWrapper smoWrapper = new SMOWrapper();
ScriptingOptions oScriptingOptions = new ScriptingOptions();
oScriptingOptions.IncludeDatabaseContext = true;
oScriptingOptions.IncludeIfNotExists = false;
oScriptingOptions.ScriptDrops = false;
oScriptingOptions.IncludeDatabaseContext = true;
oScriptingOptions.DriForeignKeys = true;
oScriptingOptions.SchemaQualify = true;
oScriptingOptions.SchemaQualifyForeignKeysReferences = true;
oScriptingOptions.ScriptSchema = true;
RemoveScriptFileIfExists(ScriptFile);
_DB = oServer.Databases[SQLDatabase];
foreach (Login _login in oServer.Logins)
{
try
{
if (!_login.IsSystemObject)
{
string UserScript = smoWrapper.ScriptObject <Login, ScriptingOptions, int>
(_login, oScriptingOptions, 2);
ScriptWriter(ScriptFile, UserScript);
}
}
catch (Exception Ex)
{
throw Ex;
//TextWriter oErrorWriter = new StreamWriter(@"c:\DBScripts\Error\Error.txt");
//string UDFError = string.Format("{0} - {1} " + Ex.Message, SQLServer, SQLDatabase);
//oErrorWriter.Write(UDFError);
//oErrorWriter.Close();
}
}
}
public void ScriptStoredProcedures(string SQLServer, string SQLDatabase, string ScriptFile)
{
Server oServer = new Server(SQLServer);
Database _DB = null;
SMOWrapper smoWrapper = new SMOWrapper();
ScriptingOptions oScriptingOptions = new ScriptingOptions();
oScriptingOptions.IncludeDatabaseContext = true;
oScriptingOptions.IncludeIfNotExists = false;
oScriptingOptions.ScriptDrops = false;
oScriptingOptions.IncludeDatabaseContext = true;
oScriptingOptions.DriForeignKeys = true;
oScriptingOptions.SchemaQualify = true;
oScriptingOptions.SchemaQualifyForeignKeysReferences = true;
oScriptingOptions.ScriptSchema = true;
_DB = oServer.Databases[SQLDatabase];
foreach (StoredProcedure _sp in _DB.StoredProcedures)
{
try
{
if (!_sp.IsSystemObject)
{
string TriggerScript = smoWrapper.ScriptObject <StoredProcedure, ScriptingOptions, int>(_sp, oScriptingOptions, 2);
ScriptWriter(ScriptFile, TriggerScript);
}
}
catch (Exception Ex)
{
TextWriter oErrorWriter = new StreamWriter(@"c:\DBScripts\Error\Error.txt");
string UDFError = string.Format("{0} - {1} " + Ex.Message, SQLServer, SQLDatabase);
oErrorWriter.Write(UDFError);
oErrorWriter.Close();
}
}
}
public string ScriptUnTrustedKeys(List <TableAndFKey> TblFkey, string SQLSrvrInstance)
{
ServerConnection SQLSrvrConnection = new ServerConnection(SQLSrvrInstance);
System.Collections.Specialized.StringCollection script = new StringCollection();
// Recreate connection if necessary
if (SQLSrvrInstance == null)
{
SQLSrvrConnection = new ServerConnection();
}
if (string.IsNullOrEmpty(this.FileName))
{
System.Windows.Forms.MessageBox.Show("No file name give for the output script.", "Script Foreign Keys.", System.Windows.Forms.MessageBoxButtons.OK);
}
// Fill in necessary information
SQLSrvrConnection.ServerInstance = SQLSrvrInstance;
// Setup capture and execute to be able to display script
SQLSrvrConnection.SqlExecutionModes = SqlExecutionModes.ExecuteAndCaptureSql;
// Use Windows authentication
SQLSrvrConnection.LoginSecure = true;
// Go ahead and connect
SQLSrvrConnection.Connect();
Server srv = new Server(SQLSrvrConnection);
ScriptingOptions oScriptingOptions;
string ScriptedFK = string.Empty;
SMOWrapper smoWrapper = new SMOWrapper();
if (System.IO.File.Exists(FileName))
{
System.IO.File.Delete(FileName);
}
Londeck.Net.Logging.LogFile _LogFile = new Londeck.Net.Logging.LogFile(FileName, true, 0);
try
{
foreach (TableAndFKey _TableFKey in TblFkey)
{
// ***** Make sure that the Database is correct in srv.Databases[""]
// Set up the Foreign Key Drop script
oScriptingOptions = new ScriptingOptions();
oScriptingOptions.IncludeDatabaseContext = true;
oScriptingOptions.IncludeIfNotExists = true;
oScriptingOptions.ScriptDrops = true;
oScriptingOptions.IncludeDatabaseContext = true;
oScriptingOptions.DriForeignKeys = true;
oScriptingOptions.SchemaQualify = true;
oScriptingOptions.SchemaQualifyForeignKeysReferences = true;
oScriptingOptions.ScriptSchema = true;
ForeignKey fk = srv.Databases[_TableFKey.DBName].Tables[_TableFKey.TableName, "dbo"].ForeignKeys[_TableFKey.FKeyName];
//script = fk.Script(oScriptingOptions);
ScriptedFK = smoWrapper.ScriptObject <ForeignKey, ScriptingOptions, int>(fk, oScriptingOptions, 0);
ScriptedFK += Environment.NewLine;
oScriptingOptions = null;
// Set up the Create Foreign Key script
oScriptingOptions = new ScriptingOptions();
oScriptingOptions.IncludeDatabaseContext = true;
ScriptedFK += smoWrapper.ScriptObject <ForeignKey, ScriptingOptions, int>(fk, oScriptingOptions, 2);
_LogFile.WriteLogMessage(ScriptedFK);
_LogFile.WriteBlankLines(1);
ScriptedFK = string.Empty;
}
_LogFile.CloseLogFile(true);
}
catch (NullReferenceException ex)
{
_LogFile.CloseLogFile(true);
throw ex;
}
catch (Exception ex)
{
_LogFile.CloseLogFile(true);
throw ex;
}
return(FileName);
}
