Backup rows tables of SQL Server database by SMO base on colums's value

• test0101

  SSC Rookie

  Points: 32

  More actions

  December 12, 2019 at 2:44 am

  #3707233

  I am following this article to backup database, but in that, will be backup all rows of all tables in database. But I want backup only rows of all tables that value of department column is '10' (in my database, all tables also having department column).

  Can someone help me the how to changes CreateScriptTable() backup. My code app is winform C#

  public void CreateScriptDataBase(string dataBaseName, string connectionString)

  {

  SqlConnection con = new SqlConnection(connectionString);

  ServerConnection serverConnection = new ServerConnection(con);

  Server server = new Server(serverConnection);

  Database database = server.Databases["" + dataBaseName + ""];

  if (database != null)

  {

  Scripter scripter = new Scripter(server);

  scripter.Options.ScriptData = true;

  scripter.Options.ScriptSchema = true;

  scripter.Options.ScriptDrops = false;

  var sb = new System.Text.StringBuilder();

  foreach (Microsoft.SqlServer.Management.Smo.Table table in database.Tables)

  {

  sb.Append("DROP TABLE " + table.Name);

  sb.Append(Environment.NewLine);

  foreach (string s in scripter.EnumScript(new Urn[] { table.Urn }))

  {

  sb.Append(s);

  sb.Append(Environment.NewLine);

  }

  string folder = Server.MapPath("~/Scripts/");

  string filename = folder + dataBaseName + ".sql";

  System.IO.StreamWriter fs = System.IO.File.CreateText(filename);

  fs.Write(sb);

  fs.Close();

  }

  }

  }

• Site Owners

  SSC Guru

  Points: 80381

  More actions

  December 13, 2019 at 3:10 am

  #3707562

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.