private static void Process()
{
IMigrationOutput output = new ConsoleMigrationOutput(_OutputResultsFile, !_NoConsoleLogging);
SMOScriptOptions options = SMOScriptOptions.CreateFromConfig();
var migrator = new TsqlFileMigrator(_FileToProcess, output);
migrator.ParseFile(options, true);
}
public List <string> GenerateScriptFromSourceServer(ServerConnection sc, List <DatabaseObject> objectList)
{
var dtStart = DateTime.Now;
AsyncUpdateStatus updateStatus = GenScriptAsyncUpdateStatusHandler;
var args = new AsyncNotificationEventArgs(NotificationEventFunctionCode.GenerateScriptFromSQLServer, 0, "", CommonFunc.FormatString("Process started at {0} -- UTC -> {1} ... ", dtStart.ToString(CultureInfo.CurrentUICulture), dtStart.ToUniversalTime().ToString(CultureInfo.CurrentUICulture)) + Environment.NewLine, Color.DarkBlue);
StreamWriter swTSQL = null;
SqlSmoObject[] smoTriggers = null;
Object sender = System.Threading.Thread.CurrentThread;
var smoScriptOpts = SMOScriptOptions.CreateFromConfig();
updateStatus(args);
sc.Connect();
var ss = new Server(sc);
var db = ss.Databases[sc.DatabaseName];
var sdb = new ScriptDatabase();
sdb.Initialize(ss, db, updateStatus, smoScriptOpts, swTSQL);
args.DisplayText = "";
args.StatusMsg = "Sorting objects by dependency ...";
args.PercentComplete = 1;
updateStatus(args);
var sorted = GetSortedObjects(db, objectList);
var sp = new SourceProcessor();
var tempPathForBCPData = Path.Combine(Path.GetTempPath(), "/BCPData");
Directory.CreateDirectory(tempPathForBCPData);
sp.Initialize(sdb, smoScriptOpts, updateStatus, args, tempPathForBCPData);
//NOTE: This is what does the magic!
if (sp.Process(sorted, 30))
{
return(output);
}
if (!Regex.IsMatch(smoScriptOpts.ScriptTableAndOrData, smoScriptOpts.GetLocalizedStringValue("ScriptOptionsTableData"), RegexOptions.IgnoreCase))
{
if (sp.Process(DatabaseObjectsTypes.Triggers, smoTriggers, 95))
{
return(output);
}
}
var dtEnd = DateTime.Now;
var tsDuration = dtEnd.Subtract(dtStart);
var sHour = tsDuration.Minutes == 1 ? " hour, " : " hours, ";
var sMin = tsDuration.Minutes == 1 ? " minute and " : " minutes and ";
var sSecs = tsDuration.Seconds == 1 ? " second" : " seconds";
args.StatusMsg = "Done!";
args.DisplayColor = Color.DarkCyan;
if (smoScriptOpts.CheckCompatibility() == 1)
{
args.DisplayText = CommonFunc.FormatString(@"No analysis done on script.
Processing finished at {0} -- UTC -> {1}
Total processing time: {2}", dtEnd.ToString(CultureInfo.CurrentUICulture), dtEnd.ToUniversalTime().ToString(CultureInfo.CurrentUICulture), tsDuration.Hours + sHour + tsDuration.Minutes.ToString(CultureInfo.CurrentUICulture) + sMin + tsDuration.Seconds.ToString(CultureInfo.CurrentUICulture) + sSecs);
}
else
{
args.DisplayText = CommonFunc.FormatString(@"Analysis completed at {0} -- UTC -> {1}
Any issues discovered will be reported above.
Total processing time: {2}", dtEnd.ToString(CultureInfo.CurrentUICulture), dtEnd.ToUniversalTime().ToString(CultureInfo.CurrentUICulture), tsDuration.Hours + sHour + tsDuration.Minutes.ToString(CultureInfo.CurrentUICulture) + sMin + tsDuration.Seconds.ToString(CultureInfo.CurrentUICulture) + sSecs);
}
args.PercentComplete = 100;
updateStatus(args);
return(output);
}
public SMOScriptOptions CreateFromConfig()
{
var smoScriptOpts = new SMOScriptOptions();
var target = CommonFunc.GetAppSettingsStringValue("TargetServerType");
if (!string.IsNullOrEmpty(target))
{
smoScriptOpts.TargetServer = target;
}
smoScriptOpts.ScriptDefaults = CommonFunc.GetAppSettingsBoolValue("ScriptDefaults");
smoScriptOpts.ScriptHeaders = CommonFunc.GetAppSettingsBoolValue("ScriptHeaders");
smoScriptOpts.IncludeIfNotExists = CommonFunc.GetAppSettingsBoolValue("IncludeIfNotExists");
smoScriptOpts.ScriptDropCreate = CommonFunc.GetAppSettingsStringValue("ScriptDropCreate");
smoScriptOpts.ScriptCheckConstraints = CommonFunc.GetAppSettingsBoolValue("ScriptCheckConstraints");
smoScriptOpts.ScriptCollation = CommonFunc.GetAppSettingsBoolValue("ScriptCollation");
smoScriptOpts.ScriptForeignKeys = CommonFunc.GetAppSettingsBoolValue("ScriptForeignKeys");
smoScriptOpts.ScriptPrimaryKeys = CommonFunc.GetAppSettingsBoolValue("ScriptPrimaryKeys");
smoScriptOpts.ScriptUniqueKeys = CommonFunc.GetAppSettingsBoolValue("ScriptUniqueKeys");
smoScriptOpts.ScriptIndexes = CommonFunc.GetAppSettingsBoolValue("ScriptIndexes");
smoScriptOpts.ScriptTableAndOrData = CommonFunc.GetAppSettingsStringValue("ScriptTableAndOrData");
if (smoScriptOpts.ScriptTableAndOrData.Length == 0)
{
smoScriptOpts.ScriptTableAndOrData = CommonFunc.GetAppSettingsBoolValue("ScriptData") ? "Table Schema with Data" : "Table Schema Only";
}
smoScriptOpts.ScriptTableTriggers = CommonFunc.GetAppSettingsBoolValue("ScriptTableTriggers");
smoScriptOpts.ActiveDirectorySP = CommonFunc.GetAppSettingsBoolValue("ActiveDirectorySP");
smoScriptOpts.BackupandRestoreTable = CommonFunc.GetAppSettingsBoolValue("BackupandRestoreTable");
smoScriptOpts.ChangeDataCapture = CommonFunc.GetAppSettingsBoolValue("ChangeDataCapture");
smoScriptOpts.ChangeDataCaptureTable = CommonFunc.GetAppSettingsBoolValue("ChangeDataCaptureTable");
smoScriptOpts.CompatibilityChecks = CommonFunc.GetAppSettingsStringValue("CompatibilityChecks");
smoScriptOpts.DatabaseEngineSP = CommonFunc.GetAppSettingsBoolValue("DatabaseEngineSP");
smoScriptOpts.DatabaseMailSP = CommonFunc.GetAppSettingsBoolValue("DatabaseMailSP");
smoScriptOpts.DatabaseMaintenancePlan = CommonFunc.GetAppSettingsBoolValue("DatabaseMaintenancePlan");
smoScriptOpts.DataControl = CommonFunc.GetAppSettingsBoolValue("DataControl");
smoScriptOpts.DistributedQueriesSP = CommonFunc.GetAppSettingsBoolValue("DistributedQueriesSP");
smoScriptOpts.FullTextSearchSP = CommonFunc.GetAppSettingsBoolValue("FullTextSearchSP");
smoScriptOpts.GeneralExtendedSPs = CommonFunc.GetAppSettingsBoolValue("GeneralExtendedSPs");
smoScriptOpts.GeneralTSQL = CommonFunc.GetAppSettingsBoolValue("GeneralTSQL");
smoScriptOpts.IntegrationServicesTable = CommonFunc.GetAppSettingsBoolValue("IntegrationServicesTable");
smoScriptOpts.LogShipping = CommonFunc.GetAppSettingsBoolValue("LogShipping");
smoScriptOpts.MetadataFunction = CommonFunc.GetAppSettingsBoolValue("MetadataFunction");
smoScriptOpts.OLEAutomationSP = CommonFunc.GetAppSettingsBoolValue("OLEAutomationSP");
smoScriptOpts.OLEDBTable = CommonFunc.GetAppSettingsBoolValue("OLEDBTable");
smoScriptOpts.ProfilerSP = CommonFunc.GetAppSettingsBoolValue("ProfilerSP");
smoScriptOpts.ReplicationSP = CommonFunc.GetAppSettingsBoolValue("ReplicationSP");
smoScriptOpts.ReplicationTable = CommonFunc.GetAppSettingsBoolValue("ReplicationTable");
smoScriptOpts.RowsetFunction = CommonFunc.GetAppSettingsBoolValue("RowsetFunction");
smoScriptOpts.SecurityFunction = CommonFunc.GetAppSettingsBoolValue("SecurityFunction");
smoScriptOpts.SecuritySP = CommonFunc.GetAppSettingsBoolValue("SecuritySP");
smoScriptOpts.SQLMailSP = CommonFunc.GetAppSettingsBoolValue("SQLMailSP");
smoScriptOpts.SQLServerAgentSP = CommonFunc.GetAppSettingsBoolValue("SQLServerAgentSP");
smoScriptOpts.SQLServerAgentTable = CommonFunc.GetAppSettingsBoolValue("SQLServerAgentTable");
smoScriptOpts.SystemCatalogView = CommonFunc.GetAppSettingsBoolValue("SystemCatalogView");
smoScriptOpts.SystemFunction = CommonFunc.GetAppSettingsBoolValue("SystemFunction");
smoScriptOpts.SystemStatisticalFunction = CommonFunc.GetAppSettingsBoolValue("SystemStatisticalFunction");
smoScriptOpts.Unclassified = CommonFunc.GetAppSettingsBoolValue("Unclassified");
return(smoScriptOpts);
}
public void GenerateScriptFromSourceServer(Server sourceServer, Database sourceDatabase, StreamWriter swTSQL, AsyncUpdateStatus updateStatus, string outputDir, ObjectSelector objectFilter)
{
_updateStatus = updateStatus;
_smoScriptOpts = SMOScriptOptions.CreateFromConfig();
_ObjectSelector = objectFilter;
//if (_smoScriptOpts.TargetServer == _smoScriptOpts.GetLocalizedStringValue("ServerType_AzureSQLDatabase"))
//{
// _ObjectSelector.Assemblies.Script = false;
// _ObjectSelector.SchemaCollections.Script = false;
//}
DateTime dtStart = DateTime.Now;
AsyncNotificationEventArgs eventArgs = new AsyncNotificationEventArgs(NotificationEventFunctionCode.GenerateScriptFromSQLServer, 0, "", CommonFunc.FormatString(Properties.Resources.MessageProcessStarted, dtStart.ToString(CultureInfo.CurrentUICulture), dtStart.ToUniversalTime().ToString(CultureInfo.CurrentUICulture)) + Environment.NewLine, Color.DarkBlue);
_updateStatus(eventArgs);
ScriptDatabase sdb = new ScriptDatabase();
sdb.Initialize(sourceServer, sourceDatabase, _updateStatus, _smoScriptOpts, swTSQL, true);
eventArgs.DisplayText = "";
eventArgs.StatusMsg = Properties.Resources.MessageSorting;
eventArgs.PercentComplete = 1;
_updateStatus(eventArgs);
// Tables, Views, Stored Procedures, and Triggers can all have dependencies. GetSortedObjects returns
// these objects in dependency order.
SqlSmoObject[] smoAssemblies = GetAssemblies(sourceDatabase);
SqlSmoObject[] smoPartitionFunctions = GetPartitionFunctions(sourceDatabase);
SqlSmoObject[] smoPartitionSchemes = GetPartitionSchemes(sourceDatabase);
SqlSmoObject[] smoTriggers = GetTriggers(sourceServer, sourceDatabase);
SqlSmoObject[] smoRoles = GetRoles(sourceDatabase);
SqlSmoObject[] smoSchemas = GetSchemas(sourceDatabase);
SqlSmoObject[] smoSchemaCols = GetSchemaCollections(sourceServer, sourceDatabase);
SqlSmoObject[] smoUDTs = GetUDTs(sourceDatabase);
SqlSmoObject[] smoUDTTs = GetUDTTs(sourceDatabase);
SqlSmoObject[] smoSynonyms = GetSynonyms(sourceDatabase);
SqlSmoObject[] sorted = GetSortedObjects(sourceDatabase);
if (Regex.IsMatch(_smoScriptOpts.ScriptDropCreate, _smoScriptOpts.GetLocalizedStringValue("SOSDrop"), RegexOptions.IgnoreCase) ||
Regex.IsMatch(_smoScriptOpts.ScriptDropCreate, _smoScriptOpts.GetLocalizedStringValue("SOSDropCreate"), RegexOptions.IgnoreCase))
{
eventArgs.StatusMsg = Properties.Resources.MessageCreatingDropScripts;
eventArgs.PercentComplete = 2;
_updateStatus(eventArgs);
ScriptDrops(sorted, smoRoles, smoSchemas, smoSchemaCols, smoUDTs, smoUDTTs, smoAssemblies, smoSynonyms, smoPartitionFunctions, smoPartitionSchemes, sdb);
}
if (Regex.IsMatch(_smoScriptOpts.ScriptDropCreate, _smoScriptOpts.GetLocalizedStringValue("SOSCreate"), RegexOptions.IgnoreCase) ||
Regex.IsMatch(_smoScriptOpts.ScriptDropCreate, _smoScriptOpts.GetLocalizedStringValue("SOSDropCreate"), RegexOptions.IgnoreCase))
{
SourceProcessor sp = new SourceProcessor();
sp.Initialize(sdb, _smoScriptOpts, _updateStatus, eventArgs, outputDir);
// Roles, Schemas, XML Schema Collections and UDT have no dependencies. Thus we process one at a time.
if (!Regex.IsMatch(_smoScriptOpts.ScriptTableAndOrData, _smoScriptOpts.GetLocalizedStringValue("ScriptOptionsTableData"), RegexOptions.IgnoreCase))
{
if (sp.Process(DatabaseObjectsTypes.Assemblies, smoAssemblies, 3))
{
return;
}
if (sp.Process(DatabaseObjectsTypes.PartitionFunctions, smoPartitionFunctions, 6))
{
return;
}
if (sp.Process(DatabaseObjectsTypes.PartitionSchemes, smoPartitionSchemes, 9))
{
return;
}
if (sp.Process(DatabaseObjectsTypes.Roles, smoRoles, 12))
{
return;
}
if (sp.Process(DatabaseObjectsTypes.Schemas, smoSchemas, 15))
{
return;
}
if (sp.Process(DatabaseObjectsTypes.XMLSchemaCollections, smoSchemaCols, 20))
{
return;
}
if (sp.Process(DatabaseObjectsTypes.UserDefinedDataTypes, smoUDTs, 25))
{
return;
}
if (sp.Process(DatabaseObjectsTypes.UserDefinedTableTypes, smoUDTTs, 30))
{
return;
}
if (sp.Process(DatabaseObjectsTypes.Synonyms, smoSynonyms, 35))
{
return;
}
}
if (sp.Process(sorted, 40))
{
return;
}
if (!Regex.IsMatch(_smoScriptOpts.ScriptTableAndOrData, _smoScriptOpts.GetLocalizedStringValue("ScriptOptionsTableData"), RegexOptions.IgnoreCase))
{
if (sp.Process(DatabaseObjectsTypes.Triggers, smoTriggers, 95))
{
return;
}
}
}
if (swTSQL != null)
{
swTSQL.Flush();
swTSQL.Close();
}
DateTime dtEnd = DateTime.Now;
TimeSpan tsDuration = dtEnd.Subtract(dtStart);
string sHour = tsDuration.Minutes == 1 ? Properties.Resources.MessageHour : Properties.Resources.MessageHours;
string sMin = tsDuration.Minutes == 1 ? Properties.Resources.MessageMinute : Properties.Resources.MessageMinutes;
string sSecs = tsDuration.Seconds == 1 ? Properties.Resources.MessageSecond : Properties.Resources.MessageSeconds;
eventArgs.StatusMsg = Properties.Resources.Done;
eventArgs.DisplayColor = Color.DarkCyan;
if (_smoScriptOpts.CheckCompatibility() == 1)
{
eventArgs.DisplayText = CommonFunc.FormatString(Properties.Resources.MessageFinishedNoAnalysis, dtEnd.ToString(CultureInfo.CurrentUICulture), dtEnd.ToUniversalTime().ToString(CultureInfo.CurrentUICulture), tsDuration.Hours + sHour + tsDuration.Minutes.ToString(CultureInfo.CurrentUICulture) + sMin + tsDuration.Seconds.ToString(CultureInfo.CurrentUICulture) + sSecs);
}
else
{
eventArgs.DisplayText = CommonFunc.FormatString(Properties.Resources.MessageFinishedWithAnalysis, dtEnd.ToString(CultureInfo.CurrentUICulture), dtEnd.ToUniversalTime().ToString(CultureInfo.CurrentUICulture), tsDuration.Hours + sHour + tsDuration.Minutes.ToString(CultureInfo.CurrentUICulture) + sMin + tsDuration.Seconds.ToString(CultureInfo.CurrentUICulture) + sSecs);
}
eventArgs.PercentComplete = 100;
_updateStatus(eventArgs);
}
private List <Azure> ParseFile(string fileToProcess, bool parseFile)
{
var startTime = DateTime.Now;
var messageList = new List <Azure> {
new Azure(fileToProcess, MessageTypes.Info, "Beginning Azure Check.")
};
var sdb = new ScriptDatabase();
var options = SMOScriptOptions.CreateFromConfig();
sdb.Initialize(_output.StatusUpdateHandler, options, false);
/****************************************************************/
var sqlText = CommonFunc.GetTextFromFile(fileToProcess);
var cah = new CommentAreaHelper();
long totalCharacterOffset = 0;
var bCommentedLine = false;
var sqlCmds = new List <string>();
if (parseFile)
{
var sb = new StringBuilder();
cah.FindCommentAreas(sqlText);
foreach (var line in cah.Lines)
{
if (line.Equals("GO", StringComparison.OrdinalIgnoreCase))
{
if (!cah.IsIndexInComments(totalCharacterOffset))
{
sqlCmds.Add(sb.ToString());
sb.Length = 0;
}
else
{
sb.Append(line + Environment.NewLine);
}
}
else
{
sb.Append(line + Environment.NewLine);
}
totalCharacterOffset += line.Length + cah.CrLf;
}
//first we will remove the comments.
var r = new Regex(@"(/\*([^*]|[\r\n]|(\*+([^*/]|[\r\n])))*\*+/)|(--.*)");
var remainder = r.Replace(sb.ToString(), string.Empty).Trim();
if (remainder.Length > 8)
{
messageList.Add(new Azure(fileToProcess, MessageTypes.Warning, "Final Section of T-SQL didn't finish with a 'GO' command."));
sb.Append("GO" + Environment.NewLine);
sqlCmds.Add(sb.ToString());
}
else //this will re-add the comments and such so that the output files will better match the input files.
{
sqlCmds.Add(sb.ToString());
}
}
else
{
sqlCmds.Add(sqlText);
}
var numCmds = sqlCmds.Count;
if (numCmds == 0)
{
messageList.Add(new Azure(fileToProcess, MessageTypes.Info, "No data to process"));
return(messageList);
}
var loopCtr = 0;
messageList.Add(new Azure(fileToProcess, MessageTypes.Info, string.Format("Processing {0} out of {1}", loopCtr, numCmds)));
totalCharacterOffset = 0;
foreach (var cmd in sqlCmds)
{
++loopCtr;
if (cmd.Length == 0 || cmd.Equals(Environment.NewLine))
{
continue;
}
foreach (CommentArea ca in cah.CommentAreas)
{
if (ca.Start == totalCharacterOffset && ca.End == totalCharacterOffset + cmd.Length - cah.CrLf - 1) // note that the -1 is to put you at zero based counting
{
bCommentedLine = true;
break;
}
bCommentedLine = false;
}
totalCharacterOffset += cmd.Length + cah.CrLf;
if (parseFile && !bCommentedLine && !(cmd.StartsWith("/*~") || cmd.StartsWith("~*/")))
{
if (Regex.IsMatch(cmd, "(CREATE|ALTER)\\sPROCEDURE", RegexOptions.IgnoreCase))
{
sdb.ParseFileTSQLGo(cmd);
}
else if (Regex.IsMatch(cmd, "(CREATE|ALTER)\\sTABLE", RegexOptions.IgnoreCase))
{
sdb.ParseFileTable(cmd);
}
else if (Regex.IsMatch(cmd, "CREATE\\sXML\\sSCHEMA\\sCOLLECTION", RegexOptions.IgnoreCase))
{
sdb.ParseFileXMLSchemaCollections(cmd);
}
else if (Regex.IsMatch(cmd, "CREATE\\sTYPE", RegexOptions.IgnoreCase))
{
sdb.ParseFileUDT(cmd);
}
else if (Regex.IsMatch(cmd, "CREATE\\s[a-z\\s]*\\sINDEX", RegexOptions.IgnoreCase))
{
sdb.ParseFileIndex(cmd);
}
else if (Regex.IsMatch(cmd, "CREATE ROLE", RegexOptions.IgnoreCase))
{
sdb.ParseFileRole(cmd);
}
else
{
sdb.ParseFileTSQLGo(cmd);
}
}
else
{
sdb.OutputSQLString(cmd, Color.Black);
}
if (loopCtr % 20 == 0)
{
messageList.Add(new Azure(fileToProcess, MessageTypes.Info, string.Format("Processing {0} out of {1} - %{2} Complete", loopCtr, numCmds, (int)((loopCtr / (float)numCmds) * 100.0))));
}
}
var endTime = DateTime.Now;
messageList.Add(new Azure(fileToProcess, MessageTypes.Info, string.Format(
"{1}Total processing time --> {0}",
endTime.Subtract(startTime),
"--~")));
return(messageList);
}
public AdvancedSettings(SMOScriptOptions sso)
{
InitializeComponent();
pgOptions.SelectedObject = sso;
}