how to write script for backup database same as that RSS

• • Reply
  test0101

  Member

  11 Points

  77 Posts

  how to write script for backup database same as that

  Jun 15, 2020 08:32 AM|test0101|LINK

  Hi all

  I have a webapp provide to multi customers. In database every customer has one id (id_customer).

  examle:

  table1(colum1, colum2, clomum3.....,id_customer )

  table2(colum1, colum2, clomum3.....,id_customer )

  table3(colum1, colum2, clomum3.....,id_customer )

  ...

  tableN(colum1, colum2, clomum3.....,id_customer )

  Now, I want: when customer login to sytem (with His_id_customer get from login custoimer.) , he can only backup his database, same as structure:

  Delete From table1 where id_customer = His_id_customer 

  SET IDENTITY_INSERT [dbo].[table1 ] ON

  INSERT [dbo].[table1 ] ([colum1], [colum2], [colum3],......, [columN]) VALUES ('v11','v12','v13',....,'Vn')

  INSERT [dbo].[table1 ] ([colum1], [colum2], [colum3],......, [columN]) VALUES ('v21','v22','v23',....,'Vn')

  SET IDENTITY_INSERT [dbo].[table1 ] OFF

  ...

  Delete From tableN where id_customer = His_id_customer
  SET IDENTITY_INSERT [dbo].[tableN ] ON

  INSERT [dbo].[tableN ] ([colum1], [colum2], [colum3],......, [columN]) VALUES ('Vn1','Vn2','Vn3',....,'VNn')

  SET IDENTITY_INSERT [dbo].[tableN ] OFF

  WHEN USER LOGIN IS ADMIN then backup all databse of all customers, like as:

  Delete From table1

  SET IDENTITY_INSERT [dbo].[table1 ] ON

  INSERT [dbo].[table1 ] ([colum1], [colum2], [colum3],......, [columN]) VALUES ('v11','v12','v13',....,'Vn')

  INSERT [dbo].[table1 ] ([colum1], [colum2], [colum3],......, [columN]) VALUES ('v21','v22','v23',....,'Vn')

  SET IDENTITY_INSERT [dbo].[table1 ] OFF

  ...

  Delete From tableN
  SET IDENTITY_INSERT [dbo].[tableN ] ON

  INSERT [dbo].[tableN ] ([colum1], [colum2], [colum3],......, [columN]) VALUES ('Vn1','Vn2','Vn3',....,'VNn')

  SET IDENTITY_INSERT [dbo].[tableN ] OFF

  In winform I success by using SMO, code:

    public void BackuptDataBase(string login_user, string fileSQL)
          {
              con = new SqlConnection(constr);
  
              string dataBaseName = con.Database.ToString();
              Microsoft.SqlServer.Management.Common.ServerConnection serverConnection = new Microsoft.SqlServer.Management.Common.ServerConnection(con);
  
              Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(serverConnection);
  
              Microsoft.SqlServer.Management.Smo.Database database = server.Databases["" + dataBaseName + ""];
  
  
              if (database != null)
              {
                  Microsoft.SqlServer.Management.Smo.Scripter scripter = new Microsoft.SqlServer.Management.Smo.Scripter(server);
                  scripter.Options.ScriptData = true;
  
                  string Drop_OR_DeleteTable, filename;
  
                  string folder = System.Web.HttpContext.Current.Server.MapPath("~/");
  
                  if (login_user == "admin")
                  {
                      scripter.Options.ScriptSchema = true;
                      Drop_OR_DeleteTable = "DROP TABLE ";
                  }
                  else
                  {
                      scripter.Options.ScriptSchema = false;
                      Drop_OR_DeleteTable = "Delete From ";
                  }
  
                  filename = folder + fileSQL;
  
                  scripter.Options.ScriptDrops = false;
                  var sb = new System.Text.StringBuilder();
  
                  foreach (Microsoft.SqlServer.Management.Smo.Table table in database.Tables)
                  {                   
  
                          sb.Append(Drop_OR_DeleteTable + table.Name);
                          sb.Append(Environment.NewLine);
                        
                          foreach (string s in scripter.EnumScript(new Microsoft.SqlServer.Management.Sdk.Sfc.Urn[] { table.Urn }))
                          {                            
                                  sb.Append(s);
                                  sb.Append(Environment.NewLine);
                              
                          }                     
                     
  
                      System.IO.StreamWriter fs = System.IO.File.CreateText(filename);
                      fs.Write(sb);
                      fs.Close();
                      
                  }
              }
          }

  please help me script by aps.net C#.