public static void UpdateDataSet(this MSMO.UserDefinedFunction userDefinedFunction, Data.ApplicationDataSet.DBUserDefinedFunctionsRow dataRow)
{
try
{
//dataRow.X = userDefinedFunction.X;
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 8);
// TODO(crhodes):
// Wrap anything above that throws an exception that we want to ignore,
// e.g. property not available because of SQL Edition.
}
}
public static void UpdateDataSet(this MSMO.UserDefinedFunction userDefinedFunction, Data.ApplicationDataSet.DBUserDefinedFunctionsRow userDefinedFunctionRow)
{
try
{
//instanceRow.ServiceName = server.ServiceName;
}
catch (Exception ex)
{
// TODO(crhodes): Need to wrap anything above that throws an exception
// that we want to ignore, e.g. property not available because of
// SQL Edition.
PLLog.Error(ex, PLLOG_APPNAME, CLASS_BASE_ERRORNUMBER + 2);
throw ex;
}
}
private static void Update(MSMO.UserDefinedFunction userDefinedFunction, SQLInformation.Data.ApplicationDataSet.DBUserDefinedFunctionsRow dataRow)
{
try
{
userDefinedFunction.UpdateDataSet(dataRow);
UpdateDatabaseWithSnapShot(dataRow, "");
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 7);
UpdateDatabaseWithSnapShot(dataRow, ex.ToString().Substring(0, 256));
}
}
public MySmo.UserDefinedFunction GetUserDefinedFunction(Smo.UserDefinedFunction smo_f, MySmo.Database parent = null)
{
#region implement
SetDataLimit();
var mysmo_f = new MySmo.UserDefinedFunction();
mysmo_f.ParentDatabase = parent;
mysmo_f.Name = smo_f.Name;
mysmo_f.Schema = smo_f.Schema;
mysmo_f.CreateTime = smo_f.CreateDate;
mysmo_f.Owner = smo_f.Owner;
mysmo_f.ExtendedProperties = GetExtendProperties(mysmo_f, smo_f.ExtendedProperties);
var s = "";
if (mysmo_f.ExtendedProperties.TryGetValue(K_MS_Description, out s))
{
mysmo_f.Description = s;
mysmo_f.ExtendedProperties.Remove(K_MS_Description);
}
mysmo_f.Parameters = new List <MySmo.Parameter>();
foreach (Smo.UserDefinedFunctionParameter smo_p in smo_f.Parameters)
{
var mysmo_p = new MySmo.Parameter
{
ParentDatabase = parent,
ParentParameterBase = mysmo_f,
Name = smo_p.Name.Substring(1),
DefaultValue = smo_p.DefaultValue,
IsOutputParameter = false,
IsReadOnly = smo_p.IsReadOnly,
DataType = new MySmo.DataType
{
Name = smo_p.DataType.Name,
Schema = smo_p.DataType.Schema,
MaximumLength = smo_p.DataType.MaximumLength,
NumericPrecision = smo_p.DataType.NumericPrecision,
NumericScale = smo_p.DataType.NumericScale,
SqlDataType = (MySmo.SqlDataType)(int) smo_p.DataType.SqlDataType
}
};
mysmo_f.Parameters.Add(mysmo_p);
}
if (smo_f.FunctionType == Smo.UserDefinedFunctionType.Table)
{
mysmo_f.Columns = new List <MySmo.Column>();
foreach (Smo.Column smo_c in smo_f.Columns)
{
var mysmo_c = new MySmo.Column
{
ParentDatabase = parent,
ParentTableBase = mysmo_f,
Name = smo_c.Name,
DataType = GetDataType(smo_c),
Computed = smo_c.Computed,
ComputedText = smo_c.ComputedText,
Default = smo_c.Default,
Identity = smo_c.Identity,
IdentityIncrement = smo_c.IdentityIncrement,
IdentitySeed = smo_c.IdentitySeed,
InPrimaryKey = smo_c.InPrimaryKey,
IsForeignKey = smo_c.IsForeignKey,
Nullable = smo_c.Nullable,
RowGuidCol = smo_c.RowGuidCol
};
mysmo_f.Columns.Add(mysmo_c);
}
}
FormatExtendProperties(mysmo_f);
return(mysmo_f);
#endregion
}
public void ExtractScript(SQLObjectName oname, SQLScripts SQLScriptsCollection, bool Verbose)
{
// Store extracted scripts. Each extract may include multiple scripts.
StringCollection OutputScripts = new StringCollection();
string FinalScript = String.Empty;
switch (oname.ObjectType)
{
case SQLObjectType.Table:
Microsoft.SqlServer.Management.Smo.Table scriptTable = connDatabase.Tables[oname.Name, oname.Schema];
if (scriptTable != null)
{
StringCollection CheckScripts = new StringCollection(); // Store scripts to be checked
String TableScript = String.Empty; // Stores individual script for output collection.
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.DriAll = true;
scriptOptions.Statistics = true;
scriptOptions.ClusteredIndexes = true;
scriptOptions.NonClusteredIndexes = true;
scriptOptions.DriAllConstraints = true;
scriptOptions.WithDependencies = false;
// Get table and related scripts
CheckScripts = scriptTable.Script(scriptOptions);
// Check scripts so we can remove invalide SQL 2012 column store options from the script.
// (Why doesn't the target server version remove this?
// This is a crappy place to do this, and it's version specific.
// Need to implement the new versioning code to check target model.
foreach (string CheckCCI in CheckScripts)
{
if (CheckCCI.Contains(", DATA_COMPRESSION = COLUMNSTORE"))
{
TableScript = CheckCCI.Replace(", DATA_COMPRESSION = COLUMNSTORE", "");
}
else
{
TableScript = CheckCCI;
}
// Add the script into the OutputScripts collection.
OutputScripts.Add(TableScript);
}
}
break;
case SQLObjectType.View:
Microsoft.SqlServer.Management.Smo.View scriptView = connDatabase.Views[oname.Name, oname.Schema];
if (scriptView != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.DriAll = true;
scriptOptions.ClusteredIndexes = true;
scriptOptions.NonClusteredIndexes = true;
scriptOptions.WithDependencies = false;
// Must specify tables seperatly, but safer to do so
// to avoid having duplicate table names in the model.
OutputScripts = scriptView.Script(scriptOptions);
}
break;
case SQLObjectType.StoredProcedure:
Microsoft.SqlServer.Management.Smo.StoredProcedure scriptStoredProcedure = connDatabase.StoredProcedures[oname.Name, oname.Schema];
if (scriptStoredProcedure != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = scriptStoredProcedure.Script(scriptOptions);
}
break;
case SQLObjectType.PartitionScheme:
{
Microsoft.SqlServer.Management.Smo.PartitionScheme scriptPScheme = connDatabase.PartitionSchemes[oname.Name];
if (scriptPScheme != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = scriptPScheme.Script(scriptOptions);
}
}
break;
case SQLObjectType.PartitionFunction:
{
Microsoft.SqlServer.Management.Smo.PartitionFunction scriptPFunction = connDatabase.PartitionFunctions[oname.Name];
if (scriptPFunction != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = scriptPFunction.Script(scriptOptions);
}
}
break;
case SQLObjectType.Schema:
{
Microsoft.SqlServer.Management.Smo.Schema scriptSchema = connDatabase.Schemas[oname.Name];
if (scriptSchema != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
scriptOptions.ScriptOwner = true; // This includes the "with authorize" part.
OutputScripts = scriptSchema.Script(scriptOptions);
}
}
break;
case SQLObjectType.FileGroup:
{
Microsoft.SqlServer.Management.Smo.FileGroup scriptFG = connDatabase.FileGroups[oname.Name];
if (scriptFG != null)
{
// Create manual script for FileGroups
OutputScripts.Add("ALTER DATABASE [$(DatabaseName)] ADD FILEGROUP " + scriptFG.Name);
}
}
break;
case SQLObjectType.User:
{
Microsoft.SqlServer.Management.Smo.User scriptUser = connDatabase.Users[oname.Name];
if (scriptUser != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = scriptUser.Script(scriptOptions);
}
}
break;
case SQLObjectType.Function:
Microsoft.SqlServer.Management.Smo.UserDefinedFunction userDefinedFunction = connDatabase.UserDefinedFunctions[oname.Name, oname.Schema];
if (userDefinedFunction != null)
{
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.WithDependencies = false;
OutputScripts = userDefinedFunction.Script(scriptOptions);
}
break;
}
if (OutputScripts.Count > 0)
{
Console.WriteLine("Extracted SQL script: (" + oname.ObjectType.ToString() + ") " + ((oname.Schema != String.Empty) ? oname.Schema + "." + oname.Name : oname.Name));
foreach (string script in OutputScripts)
{
// Add the script to the script collection.
FinalScript = FinalScript + script + Environment.NewLine + "GO" + Environment.NewLine;
}
}
else
{
Console.WriteLine("Warning - Could not retrieve: (" + oname.ObjectType.ToString() + ") " + ((oname.Schema != String.Empty) ? oname.Schema + "." + oname.Name : oname.Name));
FinalScript = String.Empty;
}
if (FinalScript != String.Empty)
{
SQLScriptsCollection.Scripts.Add(FinalScript);
}
else
{
SQLScriptsCollection.MissingScripts.Add("Missing SQL object: (" + oname.ObjectType.ToString() + ") " + ((oname.Schema != String.Empty) ? oname.Schema + "." + oname.Name : oname.Name));
}
// Print script(s) if verbose is on.
if (Verbose)
{
Console.WriteLine(FinalScript);
}
}
private static SQLInformation.Data.ApplicationDataSet.DBUserDefinedFunctionsRow Add(Guid databaseID, MSMO.UserDefinedFunction userDefinedFunction)
{
SQLInformation.Data.ApplicationDataSet.DBUserDefinedFunctionsRow dataRow = null;
try
{
dataRow = Common.ApplicationDataSet.DBUserDefinedFunctions.NewDBUserDefinedFunctionsRow();
dataRow.ID = Guid.NewGuid();
dataRow.Database_ID = databaseID;
dataRow.IsSystemObject = userDefinedFunction.IsSystemObject;
dataRow.Name_UserDefinedFunction = userDefinedFunction.Name;
dataRow.CreateDate = userDefinedFunction.CreateDate;
try
{
dataRow.DateLastModified = userDefinedFunction.DateLastModified;
}
catch (Exception ex)
{
#if TRACE
VNC.AppLog.Debug(ex.ToString(), LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 5);
#endif
}
dataRow.SnapShotDate = DateTime.Now;
dataRow.SnapShotError = "";
Common.ApplicationDataSet.DBUserDefinedFunctions.AddDBUserDefinedFunctionsRow(dataRow);
Common.ApplicationDataSet.DBUserDefinedFunctionsTA.Update(Common.ApplicationDataSet.DBUserDefinedFunctions);
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 6);
// TODO(crhodes):
// Wrap anything above that throws an exception that we want to ignore,
// e.g. property not available because of SQL Edition.
UpdateDatabaseWithSnapShot(dataRow, ex.ToString().Substring(0, 256));
}
return(dataRow);
}
private static SQLInformation.Data.ApplicationDataSet.DBUserDefinedFunctionsRow GetInfoFromSMO(MSMO.UserDefinedFunction userDefinedFunction, Guid databaseID)
{
#if TRACE
long startTicks = VNC.AppLog.Trace4("Enter", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 2);
#endif
SQLInformation.Data.ApplicationDataSet.DBUserDefinedFunctionsRow dataRow = null;
try
{
var dbs = from tb in Common.ApplicationDataSet.DBUserDefinedFunctions
where tb.Database_ID == databaseID
select tb;
var dbs2 = from db2 in dbs
where db2.Name_UserDefinedFunction == userDefinedFunction.Name
select db2;
if (dbs2.Count() > 0)
{
dataRow = dbs2.First();
Update(userDefinedFunction, dataRow);
}
else
{
dataRow = Add(databaseID, userDefinedFunction);
}
}
catch (Exception ex)
{
VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 3);
}
#if TRACE
VNC.AppLog.Trace4("Exit", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 4, startTicks);
#endif
return(dataRow);
}
public void Script(string srvname, string dbName, string destination)
{
tbxOutput.Text = "Scripting the " + dbName + " database." + "\r\n";
if (destination == "")
{
destination = Environment.CurrentDirectory + "\\";
}
else
{
if (destination[destination.Length - 1] != Convert.ToChar("\\"))
{
destination += "\\";
}
}
tbxOutput.Text += "Output directory set to " + destination + "\r\n";
/* *************************** */
/* CHECK FOR VALID DESTINATION */
/* *************************** */
tbxOutput.Text += "Checking for valid destination directory...\r\n";
if (!Directory.Exists(destination))
{
throw new DirectoryNotFoundException("The specified destination directory does not exist.");
}
else
{
tbxOutput.Text += "Destination directory is valid.\r\n";
/* *********************** */
/* CREATE FOLDER STRUCTURE */
/* *********************** */
tbxOutput.Text += "Establishing folder structure...\r\n";
newFolders.Clear();
try
{
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd")))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd"));
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd"));
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + "\r\n";
}
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\" + "\r\n";
}
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\" + "\r\n";
}
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\" + "\r\n";
}
if (!Directory.Exists(destination + dbName + @"\Programmability\Functions\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\" + "\r\n";
}
}
catch
{
throw new UnauthorizedAccessException("The program failed to create the backup folders in the specified directory. Please check security settings.");
}
tbxOutput.Text += "Folder structure established \r\n";
}
/* *************** */
/* Generate Script */
/* *************** */
try //Wrap in try statement to catch incorrect server errors
{
tbxOutput.Text += "Connecting to server " + srvname + "...\r\n";
Server srv;
srv = new Server(srvname);
srv.ConnectionContext.LoginSecure = true;
if (!srv.Databases.Contains(dbName))
{
RemoveFolders();//Clean out folders creating during this run
throw new ArgumentException("The specified database could not be found.");
}
Database db = new Database();
db = srv.Databases[dbName];
Scripter scr = new Scripter(srv);
Scripter scrFullScript = new Scripter(srv);
srv.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");
/* Create Options for the scr Scripter */
ScriptingOptions options = new ScriptingOptions();
options.IncludeHeaders = true;
options.AppendToFile = false;
options.ToFileOnly = true;
options.DriAll = true;
options.IncludeDatabaseContext = true;
//options.ScriptDrops = true;
scr.Options = options; //Assign options to scr
/* Create options for the scrFullScript Scripter */
ScriptingOptions scopFull = new ScriptingOptions();
scopFull.IncludeHeaders = true;
scopFull.AppendToFile = true;
scopFull.ToFileOnly = true;
scopFull.DriAll = true;
scopFull.IncludeDatabaseContext = true;
scopFull.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\"
+ dbName + "_FULL.sql";
scrFullScript.Options = scopFull; //Assign options to scrFullScript
/* ******************* */
/* CREATE SCRIPT FILES */
/* ******************* */
List<string> lstErrors = new List<string>();
//SCRIPT DATABASE
Microsoft.SqlServer.Management.Smo.Database[] dbs = new Microsoft.SqlServer.Management.Smo.Database[1];
tbxOutput.Text += "Scripting Database: " + db + "\r\n";
dbs[0] = db;
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\"
+ dbName + ".sql";
scr.Script(dbs);
scrFullScript.Script(dbs);
tbxOutput.Text += "Scripting Database Complete.\r\n";
//SCRIPT TABLES
Microsoft.SqlServer.Management.Smo.Table[] tbl = new Microsoft.SqlServer.Management.Smo.Table[1];
tbxOutput.Text += "Scripting Tables...\r\n";
for (int idx = 0; idx < db.Tables.Count; idx++)
{
if (!db.Tables[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting Table: " + db.Tables[idx] + "\r\n";
tbl[0] = db.Tables[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\"
+ tbl[0].Name + ".sql";
scr.Script(tbl);
scrFullScript.Script(tbl);
}
}
tbxOutput.Text += "Scripting Tables Complete.\r\n";
//SCRIPT VIEWS
Microsoft.SqlServer.Management.Smo.View[] vw = new Microsoft.SqlServer.Management.Smo.View[1];
tbxOutput.Text += "Scripting Views...\r\n";
for (int idx = 0; idx < db.Views.Count; idx++)
{
if (!db.Views[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting View: " + db.Views[idx] + "\r\n";
vw[0] = db.Views[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\"
+ vw[0].Name + ".sql";
scr.Script(vw);
scrFullScript.Script(vw);
}
}
tbxOutput.Text += "Scripting Views Complete.\r\n";
//SCRIPT STORED PROCEDURES
Microsoft.SqlServer.Management.Smo.StoredProcedure[] proc = new Microsoft.SqlServer.Management.Smo.StoredProcedure[1];
tbxOutput.Text += "Scripting Stored Procedures...\r\n";
for (int idx = 0; idx < db.StoredProcedures.Count; idx++)
{
if (!db.StoredProcedures[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting Stored Procedure: " + db.StoredProcedures[idx] + "\r\n";
proc[0] = db.StoredProcedures[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\"
+ proc[0].Name + ".sql";
scr.Script(proc);
scrFullScript.Script(proc);
}
}
tbxOutput.Text += "Scripting Stored Procedures Complete.\r\n";
//SCRIPT FUNCTIONS
Microsoft.SqlServer.Management.Smo.UserDefinedFunction[] udf = new Microsoft.SqlServer.Management.Smo.UserDefinedFunction[1];
tbxOutput.Text += "Scripting User Defined Functions...\r\n";
for (int idx = 0; idx < db.UserDefinedFunctions.Count; idx++)
{
if (!db.UserDefinedFunctions[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting User Defined Function: " + db.UserDefinedFunctions[idx] + "\r\n";
udf[0] = db.UserDefinedFunctions[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\"
+ udf[0].Name + ".sql";
scr.Script(udf);
scrFullScript.Script(udf);
}
}
tbxOutput.Text += "Scripting User Defined Functions complete.\r\n";
tbxOutput.Text += "Scripting master file...\r\n";
try
{
String strFullScript = "";
String strFullOutput = "";
tbxOutput.Text += "Retrieving full script...\r\n";
using (StreamReader sr = new StreamReader(scopFull.FileName))
{
strFullScript = sr.ReadToEnd();
}
tbxOutput.Text += "Full script retrieved.\r\n";
//strFullOutput = strFullScript;//Temporary
string[] arrFullScript = Regex.Split(strFullScript, "GO");
foreach (string line in arrFullScript)
{
if(!line.StartsWith("\r\nALTER TABLE"))
strFullOutput += line + "GO\r\n";
}
foreach (string line in arrFullScript)
{
if (line.StartsWith("\r\nALTER TABLE"))
strFullOutput += line + "GO\r\n";
}
string strConditionalDrop = "\r\n\r\nIF DB_ID('" + dbName + "') IS NOT NULL\r\nBEGIN\r\n"
+ " ALTER DATABASE " + dbName + "\r\n"
+ " SET SINGLE_USER\r\n"
+ " WITH ROLLBACK IMMEDIATE;\r\n"
+ " DROP DATABASE " + dbName + ";\r\n"
+ "END\r\n";
strFullOutput = strFullOutput.Insert(strFullOutput.IndexOf("GO") + 2, strConditionalDrop);
tbxOutput.Text += "Writing corrected full script...\r\n";
using (StreamWriter sw = new StreamWriter(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\" + dbName + "_FULL.sql"))
{
sw.Write(strFullOutput);
}
tbxOutput.Text += "Full script successfully written.\r\n";
tbxOutput.Text += "Scripting master file complete.\r\n";
}
catch
{
tbxOutput.Text += "ERROR Scripting Master File Failed.\r\n";
lstErrors.Add("Scripting Master File Failed.");
}
tbxOutput.Text += "=================================\r\n";
if (lstErrors.Count == 0)
tbxOutput.Text += "SCRIPTING COMPLETED SUCCESSFULLY.\r\n";
else
{
tbxOutput.Text += "SCRIPTING COMPLETED WITH ERRORS.\r\n";
tbxOutput.Text += String.Format("The following {0} errors occurred:\r\n", lstErrors.Count);
foreach (string error in lstErrors)
{
tbxOutput.Text += error + "\r\n";
}
}
ActiveControl = btnClose;
}
catch (ConnectionFailureException) //Error type thrown by attempt to bind invalid server name
{
//throw new ConnectionFailureException("A connection to the specified server could not be made. Please check the supplied server name and try again.");
tbxOutput.Text += "Connection to server failed.\r\n";
RemoveFolders();//Clean out folders creating during this run
tbxOutput.Text += "A connection to the specified server could not be made. Please check the supplied server name and try again.\r\n";
}
catch //General Catch-All re-throws error without further handling
{
RemoveFolders();//Clean out folders creating during this run
throw;
}
}
public void Script(string srvname, string dbName, string destination)
{
tbxOutput.Text = "Scripting the " + dbName + " database." + "\r\n";
if (destination == "")
{
destination = Environment.CurrentDirectory + "\\";
}
else
{
if (destination[destination.Length - 1] != Convert.ToChar("\\"))
{
destination += "\\";
}
}
tbxOutput.Text += "Output directory set to " + destination + "\r\n";
/* *************************** */
/* CHECK FOR VALID DESTINATION */
/* *************************** */
tbxOutput.Text += "Checking for valid destination directory...\r\n";
if (!Directory.Exists(destination))
{
throw new DirectoryNotFoundException("The specified destination directory does not exist.");
}
else
{
tbxOutput.Text += "Destination directory is valid.\r\n";
/* *********************** */
/* CREATE FOLDER STRUCTURE */
/* *********************** */
tbxOutput.Text += "Establishing folder structure...\r\n";
newFolders.Clear();
try
{
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd")))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd"));
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd"));
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + "\r\n";
}
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\" + "\r\n";
}
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\" + "\r\n";
}
if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\" + "\r\n";
}
if (!Directory.Exists(destination + dbName + @"\Programmability\Functions\"))
{
Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\");
newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\");
tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\" + "\r\n";
}
}
catch
{
throw new UnauthorizedAccessException("The program failed to create the backup folders in the specified directory. Please check security settings.");
}
tbxOutput.Text += "Folder structure established \r\n";
}
/* *************** */
/* Generate Script */
/* *************** */
try //Wrap in try statement to catch incorrect server errors
{
tbxOutput.Text += "Connecting to server " + srvname + "...\r\n";
Server srv;
srv = new Server(srvname);
srv.ConnectionContext.LoginSecure = true;
if (!srv.Databases.Contains(dbName))
{
RemoveFolders();//Clean out folders creating during this run
throw new ArgumentException("The specified database could not be found.");
}
Database db = new Database();
db = srv.Databases[dbName];
Scripter scr = new Scripter(srv);
Scripter scrFullScript = new Scripter(srv);
srv.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");
/* Create Options for the scr Scripter */
ScriptingOptions options = new ScriptingOptions();
options.IncludeHeaders = true;
options.AppendToFile = false;
options.ToFileOnly = true;
options.DriAll = true;
options.IncludeDatabaseContext = true;
//options.ScriptDrops = true;
scr.Options = options; //Assign options to scr
/* Create options for the scrFullScript Scripter */
ScriptingOptions scopFull = new ScriptingOptions();
scopFull.IncludeHeaders = true;
scopFull.AppendToFile = true;
scopFull.ToFileOnly = true;
scopFull.DriAll = true;
scopFull.IncludeDatabaseContext = true;
scopFull.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\"
+ dbName + "_FULL.sql";
scrFullScript.Options = scopFull; //Assign options to scrFullScript
/* ******************* */
/* CREATE SCRIPT FILES */
/* ******************* */
List <string> lstErrors = new List <string>();
//SCRIPT DATABASE
Microsoft.SqlServer.Management.Smo.Database[] dbs = new Microsoft.SqlServer.Management.Smo.Database[1];
tbxOutput.Text += "Scripting Database: " + db + "\r\n";
dbs[0] = db;
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\"
+ dbName + ".sql";
scr.Script(dbs);
scrFullScript.Script(dbs);
tbxOutput.Text += "Scripting Database Complete.\r\n";
//SCRIPT TABLES
Microsoft.SqlServer.Management.Smo.Table[] tbl = new Microsoft.SqlServer.Management.Smo.Table[1];
tbxOutput.Text += "Scripting Tables...\r\n";
for (int idx = 0; idx < db.Tables.Count; idx++)
{
if (!db.Tables[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting Table: " + db.Tables[idx] + "\r\n";
tbl[0] = db.Tables[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\"
+ tbl[0].Name + ".sql";
scr.Script(tbl);
scrFullScript.Script(tbl);
}
}
tbxOutput.Text += "Scripting Tables Complete.\r\n";
//SCRIPT VIEWS
Microsoft.SqlServer.Management.Smo.View[] vw = new Microsoft.SqlServer.Management.Smo.View[1];
tbxOutput.Text += "Scripting Views...\r\n";
for (int idx = 0; idx < db.Views.Count; idx++)
{
if (!db.Views[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting View: " + db.Views[idx] + "\r\n";
vw[0] = db.Views[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\"
+ vw[0].Name + ".sql";
scr.Script(vw);
scrFullScript.Script(vw);
}
}
tbxOutput.Text += "Scripting Views Complete.\r\n";
//SCRIPT STORED PROCEDURES
Microsoft.SqlServer.Management.Smo.StoredProcedure[] proc = new Microsoft.SqlServer.Management.Smo.StoredProcedure[1];
tbxOutput.Text += "Scripting Stored Procedures...\r\n";
for (int idx = 0; idx < db.StoredProcedures.Count; idx++)
{
if (!db.StoredProcedures[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting Stored Procedure: " + db.StoredProcedures[idx] + "\r\n";
proc[0] = db.StoredProcedures[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\"
+ proc[0].Name + ".sql";
scr.Script(proc);
scrFullScript.Script(proc);
}
}
tbxOutput.Text += "Scripting Stored Procedures Complete.\r\n";
//SCRIPT FUNCTIONS
Microsoft.SqlServer.Management.Smo.UserDefinedFunction[] udf = new Microsoft.SqlServer.Management.Smo.UserDefinedFunction[1];
tbxOutput.Text += "Scripting User Defined Functions...\r\n";
for (int idx = 0; idx < db.UserDefinedFunctions.Count; idx++)
{
if (!db.UserDefinedFunctions[idx].IsSystemObject)
{
tbxOutput.Text += "Scripting User Defined Function: " + db.UserDefinedFunctions[idx] + "\r\n";
udf[0] = db.UserDefinedFunctions[idx];
options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\"
+ udf[0].Name + ".sql";
scr.Script(udf);
scrFullScript.Script(udf);
}
}
tbxOutput.Text += "Scripting User Defined Functions complete.\r\n";
tbxOutput.Text += "Scripting master file...\r\n";
try
{
String strFullScript = "";
String strFullOutput = "";
tbxOutput.Text += "Retrieving full script...\r\n";
using (StreamReader sr = new StreamReader(scopFull.FileName))
{
strFullScript = sr.ReadToEnd();
}
tbxOutput.Text += "Full script retrieved.\r\n";
//strFullOutput = strFullScript;//Temporary
string[] arrFullScript = Regex.Split(strFullScript, "GO");
foreach (string line in arrFullScript)
{
if (!line.StartsWith("\r\nALTER TABLE"))
{
strFullOutput += line + "GO\r\n";
}
}
foreach (string line in arrFullScript)
{
if (line.StartsWith("\r\nALTER TABLE"))
{
strFullOutput += line + "GO\r\n";
}
}
string strConditionalDrop = "\r\n\r\nIF DB_ID('" + dbName + "') IS NOT NULL\r\nBEGIN\r\n"
+ " ALTER DATABASE " + dbName + "\r\n"
+ " SET SINGLE_USER\r\n"
+ " WITH ROLLBACK IMMEDIATE;\r\n"
+ " DROP DATABASE " + dbName + ";\r\n"
+ "END\r\n";
strFullOutput = strFullOutput.Insert(strFullOutput.IndexOf("GO") + 2, strConditionalDrop);
tbxOutput.Text += "Writing corrected full script...\r\n";
using (StreamWriter sw = new StreamWriter(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\" + dbName + "_FULL.sql"))
{
sw.Write(strFullOutput);
}
tbxOutput.Text += "Full script successfully written.\r\n";
tbxOutput.Text += "Scripting master file complete.\r\n";
}
catch
{
tbxOutput.Text += "ERROR Scripting Master File Failed.\r\n";
lstErrors.Add("Scripting Master File Failed.");
}
tbxOutput.Text += "=================================\r\n";
if (lstErrors.Count == 0)
{
tbxOutput.Text += "SCRIPTING COMPLETED SUCCESSFULLY.\r\n";
}
else
{
tbxOutput.Text += "SCRIPTING COMPLETED WITH ERRORS.\r\n";
tbxOutput.Text += String.Format("The following {0} errors occurred:\r\n", lstErrors.Count);
foreach (string error in lstErrors)
{
tbxOutput.Text += error + "\r\n";
}
}
ActiveControl = btnClose;
}
catch (ConnectionFailureException) //Error type thrown by attempt to bind invalid server name
{
//throw new ConnectionFailureException("A connection to the specified server could not be made. Please check the supplied server name and try again.");
tbxOutput.Text += "Connection to server failed.\r\n";
RemoveFolders();//Clean out folders creating during this run
tbxOutput.Text += "A connection to the specified server could not be made. Please check the supplied server name and try again.\r\n";
}
catch //General Catch-All re-throws error without further handling
{
RemoveFolders(); //Clean out folders creating during this run
throw;
}
}
