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(); } }
/// <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); } }
/// <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; }
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; }
/// <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); }
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(); }
/// <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(); } }
public void Script(string srvname, string dbName, string destination) { tbxOutput.Text = "Scripting the " + dbName + " database." + "\r\n"; if (destination == "") { destination = Environment.CurrentDirectory + "\\"; } else { if (destination[destination.Length - 1] != Convert.ToChar("\\")) { destination += "\\"; } } tbxOutput.Text += "Output directory set to " + destination + "\r\n"; /* *************************** */ /* CHECK FOR VALID DESTINATION */ /* *************************** */ tbxOutput.Text += "Checking for valid destination directory...\r\n"; if (!Directory.Exists(destination)) { throw new DirectoryNotFoundException("The specified destination directory does not exist."); } else { tbxOutput.Text += "Destination directory is valid.\r\n"; /* *********************** */ /* CREATE FOLDER STRUCTURE */ /* *********************** */ tbxOutput.Text += "Establishing folder structure...\r\n"; newFolders.Clear(); try { if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd"))) { Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd")); newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd")); tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + "\r\n"; } if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\")) { Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\"); newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\"); tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\" + "\r\n"; } if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\")) { Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\"); newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\"); tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\" + "\r\n"; } if (!Directory.Exists(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\")) { Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\"); newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\"); tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\" + "\r\n"; } if (!Directory.Exists(destination + dbName + @"\Programmability\Functions\")) { Directory.CreateDirectory(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\"); newFolders.Add(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\"); tbxOutput.Text += "Folder Created: " + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\" + "\r\n"; } } catch { throw new UnauthorizedAccessException("The program failed to create the backup folders in the specified directory. Please check security settings."); } tbxOutput.Text += "Folder structure established \r\n"; } /* *************** */ /* Generate Script */ /* *************** */ try //Wrap in try statement to catch incorrect server errors { tbxOutput.Text += "Connecting to server " + srvname + "...\r\n"; Server srv; srv = new Server(srvname); srv.ConnectionContext.LoginSecure = true; if (!srv.Databases.Contains(dbName)) { RemoveFolders();//Clean out folders creating during this run throw new ArgumentException("The specified database could not be found."); } Database db = new Database(); db = srv.Databases[dbName]; Scripter scr = new Scripter(srv); Scripter scrFullScript = new Scripter(srv); srv.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject"); /* Create Options for the scr Scripter */ ScriptingOptions options = new ScriptingOptions(); options.IncludeHeaders = true; options.AppendToFile = false; options.ToFileOnly = true; options.DriAll = true; options.IncludeDatabaseContext = true; //options.ScriptDrops = true; scr.Options = options; //Assign options to scr /* Create options for the scrFullScript Scripter */ ScriptingOptions scopFull = new ScriptingOptions(); scopFull.IncludeHeaders = true; scopFull.AppendToFile = true; scopFull.ToFileOnly = true; scopFull.DriAll = true; scopFull.IncludeDatabaseContext = true; scopFull.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\" + dbName + "_FULL.sql"; scrFullScript.Options = scopFull; //Assign options to scrFullScript /* ******************* */ /* CREATE SCRIPT FILES */ /* ******************* */ List<string> lstErrors = new List<string>(); //SCRIPT DATABASE Microsoft.SqlServer.Management.Smo.Database[] dbs = new Microsoft.SqlServer.Management.Smo.Database[1]; tbxOutput.Text += "Scripting Database: " + db + "\r\n"; dbs[0] = db; options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\" + dbName + ".sql"; scr.Script(dbs); scrFullScript.Script(dbs); tbxOutput.Text += "Scripting Database Complete.\r\n"; //SCRIPT TABLES Microsoft.SqlServer.Management.Smo.Table[] tbl = new Microsoft.SqlServer.Management.Smo.Table[1]; tbxOutput.Text += "Scripting Tables...\r\n"; for (int idx = 0; idx < db.Tables.Count; idx++) { if (!db.Tables[idx].IsSystemObject) { tbxOutput.Text += "Scripting Table: " + db.Tables[idx] + "\r\n"; tbl[0] = db.Tables[idx]; options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Tables\" + tbl[0].Name + ".sql"; scr.Script(tbl); scrFullScript.Script(tbl); } } tbxOutput.Text += "Scripting Tables Complete.\r\n"; //SCRIPT VIEWS Microsoft.SqlServer.Management.Smo.View[] vw = new Microsoft.SqlServer.Management.Smo.View[1]; tbxOutput.Text += "Scripting Views...\r\n"; for (int idx = 0; idx < db.Views.Count; idx++) { if (!db.Views[idx].IsSystemObject) { tbxOutput.Text += "Scripting View: " + db.Views[idx] + "\r\n"; vw[0] = db.Views[idx]; options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Views\" + vw[0].Name + ".sql"; scr.Script(vw); scrFullScript.Script(vw); } } tbxOutput.Text += "Scripting Views Complete.\r\n"; //SCRIPT STORED PROCEDURES Microsoft.SqlServer.Management.Smo.StoredProcedure[] proc = new Microsoft.SqlServer.Management.Smo.StoredProcedure[1]; tbxOutput.Text += "Scripting Stored Procedures...\r\n"; for (int idx = 0; idx < db.StoredProcedures.Count; idx++) { if (!db.StoredProcedures[idx].IsSystemObject) { tbxOutput.Text += "Scripting Stored Procedure: " + db.StoredProcedures[idx] + "\r\n"; proc[0] = db.StoredProcedures[idx]; options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Stored Procedures\" + proc[0].Name + ".sql"; scr.Script(proc); scrFullScript.Script(proc); } } tbxOutput.Text += "Scripting Stored Procedures Complete.\r\n"; //SCRIPT FUNCTIONS Microsoft.SqlServer.Management.Smo.UserDefinedFunction[] udf = new Microsoft.SqlServer.Management.Smo.UserDefinedFunction[1]; tbxOutput.Text += "Scripting User Defined Functions...\r\n"; for (int idx = 0; idx < db.UserDefinedFunctions.Count; idx++) { if (!db.UserDefinedFunctions[idx].IsSystemObject) { tbxOutput.Text += "Scripting User Defined Function: " + db.UserDefinedFunctions[idx] + "\r\n"; udf[0] = db.UserDefinedFunctions[idx]; options.FileName = destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\Programmability\Functions\" + udf[0].Name + ".sql"; scr.Script(udf); scrFullScript.Script(udf); } } tbxOutput.Text += "Scripting User Defined Functions complete.\r\n"; tbxOutput.Text += "Scripting master file...\r\n"; try { String strFullScript = ""; String strFullOutput = ""; tbxOutput.Text += "Retrieving full script...\r\n"; using (StreamReader sr = new StreamReader(scopFull.FileName)) { strFullScript = sr.ReadToEnd(); } tbxOutput.Text += "Full script retrieved.\r\n"; //strFullOutput = strFullScript;//Temporary string[] arrFullScript = Regex.Split(strFullScript, "GO"); foreach (string line in arrFullScript) { if(!line.StartsWith("\r\nALTER TABLE")) strFullOutput += line + "GO\r\n"; } foreach (string line in arrFullScript) { if (line.StartsWith("\r\nALTER TABLE")) strFullOutput += line + "GO\r\n"; } string strConditionalDrop = "\r\n\r\nIF DB_ID('" + dbName + "') IS NOT NULL\r\nBEGIN\r\n" + " ALTER DATABASE " + dbName + "\r\n" + " SET SINGLE_USER\r\n" + " WITH ROLLBACK IMMEDIATE;\r\n" + " DROP DATABASE " + dbName + ";\r\n" + "END\r\n"; strFullOutput = strFullOutput.Insert(strFullOutput.IndexOf("GO") + 2, strConditionalDrop); tbxOutput.Text += "Writing corrected full script...\r\n"; using (StreamWriter sw = new StreamWriter(destination + dbName + "_" + DateTime.Now.ToString("yyyyMMdd") + @"\" + dbName + "_FULL.sql")) { sw.Write(strFullOutput); } tbxOutput.Text += "Full script successfully written.\r\n"; tbxOutput.Text += "Scripting master file complete.\r\n"; } catch { tbxOutput.Text += "ERROR Scripting Master File Failed.\r\n"; lstErrors.Add("Scripting Master File Failed."); } tbxOutput.Text += "=================================\r\n"; if (lstErrors.Count == 0) tbxOutput.Text += "SCRIPTING COMPLETED SUCCESSFULLY.\r\n"; else { tbxOutput.Text += "SCRIPTING COMPLETED WITH ERRORS.\r\n"; tbxOutput.Text += String.Format("The following {0} errors occurred:\r\n", lstErrors.Count); foreach (string error in lstErrors) { tbxOutput.Text += error + "\r\n"; } } ActiveControl = btnClose; } catch (ConnectionFailureException) //Error type thrown by attempt to bind invalid server name { //throw new ConnectionFailureException("A connection to the specified server could not be made. Please check the supplied server name and try again."); tbxOutput.Text += "Connection to server failed.\r\n"; RemoveFolders();//Clean out folders creating during this run tbxOutput.Text += "A connection to the specified server could not be made. Please check the supplied server name and try again.\r\n"; } catch //General Catch-All re-throws error without further handling { RemoveFolders();//Clean out folders creating during this run throw; } }
public void 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; } }
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(); }
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 }
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(); }
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; } }