Exemplo n.º 1
0
        public static Biml BuildBiml( BimlRequest request )
        {
            // Configure SQL SMO
            var server = new Server( request.ServerName );
            var scriptingOptions = new ScriptingOptions { Encoding = Encoding.UTF8 };
            server.Script( scriptingOptions );
            var database = new Microsoft.SqlServer.Management.Smo.Database( server, request.DatabaseName );
            database.Refresh();

            var bimlService = new BimlService();
            var output = new Biml();

            // Selectively build sections
            if ( request.HasConnections )
                output.Connections = bimlService.GetConnections( server, database );
            if ( request.HasDatabases )
                output.Databases = bimlService.GetDatabases( database );
            if( request.HasSchemas )
                output.Schemas = bimlService.GetSchemas( database );
            if (request.HasTables)
            {
                output.Tables = bimlService.GetTables( database, request.HasFactsAndDimensions );
            }
            if (request.HasFactsAndDimensions)
            {
                output.Facts = bimlService.GetFacts( database );
                output.Dimensions = bimlService.GetDimensions( database );
            }

            return output;
        }
 /// <summary>
 /// Creates a new empty database on the server if one of that name doesn't already exist
 /// </summary>
 /// <param name="name">Name of the database to create</param>
 public void CreateDB(string name)
 {
     if (!_targetServer.Databases.Contains(name))
     {
         var toCreate = new Microsoft.SqlServer.Management.Smo.Database(_targetServer, name);
         toCreate.Create();
     }
 }
Exemplo n.º 3
0
        /// <summary>
        /// Create a new instance of the database scripts object from the database provided
        /// </summary>
        /// <param name="Database"></param>
        internal DatabaseScriptWriter(Microsoft.SqlServer.Management.Smo.Database Database, string SchemaName)
        {
            _database = Database;
            _schemaName = SchemaName;

            options.ClusteredIndexes = true;
            options.Default = true;
            options.DriAll = true;
            options.Indexes = true;
            options.IncludeHeaders = true;
        }
        public ServerInfo GetServerInfo(Database database)
        {
            var info = new ServerInfo();
            DataFile dataFile = database.FileGroups[0].Files[0];

            info.DataFile = new DatabaseFile {LogicalName = dataFile.Name, PhysicalPath = dataFile.FileName};

            foreach (LogFile logFile in database.LogFiles)
            {
                info.LogsFiles.Add(new DatabaseFile {LogicalName = logFile.Name, PhysicalPath = logFile.FileName});
            }

            return info;
        }
        public DatabaseRestoreRequest GenerateDatabaseRestoreRequestService(string backupFileName,
                                                                            Server server, Database database)
        {
            BackupInfo backupInfo = backupFileReaderService.GetBackupInfo(backupFileName, server);
            ServerInfo targetServerInfo = targetDatabaseServerRepositoty.GetServerInfo(database);

            IList<FileToRestore> filesList = MergeFilesLists(backupInfo, targetServerInfo);

            return new DatabaseRestoreRequest
                {
                    FullBackupFile = backupFileName,
                    TargetInstance = server.Name,
                    TargetDatabase = database.Name,
                    FilesLists = filesList
                };
        }
        private static void RestoreWasCompleted(TaskCompletionSource<string> tcs, ServerMessageEventArgs args, DatabaseRestoreRequest request, Database database, Server server)
        {
            try
            {
                // God knows why if a backup successfuly completed the SqlError object is set with a success message and a code 3014.
                if (args.Error != null && args.Error.Number != 3014)
                {
                    tcs.TrySetException(new SqlExecutionException(args.Error.Message));
                }
                else
                {
                    // Execute additional action after the restore was completed
                    FinishRestore(request, database, server);

                    //Task completed!
                    tcs.TrySetResult(args.ToString());
                }
            }
            catch (Exception ex)
            {
                tcs.TrySetException(ex);
            }
        }
        private static void FinishRestore(DatabaseRestoreRequest request, Database database, Server server)
        {

            // After the restore, ensure the recovery model is set to simple.
            // Since we only support DEV/TEST/DEMO, we dont want the overhead of the other recovery models.
            database.RecoveryModel = RecoveryModel.Simple;
            database.Alter();

            string sqlConnectionString =
                string.Format("Integrated Security=SSPI;Persist Security Info=True;Initial Catalog={1};Data Source={0}",
                              server.Name, database.Name);

            foreach (var script in request.ScriptsToExecute)
            {
                var fileInfo = new FileInfo(script);


                string sql;

                using (var text = fileInfo.OpenText())
                {
                    sql = text.ReadToEnd();
                    text.Close();
                }


                SqlConnection connection = new SqlConnection(sqlConnectionString);
                Server srv = new Server(new ServerConnection(connection));
                srv.ConnectionContext.SqlExecutionModes = SqlExecutionModes.ExecuteAndCaptureSql;
                srv.ConnectionContext.ExecuteNonQuery(sql);
            }
        }
Exemplo n.º 8
0
 /// <summary>
 /// Creates a new instance of the database class using a SMO database object
 /// </summary>
 /// <param name="db"></param>
 internal Database(Microsoft.SqlServer.Management.Smo.Database db)
 {
     _database = db;
 }
Exemplo n.º 9
0
        private ServiceResponse CreateDatabase(DatabaseInfo info, string databaseUserNamePublic, string databasePasswordPublic, string scriptResourceName, IEnumerable<string> scriptResourceNames, ScriptLoader scriptLoader, DatabaseCredentials creatorCredentials)
        {
            var creatorInfo = info.CloneWithNewCredentials(creatorCredentials);
            using (var database = new DatabaseManipulator(creatorInfo))
            {
                try
                {
                    smo.Server sqlServer = database.SqlServer;

                    // Make sure SQL Server allows SQL logins if needed
                    if (creatorInfo.HasUserName())
                    {
                        if (database.LoginMode == smo.ServerLoginMode.Integrated)
                        {
                            _logger.Error("Database Server is not configured for mixed-mode authentication.", "Database Server", creatorInfo.ServerName);
                            _response.AddError(string.Format(CultureInfo.InvariantCulture, "Database Server ({0}) is not configured for mixed-mode authentication.", creatorInfo.ServerName));
                        }
                    }

                    if (!_response.HasErrors)
                    {
                        if (!database.Exists())
                        {
                            _logger.Info("Creating database.", "Database name", info.DatabaseName, "Database Server", info.ServerName);

                            // Create new database
                            var newDatabase = new smo.Database(sqlServer, info.DatabaseName);
                            newDatabase.Create();

                            database.CreateLogin(info.UserName, info.Password, true);
                            database.CreateLogin(databaseUserNamePublic, databasePasswordPublic, false);

                            // I don't know why we have to dispose this here, but I was afraid to remove it
                            database.Dispose();

                            UpdateDatabaseSchema(info, databaseUserNamePublic, scriptResourceName, scriptResourceNames, scriptLoader);

                            _logger.Info("Database created successfully.", info.GenerateCustomLoggingProperties());
                        }
                        else
                        {
                            _logger.Error("Database already exists.", info.GenerateCustomLoggingProperties());
                            _response.AddError(string.Format(CultureInfo.InvariantCulture, "Database ({0}) on server ({1}) already exists.", info.DatabaseName, info.ServerName));
                        }
                    }
                }
                catch (Exception ex)
                {
                    _logger.Error("Creation of Database failed.", ex, info.GenerateCustomLoggingProperties());
                    _response.AddError(string.Format(CultureInfo.InvariantCulture, "Creation of Database ({0}) on server ({1}) failed: {2}", info.DatabaseName, info.ServerName, ex.Message));
                }
            }

            return _response;
        }
Exemplo n.º 10
0
        /// <summary>
        /// Initializes a new instance of the Database class.
        /// </summary>
        public Database(SMO.Database database)
        {
            long startTime = Common.WriteToDebugWindow(string.Format("Enter SMOH.{0}({1})", "Database", database));

            // NB. Not all properties are available depending on login credentials
            // Wrap in TC blocks.

            _Database = database;


            try { ActiveConnections = database.ActiveConnections.ToString(); }    catch (Exception) { ActiveConnections = "<No Access>"; }
            try { AnsiNullDefault = database.AnsiNullDefault.ToString(); }    catch (Exception) { AnsiNullDefault = "<No Access>"; }
            try { AnsiNullsEnabled = database.AnsiNullsEnabled.ToString(); }    catch (Exception) { AnsiNullsEnabled = "<No Access>"; }
            try { AnsiPaddingEnabled = database.AnsiPaddingEnabled.ToString(); }    catch (Exception) { AnsiPaddingEnabled = "<No Access>"; }
            try { AnsiWarningsEnabled = database.AnsiWarningsEnabled.ToString(); }    catch (Exception) { AnsiWarningsEnabled = "<No Access>"; }
            try { AutoClose = database.AutoClose.ToString(); }    catch (Exception) { AutoClose = "<No Access>"; }
            try { AutoCreateStatisticsEnabled = database.AutoCreateStatisticsEnabled.ToString(); }    catch (Exception) { AutoCreateStatisticsEnabled = "<No Access>"; }
            try { AutoShrink = database.AutoShrink.ToString(); }    catch (Exception) { AutoShrink = "<No Access>"; }
            //try { AutoUpdateStatisticsAsync = database.AutoUpdateStatisticsAsync.ToString();    }    catch(Exception) { AutoUpdateStatisticsAsync = "<No Access>"; }
            //try { AutoUpdateStatisticsEnabled = database.AutoUpdateStatisticsEnabled.ToString();    }    catch(Exception) { AutoUpdateStatisticsEnabled = "<No Access>"; }
            //try { BrokerEnabled = database.BrokerEnabled.ToString();    }    catch(Exception) { BrokerEnabled = "<No Access>"; }
            try { CaseSensitive = database.CaseSensitive.ToString(); }    catch (Exception) { CaseSensitive = "<No Access>"; }
            //try { ChangeTrackingAutoCleanUp = database.ChangeTrackingAutoCleanUp.ToString();    }    catch(Exception) { ChangeTrackingAutoCleanUp = "<No Access>"; }
            //try { ChangeTrackingEnabled = database.ChangeTrackingEnabled.ToString();    }    catch(Exception) { ChangeTrackingEnabled = "<No Access>"; }
            //try { ChangeTrackingRetentionPeriod = database.ChangeTrackingRetentionPeriod.ToString();    }    catch(Exception) { ChangeTrackingRetentionPeriod = "<No Access>"; }
            //try { ChangeTrackingRetentionPeriodUnits = database.ChangeTrackingRetentionPeriodUnits.ToString();    }    catch(Exception) { ChangeTrackingRetentionPeriodUnits = "<No Access>"; }
            try { CloseCursorsOnCommitEnabled = database.CloseCursorsOnCommitEnabled.ToString(); }    catch (Exception) { CloseCursorsOnCommitEnabled = "<No Access>"; }
            try { Collation = database.Collation.ToString(); }    catch (Exception) { Collation = "<No Access>"; }

            try
            {
                switch (database.CompatibilityLevel)
                {
                case Microsoft.SqlServer.Management.Smo.CompatibilityLevel.Version60:
                    CompatibilityLevel = "Version60";
                    break;

                case Microsoft.SqlServer.Management.Smo.CompatibilityLevel.Version65:
                    CompatibilityLevel = "Version65";
                    break;

                case Microsoft.SqlServer.Management.Smo.CompatibilityLevel.Version70:
                    CompatibilityLevel = "Version70";
                    break;

                case Microsoft.SqlServer.Management.Smo.CompatibilityLevel.Version80:
                    CompatibilityLevel = "Version80";
                    break;

                case Microsoft.SqlServer.Management.Smo.CompatibilityLevel.Version90:
                    CompatibilityLevel = "Version90";
                    break;

                case Microsoft.SqlServer.Management.Smo.CompatibilityLevel.Version100:
                    CompatibilityLevel = "Version100";
                    break;
                }
            }
            catch (Exception)
            {
                CompatibilityLevel = "<No Access>";
            }

            try { ConcatenateNullYieldsNull = database.ConcatenateNullYieldsNull.ToString(); }      catch (Exception) { ConcatenateNullYieldsNull = "<No Access>"; }
            try { CreateDate = database.CreateDate.ToString("yyyy-MM-dd hh:mm:ss"); }               catch (Exception) { CreateDate = "<No Access>"; }
            //try { DatabaseGuid = database.DatabaseGuid.ToString(); }                                catch(Exception) { DatabaseGuid = "<No Access>"; }
            //try { DatabaseSnapshotBaseName = database.DatabaseSnapshotBaseName; }                   catch(Exception) { DatabaseSnapshotBaseName = "<No Access>"; }
            try { DataSpaceUsage = database.DataSpaceUsage.ToString(); }                            catch (Exception) { DataSpaceUsage = "<No Access>"; }
            //try { DateCorrelationOptimization = database.DateCorrelationOptimization.ToString(); }  catch(Exception) { DateCorrelationOptimization = "<No Access>"; }
            try { DboLogin = database.DboLogin.ToString(); }                                        catch (Exception) { DboLogin = "******"; }
            try { DefaultFileGroup = database.DefaultFileGroup; }                                   catch (Exception) { DefaultFileGroup = "<No Access>"; }
            //try { DefaultFileStreamFileGroup = database.DefaultFileStreamFileGroup.ToString(); }    catch(Exception) { DefaultFileStreamFileGroup = "<No Access>"; }
            //try { DefaultFullTextCatalog = database.DefaultFullTextCatalog.ToString();    }         catch(Exception) { DefaultFullTextCatalog = "<No Access>"; }
            try { DefaultSchema = database.DefaultSchema.ToString(); }                              catch (Exception) { DefaultSchema = "<No Access>"; }
            //try { EncryptionEnabled = database.EncryptionEnabled.ToString(); }                      catch(Exception) { EncryptionEnabled = "<No Access>"; }
            //try { HonorBrokerPriority = database.HonorBrokerPriority.ToString(); }                  catch(Exception) { HonorBrokerPriority = "<No Access>"; }
            try { ID = database.ID.ToString(); }                                                    catch (Exception) { ID = "<No Access>"; }
            try { IndexSpaceUsage = database.IndexSpaceUsage.ToString(); }                          catch (Exception) { IndexSpaceUsage = "<No Access>"; }
            try { IsAccessible = database.IsAccessible.ToString(); }                                catch (Exception) { IsAccessible = "<No Access>"; }
            //try { IsDatabaseSnapshot = database.IsDatabaseSnapshot.ToString(); }                    catch(Exception) { IsDatabaseSnapshot = "<No Access>";  }
            //try { IsDatabaseSnapshotBase = database.IsDatabaseSnapshotBase.ToString(); }            catch(Exception) { IsDatabaseSnapshotBase = "<No Access>";  }
            try { IsDbAccessAdmin = database.IsDbAccessAdmin.ToString(); }                          catch (Exception) { IsDbAccessAdmin = "<No Access>"; }
            try { IsDbBackupOperator = database.IsDbBackupOperator.ToString(); }                    catch (Exception) { IsDbBackupOperator = "<No Access>"; }
            try { IsDbDatareader = database.IsDbDatareader.ToString(); }                            catch (Exception) { IsDbDatareader = "<No Access>"; }
            try { IsDbDatawriter = database.IsDbDatawriter.ToString(); }                            catch (Exception) { IsDbDatawriter = "<No Access>"; }
            try { IsDbDdlAdmin = database.IsDbDdlAdmin.ToString(); }                                catch (Exception) { IsDbDdlAdmin = "<No Access>"; }
            try { IsDbDenyDatareader = database.ID.ToString(); }                                    catch (Exception) { IsDbDenyDatareader = "<No Access>"; }
            try { IsDbDenyDatawriter = database.IsDbDenyDatawriter.ToString(); }                    catch (Exception) { IsDbDenyDatawriter = "<No Access>"; }
            try { IsDbOwner = database.IsDbOwner.ToString(); }                                      catch (Exception) { IsDbOwner = "<No Access>"; }
            try { IsDbSecurityAdmin = database.IsDbSecurityAdmin.ToString(); }                      catch (Exception) { IsDbSecurityAdmin = "<No Access>"; }
            try { IsFullTextEnabled = database.IsFullTextEnabled.ToString(); }                      catch (Exception) { IsFullTextEnabled = "<No Access>"; }
            //try { IsMailHost = database.IsMailHost.ToString(); }                                    catch(Exception) { IsMailHost = "<No Access>";  }
            //try { IsManagementDataWarehouse = database.IsManagementDataWarehouse.ToString(); }      catch(Exception) { IsManagementDataWarehouse = "<No Access>";  }
            //try { IsMirroringEnabled = database.IsMirroringEnabled.ToString(); }                    catch(Exception) { IsMirroringEnabled = "<No Access>";  }
            //try { IsParameterizationForced = database.IsParameterizationForced.ToString(); }        catch(Exception) { IsParameterizationForced = "<No Access>";  }
            //try { IsReadCommittedSnapshotOn = database.IsReadCommittedSnapshotOn.ToString(); }      catch(Exception) { IsReadCommittedSnapshotOn = "<No Access>";  }
            try { IsSystemObject = database.IsSystemObject.ToString(); }                            catch (Exception) { IsSystemObject = "<No Access>"; }
            //try { IsTouched = database.IsTouched.ToString(); }                                    catch(Exception) { IsTouched = "<No Access>";  }
            try { IsUpdateable = database.IsUpdateable.ToString(); }                                catch (Exception) { IsUpdateable = "<No Access>"; }
            try { IsVarDecimalStorageFormatEnabled = database.IsVarDecimalStorageFormatEnabled.ToString(); }  catch (Exception) { IsVarDecimalStorageFormatEnabled = "<No Access>"; }
            try { LastBackupDate = database.LastBackupDate.ToString(); }                            catch (Exception) { LastBackupDate = "<No Access>"; }
            try { LastDifferentialBackupDate = database.LastDifferentialBackupDate.ToString(); }    catch (Exception) { LastDifferentialBackupDate = "<No Access>"; }
            try { LastLogBackupDate = database.LastLogBackupDate.ToString(); }                      catch (Exception) { LastLogBackupDate = "<No Access>"; }
            try { LocalCursorsDefault = database.LocalCursorsDefault.ToString(); }                  catch (Exception) { LocalCursorsDefault = "<No Access>"; }
            //try { LogReuseWaitStatus = database.LogReuseWaitStatus.ToString(); }                    catch(Exception) { LogReuseWaitStatus = "<No Access>";  }
            //try { MirroringFailoverLogSequenceNumber = database.MirroringFailoverLogSequenceNumber.ToString(); }  catch(Exception) { MirroringFailoverLogSequenceNumber = "<No Access>";  }
            //try { MirroringID = database.MirroringID.ToString(); }                                  catch(Exception) { MirroringID = "<No Access>";  }
            //try { MirroringPartner = database.MirroringPartner.ToString(); }                        catch(Exception) { MirroringPartner = "<No Access>";  }
            //try { MirroringPartnerInstance = database.MirroringPartnerInstance.ToString(); }        catch(Exception) { MirroringPartnerInstance = "<No Access>";  }
            //try { MirroringRedoQueueMaxSize = database.MirroringRedoQueueMaxSize.ToString(); }      catch(Exception) { MirroringRedoQueueMaxSize = "<No Access>";  }
            //try { MirroringRoleSequence = database.MirroringRoleSequence.ToString(); }              catch(Exception) { MirroringRoleSequence = "<No Access>";  }
            //try { MirroringSafetyLevel = database.MirroringSafetyLevel.ToString(); }                catch(Exception) { MirroringSafetyLevel = "<No Access>";  }
            //try { MirroringSafetySequence = database.MirroringSafetySequence.ToString(); }          catch(Exception) { MirroringSafetySequence = "<No Access>";  }
            //try { MirroringStatus = database.MirroringStatus.ToString(); }                          catch(Exception) { MirroringStatus = "<No Access>";  }
            //try { MirroringTimeout = database.MirroringTimeout.ToString(); }                        catch(Exception) { MirroringTimeout = "<No Access>";  }
            //try { MirroringWitness = database.MirroringWitness.ToString(); }                        catch(Exception) { MirroringWitness = "<No Access>";  }
            //try { MirroringWitnessStatus = database.MirroringWitnessStatus.ToString(); }            catch(Exception) { MirroringWitnessStatus = "<No Access>";  }
            try
            {
                Name = database.Name;
            }
            catch (Exception)
            {
                Name = "<No Access>";
            }
            try { NumericRoundAbortEnabled = database.NumericRoundAbortEnabled.ToString(); }        catch (Exception) { NumericRoundAbortEnabled = "<No Access>"; }
            try { Owner = database.Owner; }                                                         catch (Exception) { Owner = "<No Access>"; }
            try { PageVerify = database.PageVerify.ToString(); }                                    catch (Exception) { PageVerify = "<No Access>"; }
            try { Parent = database.Parent.Name; }                                                  catch (Exception) { Parent = "<No Access>"; }
            try { PrimaryFilePath = database.PrimaryFilePath.ToString(); }                          catch (Exception) { PrimaryFilePath = "<No Access>"; }
            try { QuotedIdentifiersEnabled = database.QuotedIdentifiersEnabled.ToString(); }        catch (Exception) { QuotedIdentifiersEnabled = "<No Access>"; }
            try { ReadOnly = database.ReadOnly.ToString(); }                                        catch (Exception) { ReadOnly = "<No Access>"; }
            //try { RecoveryForkGuid = database.RecoveryForkGuid.ToString(); }                        catch(Exception) { RecoveryForkGuid = "<No Access>"; }
            try { RecoveryModel = database.RecoveryModel.ToString(); }                              catch (Exception) { RecoveryModel = "<No Access>"; }
            try { RecursiveTriggersEnabled = database.RecursiveTriggersEnabled.ToString(); }        catch (Exception) { RecursiveTriggersEnabled = "<No Access>"; }
            try { Size = database.Size.ToString(); }                                                catch (Exception) { Size = "<No Access>"; }
            //try { SnapshotIsolationState = database.SnapshotIsolationState.ToString(); }            catch(Exception) { SnapshotIsolationState = "<No Access>"; }
            try { SpaceAvailable = database.SpaceAvailable.ToString(); }                            catch (Exception) { SpaceAvailable = "<No Access>"; }
            try { State = database.State.ToString(); }                                              catch (Exception) { State = "<No Access>"; }
            try { Status = database.Status.ToString(); }                                            catch (Exception) { Status = "<No Access>"; }
            try { UserName = database.UserName.ToString(); }                                        catch (Exception) { UserName = "******"; }
            try { Version = database.Version.ToString(); }                                          catch (Exception) { Version = "<No Access>"; }

            try { ExtendedProperties = database.ExtendedProperties; }           catch (Exception)  {       }

            Common.WriteToDebugWindow(string.Format(" Exit SMOH.{0}({1}) Exit", "Database", database), startTime);
        }
Exemplo n.º 11
0
        private void Create()
        {
            this.LogTaskMessage(string.Format(CultureInfo.CurrentCulture, "Creating Database: {0}", this.DatabaseItem.ItemSpec));
            if (this.CheckDatabaseExists())
            {
                if (this.Force)
                {
                    this.Delete();
                }
                else
                {
                    this.Log.LogError(string.Format(CultureInfo.CurrentCulture, "Database already exists: {0}. Set Force to true to delete an existing Database.", this.DatabaseItem.ItemSpec));
                    return;
                }
            }

            SMO.Database newDatabase = new SMO.Database(this.sqlServer, this.DatabaseItem.ItemSpec);
            if (this.DataFilePath != null)
            {
                FileGroup fileGroup = new FileGroup(newDatabase, this.FileGroupName);
                DataFile dataFile = new DataFile(fileGroup, this.DatabaseItem.ItemSpec, this.DataFilePath.GetMetadata("FullPath"));
                fileGroup.Files.Add(dataFile);
                newDatabase.FileGroups.Add(fileGroup);
            }

            if (this.LogFilePath != null)
            {
                if (string.IsNullOrEmpty(this.LogName))
                {
                    this.LogName = this.DatabaseItem.ItemSpec + "_log";
                }

                LogFile logFile = new LogFile(newDatabase, this.LogName, this.LogFilePath.GetMetadata("FullPath"));
                newDatabase.LogFiles.Add(logFile);
            }
            
            if (!string.IsNullOrEmpty(this.Collation))
            {
                newDatabase.Collation = this.Collation;
            }

            newDatabase.Create();
        }
Exemplo n.º 12
0
        /// <summary>
        /// Function that adds a new user with a new database (copied from admin account).
        /// </summary>
        public void _AddUserCommand()
        {
            Views.Utils.NewLogInDialog newLogInDialog = new Views.Utils.NewLogInDialog("Please enter the new user login data!");

            if (newLogInDialog.ShowDialog() == true && newLogInDialog.logIn.Password != "" && newLogInDialog.logIn.Password != "")
            {
                // Create database
                Microsoft.SqlServer.Management.Smo.Database db = new Microsoft.SqlServer.Management.Smo.Database(server, newLogInDialog.logIn.UserName + "_db");
                db.Create();

                // Create login & user
                Login login = new Login(server, newLogInDialog.logIn.UserName);
                login.LoginType = LoginType.SqlLogin;
                login.Create(newLogInDialog.logIn.Password);

                User user = new User(db, newLogInDialog.logIn.UserName);
                user.Login = newLogInDialog.logIn.UserName;
                user.Create();

                trace.Value.TraceEvent(TraceEventType.Information, 0, "Created new User '" + user.Login + "'");

                // Creating database permission Sets
                DatabasePermissionSet databasePermissionSet = new DatabasePermissionSet();
                databasePermissionSet.Add(DatabasePermission.Insert);
                databasePermissionSet.Add(DatabasePermission.Update);
                databasePermissionSet.Add(DatabasePermission.Select);
                databasePermissionSet.Add(DatabasePermission.Delete);

                // Granting Database Permission Sets to Roles
                db.Grant(databasePermissionSet, newLogInDialog.logIn.UserName);

                trace.Value.TraceEvent(TraceEventType.Information, 0, "Granted permissions to User '" + user.Login + "'");

                // Copy database
                Microsoft.SqlServer.Management.Smo.Database adminDB = server.Databases[AdminLogIn.UserName + "_db"];
                Transfer transfer = new Transfer(adminDB);

                transfer.CopyAllTables = true;
                transfer.Options.WithDependencies = true;
                transfer.Options.DriAll = true;
                transfer.DestinationDatabase = newLogInDialog.logIn.UserName + "_db";
                transfer.DestinationServer = server.Name;
                transfer.DestinationLoginSecure = false;
                transfer.DestinationLogin = AdminLogIn.UserName;
                transfer.DestinationPassword = AdminLogIn.Password;
                transfer.CopySchema = true;
                transfer.TransferData();

                trace.Value.TraceEvent(TraceEventType.Information, 0, "Copied default database to User '" + user.Login + "'");

                FillUserList();
            }
        }
Exemplo n.º 13
0
        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;
            }
        }
Exemplo n.º 14
0
        public void bgwValidateConnection_DoWorkHandler(object sender, DoWorkEventArgs e)
        {
            try
            {
                List<CheckedListItem> backupTables = e.Argument as List<CheckedListItem>;
                Smo.Server srvDestination = new Smo.Server(Destination.ServerConnection);
                // 如果Db不存在
                if (!srvDestination.Databases.Contains(Destination.Database))
                {
                    #region Create Database and Copy Table sechma
                    Smo.Database newdb = new Smo.Database(srvDestination, Destination.Database);
                    newdb.Create();
                    Smo.Server srvSource = new Smo.Server(Source.ServerConnection);
                    Smo.Database dbSource = srvSource.Databases[Source.Database];
                    Smo.Transfer transfer = new Smo.Transfer(dbSource);
                    transfer.CopyAllUsers = true;
                    transfer.CopyAllObjects = true;
                    transfer.CopyAllTables = false;
                    transfer.CopyData = false;
                    transfer.CopySchema = true;
                    transfer.Options.WithDependencies = true;
                    transfer.Options.DriAll = true;
                    transfer.Options.ContinueScriptingOnError = false;
                    // Create all table when database not exist
                    foreach (var tbl in dbSource.Tables)
                    {
                        transfer.ObjectList.Add(tbl);
                    }
                    transfer.DestinationServer = Destination.Server;
                    transfer.DestinationDatabase = newdb.Name;
                    transfer.DestinationLoginSecure = Destination.LoginSecurity;
                    if (!Destination.LoginSecurity)
                    {
                        transfer.DestinationLogin = Destination.UserId;
                        transfer.DestinationPassword = Destination.Password;
                    }
                    transfer.TransferData();
                    #endregion

                    #region Get Source Data and Filter
                    DataSet ds = DbHelper.CopySechmaFromDatabase(Source.ConnectionString());
                    List<string> sortTables = DbHelper.Fill(Source.ConnectionString(), ds, backupTables.Select(b => b.Name).ToList());
                    if (ds.Tables["Jobs"].Rows.Count > 0)
                    {
                        ds.Tables["Jobs"].Rows.Cast<DataRow>().LastOrDefault().Delete(); // Always delete last record.
                        ds.Tables["Jobs"].AcceptChanges();
                        if (IsDateFiltration)
                        {
                            ds.Tables["Jobs"].Rows.Cast<DataRow>().Where(j => (DateTime)j["Date"] < DateFrom || (DateTime)j["Date"] > DateTo).ToList().ForEach(j => j.Delete());
                        }
                        foreach (var tbl in sortTables)
                        {
                            ds.Tables[tbl].AcceptChanges();
                        }
                    }
                    #endregion

                    #region Execute SqlBulk Copy
                    foreach (string tbl in sortTables)
                    {
                        DbHelper.ExecuteSqlBulk(Destination.ConnectionString(), ds.Tables[tbl]);
                        this.Progress += 100 / backupTables.Count;
                        if (DbHelper.SqlBulkLog.Count > 0)
                        {
                            this.Log += DbHelper.SqlBulkLog.LastOrDefault() + Environment.NewLine;
                        }
                    }
                    #endregion
                }
                else
                {
                    if (CheckVersion())
                    {
                        // TODO: Job 這邊無法判斷

                        #region Get Source Data and Filter date range
                        DataSet ds = DbHelper.CopySechmaFromDatabase(Source.ConnectionString());
                        List<string> sortTables = DbHelper.Fill(Source.ConnectionString(), ds, backupTables.Select(b => b.Name).ToList());
                        if (ds.Tables["Jobs"].Rows.Count > 0)
                        {
                            ds.Tables["Jobs"].Rows.Cast<DataRow>().LastOrDefault().Delete(); // Always delete last record.
                            ds.Tables["Jobs"].AcceptChanges();
                            if (IsDateFiltration)
                            {
                                ds.Tables["Jobs"].Rows.Cast<DataRow>().Where(j => (DateTime)j["Date"] < DateFrom || (DateTime)j["Date"] > DateTo).ToList().ForEach(j => j.Delete());
                            }
                            foreach (var tbl in sortTables)
                            {
                                ds.Tables[tbl].AcceptChanges();
                            }
                        }
                        #endregion

                        #region Get destination PK list of table exists and modify for merge
                        // filter override table don't modify key
                        List<string> overrideTable = backupTables.Where(b => b.IsOverride == true).Select(b => b.Name).ToList();
                        foreach (string tbl in sortTables)
                        {
                            if (!overrideTable.Contains(tbl))
                            {
                                string keycolumn = DbHelper.PrimaryKeyColumn(Destination.ConnectionString(), tbl);
                                string sql = string.Format("Select TOP 1 {0} From {1} Order By {0} DESC", keycolumn, tbl);
                                int? lastkey = (int?)(DbHelper.ReadOne(Destination.ConnectionString(), sql));
                                if (lastkey != null)
                                {
                                    int newkey = (int)lastkey + 1;
                                    int row = ds.Tables[tbl].Rows.Count;
                                    ds.Tables[tbl].Columns[keycolumn].ReadOnly = false;

                                    #region Delete Duplicate First
                                    for (int i = row - 1; i >= 0; i--)
                                    {
                                        // check duplicate
                                        string sqlCheckDataDuplicate;
                                        int? duplicateNum;
                                        switch (tbl)
                                        {
                                            case "MCS":
                                                // NOTE: MCS sechma will appear many wrong situation.
                                                string sName = ds.Tables[tbl].Rows[i]["sName"].ToString();
                                                string pkMCS = ds.Tables[tbl].Rows[i]["pkMCS"].ToString();
                                                sqlCheckDataDuplicate = string.Format("Select Count(*) From MCS Where sName='{0}' AND pkMCS='{1}'", sName, pkMCS);
                                                duplicateNum = (int?)(DbHelper.ReadOne(Destination.ConnectionString(), sqlCheckDataDuplicate));
                                                if (duplicateNum > 0)
                                                {
                                                    ds.Tables[tbl].Rows[i].Delete();
                                                }
                                                break;
                                            default:

                                                break;
                                        }
                                        ds.Tables[tbl].AcceptChanges();
                                    }
                                    #endregion

                                    #region Change KEY
                                    row = ds.Tables[tbl].Rows.Count;
                                    for (int i = row - 1; i >= 0; i--)
                                    {

                                        ds.Tables[tbl].Rows[i][keycolumn] = newkey + i;
                                    }
                                    #endregion

                                    ds.Tables[tbl].AcceptChanges();
                                }
                            }
                        }
                        ds.AcceptChanges();
                        #endregion

                        #region Delete override table data
                        List<string> clearTable = backupTables.Where(b => b.IsOverride == true).Select(b => b.Name).ToList();
                        DeleteDestinationOverride(clearTable);
                        #endregion

                        #region Execute SqlBulk Copy
                        foreach (string tbl in sortTables)
                        {
                            DbHelper.ExecuteSqlBulk(Destination.ConnectionString(), ds.Tables[tbl]);
                            this.Progress += 100 / backupTables.Count;
                            if (DbHelper.SqlBulkLog.Count > 0)
                            {
                                this.Log += DbHelper.SqlBulkLog.LastOrDefault() + Environment.NewLine;
                            }
                        }
                        #endregion
                    }
                    else
                    {
                        this.Log += "*** Database upgrade failed ***" + Environment.NewLine;
                        e.Result = false;
                    }
                }
            }
            catch (Exception ex)
            {
                this.Log += "An error occurred during backup database." + Environment.NewLine;
                this.Log += ex.Message.ToString() + Environment.NewLine;
                e.Result = false;
            }
            // DONE: 1.   Check destination database exsits.
            // DONE: 2.   If No date range, use transfer copy all database.
            // DONE: 2-1. If use date range, DataTable.Select(); filter Jobs key (klKey) and filter another table has FK by Jobs (fkJobKey, klJobKey)
            // DONE: 2-2. Use Sqlbulk copy datatable
            // DONE: 3.   If YES  Check db version
            // CheckVersion();
            // DONE: 3-1. Source > Destination => upgrade scripts
            // DONE: 3-2. Source == Destination => Run step 4 for merge data.
            // DONE: 3-3. Source < Destination => false; alert message and block;
            // DONE: 4.   Deal table releationship PK/FK to create DataSet & Datatable from Source.
            // DONE: 5.   If table of ObservTable selected get record of Destination last PK int.
            //            List<Record> Record.LastKey, Record.TableName, Record.PKColumnName
            // TODO: 6.   DataSet.Fill(); get data and filter date range.
            // TODO: 7.   Use Sqlbulk copy datatable.
        }
 public bool SelectDatabase(string DatabaseName)
 {
     if (_srv.Databases.Contains(DatabaseName))
     {
         _db = _srv.Databases[DatabaseName];
         return true;
     }
     else
     {
         return false;
     }
 }
Exemplo n.º 16
0
 private static void CreateInternal(string connectionString)
 {
     var builder = new SqlConnectionStringBuilder(connectionString);
     Server server = new Server(builder.DataSource);
     Microsoft.SqlServer.Management.Smo.Database database = new Microsoft.SqlServer.Management.Smo.Database(server, builder.InitialCatalog);
     database.Create();
 }
Exemplo n.º 17
0
        private void RunBackup()
        {
            // Flow 1 Destination Dataabase not exists.
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(_source_connstring);
             /* SOURCE Database */
            string s_server = builder["Server"].ToString();
            string s_username = builder["User ID"].ToString();
            string s_pwd = builder["Password"].ToString();
            string s_db = cmbSourceDatabases.SelectedItem.ToString();

            /* DESTINATION Database */
            builder = new SqlConnectionStringBuilder(_target_connstring);
            string d_server = builder["Server"].ToString();
            string d_username = builder["User ID"].ToString();
            string d_pwd = builder["Password"].ToString();
            string d_db = txtBackupDatabaseName.Text.Trim();
            ServerConnection conn;
            if (string.IsNullOrEmpty(s_username))
            {
                conn = new ServerConnection(s_server);
            }
            else
            {
                conn = new ServerConnection(s_server, s_username, s_pwd);
            }
            Smo.Server source_srv = new Smo.Server(conn);
            Smo.Database source_db = source_srv.Databases[s_db];

            Smo.Transfer transfer = new Smo.Transfer(source_db);
            transfer.CopyAllUsers = true;
            transfer.CreateTargetDatabase = false;
            transfer.CopyAllObjects = false;
            transfer.CopyAllTables = false;
            transfer.CopyData = true;
            // transfer.CopySchema = true;
            transfer.Options.WithDependencies = true;
            transfer.Options.DriAll = true;
            transfer.Options.ContinueScriptingOnError = false;
            foreach (var tbl in _tables.Where(x => x.IsChecked == true))
            {
                transfer.ObjectList.Add(source_db.Tables[tbl.Name]);

            }

            //use following code if want to create destination databaes runtime
            ServerConnection d_conn;
            if (string.IsNullOrEmpty(d_username))
            {
                d_conn = new ServerConnection(d_server);
            }
            else
            {
                d_conn = new ServerConnection(d_server, d_username, d_pwd);
            }
            Smo.Server destination_srv = new Smo.Server(d_conn);
            // When database not exists backup all
            if (!destination_srv.Databases.Contains(d_db))
            {
                // transfer.CreateTargetDatabase = true;
                transfer.DestinationLoginSecure = false;
                transfer.DestinationServer = d_server;
                if (!string.IsNullOrEmpty(d_username) && !string.IsNullOrEmpty(d_pwd))
                {
                    transfer.DestinationLogin = d_username;
                    transfer.DestinationPassword = d_pwd;
                }
                else
                {
                    transfer.DestinationLoginSecure = true;
                }
                transfer.DestinationDatabase = d_db;

                    Smo.Database newdb = new Smo.Database(destination_srv, d_db);
                    newdb.Create();

                    if (!(bool)chkBackupDateRange.IsChecked)
                    {
                        transfer.ScriptTransfer();
                        transfer.TransferData();
                    }
                    else
                    {
                        transfer.CopySchema = true;
                        transfer.CopyData = false;
                        transfer.ScriptTransfer();
                        transfer.TransferData();

                        // has data range
                        using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Suppress))
                        {
                            // 大量寫入
                            // Solve Somethiing
                            // http://msdn.microsoft.com/zh-tw/library/aa561924%28v=bts.10%29.aspx
                            // http://www.died.tw/2009/04/msdtc.html
                            //
                            using (SqlConnection bulk_conn = new SqlConnection(this._target_connstring))
                            {
                                // step 1 check target tables
                                List<string> tableList = new List<string>();
                                try
                                {
                                    bulk_conn.Open();
                                }
                                catch (SqlException exp)
                                {
                                    throw new InvalidOperationException("Data could not be read", exp);
                                }
                                SqlCommand cmd = new SqlCommand();
                                cmd.Connection = bulk_conn;
                                cmd.CommandText = "SELECT name FROM sys.tables WHERE is_ms_shipped = 0";

                                SqlDataReader dr = cmd.ExecuteReader();
                                tableList.Clear();
                                while (dr.Read())
                                {
                                    tableList.Add(dr[0].ToString());
                                }
                                // Jobs 和 MCS 一定要有所以排除 下面會直接跑。
                                tableList.Remove("Jobs");
                                tableList.Remove("MCS");
                                dr.Close();
                                bulk_conn.Close();

                                // TODO: data always full
                                Dictionary<string, DataTable> dts = new Dictionary<string, DataTable>();

                                using (SqlConnection c = new SqlConnection(this._source_connstring))
                                {
                                    c.Open();
                                    // 1. 先取得 Jobs 完成 Jobs 轉移 要先撈Job才知道時間 所以流程為
                                    // get Jobs -> get MCS -> write MCS -> write Jobs
                                    string query_filter_datarange = string.Format("SELECT * FROM Jobs Where Date Between '{0}' and '{1}'", dpFrom.SelectedDate.Value.ToShortDateString(), dpTo.SelectedDate.Value.ToShortDateString());
                                    using (SqlDataAdapter da = new SqlDataAdapter(query_filter_datarange, c))
                                    {
                                        dts["Jobs"] = new DataTable();
                                        da.Fill(dts["Jobs"]);
                                    }
                                    var jobKeys = from job in dts["Jobs"].AsEnumerable()
                                                  select job.Field<int>("klKey");
                                    string condiction = string.Join(",", jobKeys.Select(j => j.ToString()).ToArray());
                                    // 後面都是跟著這個條件
                                    condiction = condiction.Trim();

                                    // 2-0. 因為 Jobs KEY 綁 MCS 所以要先撈MCS
                                    string query_mcs = string.Format("Select * From MCS Where pkMCS IN ({0})",condiction);
                                    using (SqlDataAdapter da = new SqlDataAdapter(query_mcs, c))
                                    {
                                        dts["MCS"] = new DataTable();
                                        da.Fill(dts["MCS"]);
                                    }
                                    // write MCS
                                    using (SqlBulkCopy mySbc = new SqlBulkCopy(this._target_connstring, SqlBulkCopyOptions.KeepIdentity))
                                    {

                                        // bulk_conn.Open();
                                        //設定
                                        mySbc.BatchSize = 10000; //批次寫入的數量
                                        mySbc.BulkCopyTimeout = 60; //逾時時間

                                        //處理完後丟出一個事件,或是說處理幾筆後就丟出事件
                                        //mySbc.NotifyAfter = DTableList.Rows.Count;
                                        //mySbc.SqlRowsCopied += new SqlRowsCopiedEventHandler(mySbc_SqlRowsCopied);

                                        // 更新哪個資料表
                                        mySbc.DestinationTableName = "MCS";
                                        foreach (var item in dts["MCS"].Columns.Cast<DataColumn>())
                                        {
                                            mySbc.ColumnMappings.Add(item.ColumnName, item.ColumnName);
                                        }

                                        //開始寫入
                                        mySbc.WriteToServer(dts["MCS"]);

                                        //完成交易
                                        //scope.Complete();
                                    }
                                    // write Jobs
                                    using (SqlBulkCopy mySbc = new SqlBulkCopy(this._target_connstring, SqlBulkCopyOptions.KeepIdentity))
                                    {
                                        // bulk_conn.Open();
                                        //設定
                                        mySbc.BatchSize = 10000; //批次寫入的數量
                                        mySbc.BulkCopyTimeout = 60; //逾時時間

                                        //處理完後丟出一個事件,或是說處理幾筆後就丟出事件
                                        //mySbc.NotifyAfter = DTableList.Rows.Count;
                                        //mySbc.SqlRowsCopied += new SqlRowsCopiedEventHandler(mySbc_SqlRowsCopied);

                                        // 更新哪個資料表
                                        mySbc.DestinationTableName = "Jobs";
                                        foreach (var item in dts["Jobs"].Columns.Cast<DataColumn>())
                                        {
                                            mySbc.ColumnMappings.Add(item.ColumnName, item.ColumnName);
                                        }

                                        //開始寫入
                                        mySbc.WriteToServer(dts["Jobs"]);

                                        //完成交易
                                        //scope.Complete();
                                    }
                                    // 2. 撈出所有選取的 Table 欄位 為了判斷誰有 klJobKey fkJobKey

                                    foreach (var tableName in tableList)
                                    {

                                        dts[tableName] = new DataTable();
                                        string query = "";
                                        using (SqlConnection c1 = new SqlConnection(this._source_connstring))
                                        {
                                            // 2-1. 先撈出所有欄位
                                            SqlCommand command = new SqlCommand();
                                            command.Connection = c1;
                                            string query_column = string.Format("Select * From syscolumns Where id=OBJECT_ID(N'{0}')", tableName);
                                            command.CommandText = query_column;
                                            c1.Open();
                                            string fkName = "";
                                            using (var reader = command.ExecuteReader())
                                            {
                                                while (reader.Read())
                                                {
                                                    string cName = reader["name"].ToString();
                                                    if (cName == "fkJobKey")
                                                    {
                                                        fkName = "fkJobKey";
                                                        break;
                                                    }
                                                    if (cName == "klJobKey")
                                                    {
                                                        fkName = "klJobKey";
                                                        break;
                                                    }
                                                }

                                                // 2-2. 判斷欄位有FK的SQL query statement 加入條件。
                                                if (fkName == "fkJobKey")
                                                {
                                                    query = string.Format("Select * From [{0}] Where fkJobKey IN ({1}) ", tableName, condiction);
                                                }
                                                else if (fkName == "klJobKey")
                                                {
                                                    query = string.Format("Select * From [{0}] Where klJobKey IN ({1}) ", tableName, condiction);
                                                }
                                                else
                                                {
                                                    query = string.Format("Select * From [{0}]", tableName);

                                                }
                                            }
                                            using (SqlDataAdapter da = new SqlDataAdapter(query, c1))
                                            {
                                                da.Fill(dts[tableName]);
                                            }

                                        }

                                        // 3. 如果 FK 有 Job Key 過濾移除
                                        /// || dts[tableName].Columns.Contains("klJobKey")
                                        //if (dts[tableName].Columns.Contains("fkJobKey"))
                                        //{
                                        //    // var rows = dts[tableName].Select("fkJobKey in ")
                                        //    var dealtable = from tbl in dts[tableName].AsEnumerable()
                                        //                    select tbl.Field<int>("fkJobKey");

                                        //    var filtration = dealtable.Except(jobKeys);

                                        //    var rows = from tbl in dts[tableName].AsEnumerable()
                                        //               where filtration.Any(f => f == tbl.Field<int>("fkJobKey"))
                                        //               select tbl;
                                        //    foreach (DataRow r in rows.ToArray())
                                        //    {
                                        //        dts[tableName].Rows.Remove(r);
                                        //    }
                                        //}
                                        //if (dts[tableName].Columns.Contains("klJobKey"))
                                        //{
                                        //    // var rows = dts[tableName].Select("fkJobKey in ")
                                        //    var dealtable = from tbl in dts[tableName].AsEnumerable()
                                        //                    select tbl.Field<int>("klJobKey");

                                        //    var filtration = dealtable.Except(jobKeys);

                                        //    var rows = from tbl in dts[tableName].AsEnumerable()
                                        //               where filtration.Any(f => f == tbl.Field<int>("klJobKey"))
                                        //               select tbl;
                                        //    foreach (DataRow r in rows.ToArray())
                                        //    {
                                        //        dts[tableName].Rows.Remove(r);
                                        //    }
                                        //}
                                        // 4. 跑轉移
                                        using (SqlBulkCopy mySbc = new SqlBulkCopy(this._target_connstring, SqlBulkCopyOptions.KeepIdentity))
                                        {
                                            // bulk_conn.Open();
                                            //設定
                                            mySbc.BatchSize = 10000; //批次寫入的數量
                                            mySbc.BulkCopyTimeout = 60; //逾時時間

                                            //處理完後丟出一個事件,或是說處理幾筆後就丟出事件
                                            //mySbc.NotifyAfter = DTableList.Rows.Count;
                                            //mySbc.SqlRowsCopied += new SqlRowsCopiedEventHandler(mySbc_SqlRowsCopied);

                                            // 更新哪個資料表

                                            mySbc.DestinationTableName = tableName;
                                            foreach (var item in dts[tableName].Columns.Cast<DataColumn>())
                                            {
                                                mySbc.ColumnMappings.Add(item.ColumnName, item.ColumnName);
                                            }
                                            //開始寫入
                                            mySbc.WriteToServer(dts[tableName]);
                                        }
                                    }
                                }
                            }
                            //完成交易
                            scope.Complete();
                        }
                    }

                MessageBox.Show("Done");
            }
            else
            {

                // database exists but:
                //
                #region * situation 1 : no [table] no [data range]
                // step 1. 一樣用transfer傳遞結構開表格
                // transfer.CreateTargetDatabase = true;
                transfer.DestinationLoginSecure = false;
                transfer.DestinationServer = d_server;
                if (!string.IsNullOrEmpty(d_username) && !string.IsNullOrEmpty(d_pwd))
                {
                    transfer.DestinationLogin = d_username;
                    transfer.DestinationPassword = d_pwd;
                }
                else
                {
                    transfer.DestinationLoginSecure = true;
                }
                transfer.DestinationDatabase = d_db;
                // step 2 判斷哪些 Table 沒有的 先靠 transfer 開
                int intTableToMove = transfer.ObjectList.Count;
                using (SqlConnection connCheckTable = new SqlConnection(this._target_connstring))
                {
                    connCheckTable.Open();
                    SqlCommand cmdCheckTable = new SqlCommand();
                    cmdCheckTable.Connection = connCheckTable;
                    cmdCheckTable.CommandText = "SELECT name FROM sys.tables WHERE is_ms_shipped = 0";
                    // target 已經有的 Table 先拉掉

                    using (var dr = cmdCheckTable.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            transfer.ObjectList.Remove(source_db.Tables[dr[0].ToString()]);
                        }
                    }
                }
                // 表示完全沒有 Table 空有DB 也沒有日期限制 就直接全部複製。
                if (transfer.ObjectList.Count == 0 && !((bool)chkBackupDateRange.IsChecked))
                {
                    transfer.ScriptTransfer();
                    transfer.TransferData();
                }
                else //否則就把target不存在的Table都開完 資料再一次處理
                {
                    transfer.CopySchema = true;
                    transfer.CopyData = false;
                    transfer.ScriptTransfer();
                    // transfer.TransferData();

                    //step 3. 開始搬資料 分成有DateRange和沒有的
                    if (!((bool)chkBackupDateRange.IsChecked)) //沒有時間條件的話
                    {
                        using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Suppress))
                        {
                            using (SqlConnection bulk_conn = new SqlConnection(this._target_connstring))
                            {
                                // step 1 check target tables
                                List<string> tableList = new List<string>();
                                try
                                {
                                    bulk_conn.Open();
                                }
                                catch (SqlException exp)
                                {
                                    throw new InvalidOperationException("Data could not be read", exp);
                                }
                                SqlCommand cmd = new SqlCommand();
                                cmd.Connection = bulk_conn;
                                cmd.CommandText = "SELECT name FROM sys.tables WHERE is_ms_shipped = 0";

                                SqlDataReader dr = cmd.ExecuteReader();
                                tableList.Clear();
                                while (dr.Read())
                                {
                                    tableList.Add(dr[0].ToString());
                                }
                                // Jobs 和 MCS 一定要有所以排除 下面會直接跑。
                                //tableList.Remove("Jobs");
                                //tableList.Remove("MCS");
                                dr.Close();
                                bulk_conn.Close();
                                // TODO: 2013/4/13 把所有 Dictionary 換成 Dataset
                                // step 2 取得所有target上的資料。
                                DataSet dsTarget = new DataSet(); // 取得 Target上面所有的 Table資料
                                Dictionary<string, int> keyTarget = new Dictionary<string, int>();// 取得Target 上面 有資料的 Table 最後一個KEY 作為換KEY的起始值
                                foreach (string tableName in tableList)
                                {
                                    string queryGetTargetNowData = string.Format("Select * From {0}", tableName);
                                    using (SqlDataAdapter da = new SqlDataAdapter(queryGetTargetNowData, bulk_conn))
                                    {
                                        da.FillSchema(dsTarget, SchemaType.Source, tableName);
                                        da.Fill(dsTarget, tableName);
                                    }
                                    string keyColumnName = dsTarget.Tables[tableName].Columns[0].ColumnName;

                                    // TODO: 取得FK的加入ForeignKeyConstraint限制
                                    foreach (var i in source_db.Tables[tableName].ForeignKeys)
                                    {
                                        string x = i.ToString();
                                    }
                                    // dsTarget.Tables[tableName].Constraints
                                    int targetJobsLastKey = dsTarget.Tables[tableName].AsEnumerable().LastOrDefault().Field<int>(keyColumnName);
                                    if (targetJobsLastKey != 0)
                                    {
                                        keyTarget.Add(tableName, targetJobsLastKey);
                                    }

                                }
                                // step 3. Target上面有資料的就換KEY 先把大家的KEY都換一輪 再來改參考的FK
                                //foreach (KeyValuePair<string, int> key in keyTarget)
                                //{
                                //    int startKey = key.Value;
                                //    foreach (DataRow item in dtsTarget[key.Key].Rows)
                                //    {
                                //        startKey++;
                                //        item[0] = startKey;
                                //    }
                                //}

                                // step 4 . 大家都換完KEY 開始換 FK

                                // step 5 . 換完 FK 排順序寫入 MCS -> JOBS -> etc

                            }
                        }
                    }
                    else // 有加時間範圍的
                    {

                    }

                }
                #endregion
                // * situation 2 : no [table] exists [data range]
                // * situation 3 : exists [table] no [data range]
                // * situation 4 : exists [table] exists [data range]

            }

            //Smo.Server srv = new Smo.Server();
            //// really you would get these from config or elsewhere:
            ////srv.ConnectionContext.Login = "******";
            ////srv.ConnectionContext.Password = "******";
            //srv.ConnectionContext.ServerInstance = @"(localdb)\v11.0";
            //string dbName = "TEST";

            //Smo.Database db = new Smo.Database();
            //db = srv.Databases[dbName];

            //StringBuilder sb = new StringBuilder();
            //List<SechmaModel> sms = new List<SechmaModel>();
            //foreach (Smo.Table tbl in db.Tables)
            //{
            //    SechmaModel model = new SechmaModel();
            //    Smo.ScriptingOptions options = new Smo.ScriptingOptions();
            //    options.ClusteredIndexes = true;
            //    options.Default = true;
            //    options.DriAll = true;
            //    options.Indexes = true;
            //    options.IncludeHeaders = true;

            //    StringCollection coll = tbl.Script(options);
            //    foreach (string str in coll)
            //    {
            //        sb.Append(str);
            //        sb.Append(Environment.NewLine);
            //    }
            //}
            //System.IO.StreamWriter fs = System.IO.File.CreateText("c:\\temp\\output.txt");
            //fs.Write(sb.ToString());
            //fs.Close();

            //Step2.  Build Table & Save FK Table
            //Step3.  SQL Statement
        }
Exemplo n.º 18
0
        private void Delete()
        {
            if (!this.VerifyDatabase())
            {
                return;
            }

            this.LogTaskMessage(string.Format(CultureInfo.CurrentCulture, "Deleting Database: {0}", this.DatabaseItem.ItemSpec));
            SMO.Database oldDatabase = new SMO.Database(this.sqlServer, this.DatabaseItem.ItemSpec);
            oldDatabase.Refresh();
            oldDatabase.Drop();
        }
Exemplo n.º 19
0
        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;
            }
        }