private static void Method1(SmoServerInfo ServerConnectionData) { // Connect to source SQL Server. Server sourceServer = new Server(new ServerConnection(new SqlConnection(ServerConnectionData.SourceServerConnection))); // Reference the database. Database db = sourceServer.Databases[ServerConnectionData.SourceDatabase]; //Database db = sourceServer.Databases[dbName]; // Define a Scripter object and set the required scripting options. Scripter scripter = new Scripter(sourceServer); scripter.Options.ScriptDrops = false; scripter.Options.WithDependencies = true; scripter.Options.Indexes = true; // To include indexes scripter.Options.DriAllConstraints = true; // to include referential constraints in the script // Iterate through the tables in database and script each one. Display the script. foreach (Table tb in db.Tables) { // check if the table is not a system table if (tb.IsSystemObject == false) { Console.WriteLine("-- Scripting for table " + tb.Name); // Generating script for table tb System.Collections.Specialized.StringCollection sc = scripter.Script(new Urn[] { tb.Urn }); foreach (string st in sc) { //ado.net to destination Console.WriteLine(st);//SqlCommand.ExecuteNonQuery(); } Console.WriteLine("--"); } } }
private void GetDbConnectioninfo(string databaseOriginType, ref SmoServerInfo smoServerInfo) { string connectionString = string.Empty; string ServerName = string.Empty; string DatabaseName = string.Empty; using (var dialog = new DataConnectionDialog()) { // dialog settings dialog.StartPosition = FormStartPosition.CenterParent; dialog.TopLevel = true; dialog.Title = $"Choose the {databaseOriginType} database"; dialog.ChangeDataSourceTitle = $"Choose the {databaseOriginType} database"; // If you want the user to select from any of the available data sources, do this: DataSource.AddStandardDataSources(dialog); // OR, if you want only certain data sources to be available // (e.g. only SQL Server), do something like this instead: dialog.DataSources.Add(DataSource.SqlDataSource); dialog.DataSources.Add(DataSource.SqlFileDataSource); dialog.SelectedDataSource = DataSource.SqlDataSource; dialog.SelectedDataProvider = DataProvider.SqlDataProvider; // The way how you show the dialog is somewhat unorthodox; `dialog.ShowDialog()` // would throw a `NotSupportedException`. Do it this way instead: DialogResult userChoice = DataConnectionDialog.Show(dialog); // Return the resulting connection string if a connection was selected: if (userChoice == DialogResult.OK) { connectionString = dialog.ConnectionString; using (DbConnection connection = new SqlConnection(connectionString)) { ServerName = connection.DataSource.ToUpper(); DatabaseName = connection.Database; } } else { MessageBox.Show("Error happened"); } } if (databaseOriginType.Equals("Source", StringComparison.OrdinalIgnoreCase)) { smoServerInfo.SourceServerConnection = connectionString; smoServerInfo.SourceServer = ServerName; smoServerInfo.SourceDatabase = DatabaseName; } else { smoServerInfo.DestinationServerConnection = connectionString; smoServerInfo.DestinationServer = ServerName; smoServerInfo.DestinationDatabase = DatabaseName; } }
private void btnImpExp_Click(object sender, EventArgs e) { SmoServerInfo ServerConnectionData = new SmoServerInfo(); // Get Source info GetDbConnectioninfo("Source", ref ServerConnectionData); if (string.IsNullOrEmpty(ServerConnectionData.SourceServer)) { return; } // Get destination info GetDbConnectioninfo("Destination", ref ServerConnectionData); if (string.IsNullOrEmpty(ServerConnectionData.DestinationServer)) { return; } DoImportExport(ServerConnectionData); }
private void Method2(SmoServerInfo ServerConnectionData) { // Connect to source SQL Server. Server srv = new Server(new ServerConnection(new SqlConnection(ServerConnectionData.SourceServerConnection))); // Reference the database. Database db = srv.Databases[ServerConnectionData.SourceDatabase]; //Server srv; //srv = new Server(); ////Reference the AdventureWorks2012 database //Database db; //db = srv.Databases["AdventureWorks2012"]; //Create a destination database that is to be destination database. string destinationDbname = ServerConnectionData.DestinationDatabase + "Copy1"; Database dbCopy; dbCopy = new Database(srv, destinationDbname); dbCopy.Create(); //Define a Transfer object and set the required options and properties. Transfer transfer; transfer = new Transfer(db); transfer.DataTransferEvent += Xfr_DataTransferEvent; transfer.Options.ContinueScriptingOnError = true; transfer.CopyAllTables = true; transfer.CopyAllUsers = true; transfer.Options.WithDependencies = true; transfer.DestinationDatabase = destinationDbname; transfer.DestinationServer = srv.Name; transfer.DestinationLoginSecure = true; transfer.CopySchema = true; transfer.CopyData = true; try { transfer.TransferData(); } catch (Exception ex) { string errMsg = string.Empty; if (ex.InnerException.InnerException != null) { errMsg = ex.InnerException.InnerException.Message; } else { errMsg = ex.Message; } MessageBox.Show(errMsg, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); lblStatus.Invoke((MethodInvoker) delegate { lblStatus.Text = errMsg; }); } ////Script the transfer. Alternatively perform immediate data transfer //// with TransferData method. //xfr.ScriptTransfer(); }
///<reference> /// https://docs.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/tasks/transferring-data?view=sql-server-ver15 /// https://stackoverflow.com/questions/17063248/copying-table-from-one-sql-server-to-another ///</reference> private void DoImportExport(SmoServerInfo ServerConnectionData) { //Method1(ServerConnectionData); Method2(ServerConnectionData); }