Example #1
0
        /// <summary>
        /// Initializes a new instance of the FileGroup class.
        /// </summary>
        /// <param name=""></param>
        public FileGroup(SMO.FileGroup fileGroup)
        {
            _FileGroup = fileGroup;

            Name      = fileGroup.Name;
            IsDefault = fileGroup.IsDefault.ToString();
        }
Example #2
0
        public static void LoadFromSMO(MSMO.FileGroup fileGroup, Guid fileGroupID, Guid databaseID)
        {
#if TRACE
            long startTicks = VNC.AppLog.Trace3("Enter", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 0);
#endif
            foreach (MSMO.DataFile dataFile in fileGroup.Files)
            {
                GetInfoFromSMO(fileGroupID, fileGroup.Name, dataFile, databaseID);
            }
#if TRACE
            VNC.AppLog.Trace3("Exit", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 1, startTicks);
#endif
        }
Example #3
0
 private static void UpdateDataSet(this MSMO.FileGroup fileGroup, Data.ApplicationDataSet.DBFileGroupsRow dataRow)
 {
     try
     {
         dataRow.Size = fileGroup.Size;
     }
     catch (Exception ex)
     {
         VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 7);
         // TODO(crhodes):
         // Wrap anything above that throws an exception that we want to ignore,
         // e.g. property not available because of SQL Edition.
     }
 }
Example #4
0
 public static void UpdateDataSet(this MSMO.FileGroup fileGroup, Data.ApplicationDataSet.DBFileGroupsRow fileGroupRow)
 {
     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;
     }
 }
Example #5
0
        private static void Update(MSMO.FileGroup fileGroup, SQLInformation.Data.ApplicationDataSet.DBFileGroupsRow dataRow)
        {
            try
            {
                fileGroup.UpdateDataSet(dataRow);

                UpdateDatabaseWithSnapShot(dataRow, "");

                // Add the snapshot

                SMO.Helper.TakeDBFileGroupSnapShot(dataRow);

                // Add info about the DataFiles belonging to this filegroup.

                DataFile.LoadFromSMO(fileGroup, dataRow.ID, dataRow.Database_ID);
            }
            catch (Exception ex)
            {
                VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 6);

                UpdateDatabaseWithSnapShot(dataRow, ex.ToString().Substring(0, 256));
            }
        }
Example #6
0
        private static SQLInformation.Data.ApplicationDataSet.DBFileGroupsRow Add(MSMO.FileGroup fileGroup, Guid databaseID)
        {
            SQLInformation.Data.ApplicationDataSet.DBFileGroupsRow dataRow = null;

            try
            {
                dataRow = Common.ApplicationDataSet.DBFileGroups.NewDBFileGroupsRow();

                dataRow.ID             = Guid.NewGuid();
                dataRow.Database_ID    = databaseID;
                dataRow.Name_FileGroup = fileGroup.Name;
                dataRow.Size           = fileGroup.Size;

                Common.ApplicationDataSet.DBFileGroups.AddDBFileGroupsRow(dataRow);

                //fileGroupID = newFileGroup.ID;

                dataRow.SnapShotDate  = DateTime.Now;
                dataRow.SnapShotError = "";

                // Need to update before calling LoadDBDataFiles_FromSMO
                Common.ApplicationDataSet.DBFileGroupsTA.Update(Common.ApplicationDataSet.DBFileGroups);

                DataFile.LoadFromSMO(fileGroup, dataRow.ID, databaseID);
            }
            catch (Exception ex)
            {
                VNC.AppLog.Error(ex, LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 5);
                // 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);
        }
Example #7
0
        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);
            }
        }
Example #8
0
        private static SQLInformation.Data.ApplicationDataSet.DBFileGroupsRow GetInfoFromSMO(MSMO.FileGroup fileGroup, Guid databaseID)
        {
#if TRACE
            long startTicks = VNC.AppLog.Trace4("Enter", LOG_APPNAME, CLASS_BASE_ERRORNUMBER + 2);
#endif
            SQLInformation.Data.ApplicationDataSet.DBFileGroupsRow dataRow = null;

            try
            {
                var dbs = from tb in Common.ApplicationDataSet.DBFileGroups
                          where tb.Database_ID == databaseID
                          select tb;

                var dbs2 = from db2 in dbs
                           where db2.Name_FileGroup == fileGroup.Name
                           select db2;

                if (dbs2.Count() > 0)
                {
                    dataRow = dbs2.First();
                    Update(fileGroup, dataRow);
                }
                else
                {
                    dataRow = Add(fileGroup, databaseID);
                }
            }
            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);
        }