Backup only structure

[babu-narra]

Hello @adriancs2 ,

First of all, Thanks much for the wonderful solution.

Kindly suggest on the usage of parameters to dump only the structure of objects like tables, SP etc.

Alongside, is there a provision for script into multiple objects/individual files(one file per object).

Your response is much appreciated.

[adriancs2]

Yes, you can set it at:

mb.ExportInfo.TablesToBeExportedDic

Here is some explanation:
https://github.com/MySqlBackupNET/MySqlBackup.Net/wiki/Conditional-Rows-Export-for-Each-Table

Example of code:

string constr = "server=127.0.0.1;user=root;pwd=1234;database=dbtest;convertzerodatetime=true;";

using (MySqlConnection conn = new MySqlConnection(constr))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        conn.Open();
        cmd.Connection = conn;

        // assuming exporting all tables
        DataTable dtTables = new DataTable();
        cmd.CommandText = "show full tables where table_type='BASE TABLE';";
        MySqlDataAdapter da = new MySqlDataAdapter(cmd);
        da.Fill(dtTables);

        using (MySqlBackup mb = new MySqlBackup(cmd))
        {
            foreach (DataRow drTable in dtTables.Rows)
            {
                string tablename = drTable[0] + "";
                string filename = $@"C:\backup\{tablename}.sql";

                mb.ExportInfo.RecordDumpTime = false;
                mb.ExportInfo.ExportRows = false;
                mb.ExportInfo.ResetAutoIncrement = true;
                mb.ExportInfo.AddCreateDatabase = false;
                mb.ExportInfo.GetTotalRowsMode = GetTotalRowsMethod.Skip;
                mb.ExportInfo.SetDocumentHeaders(new List<string>());
                mb.ExportInfo.SetDocumentFooters(new List<string>());

                mb.ExportInfo.TablesToBeExportedDic.Clear();
                mb.ExportInfo.TablesToBeExportedDic[tablename] = $"select * from `{tablename}` where 1=2;";

                mb.ExportToFile(filename);
            }
        }

        conn.Close();
    }
}

[babu-narra]

Thanks for quick and elaborate response.

[adriancs2]

Here is an alternative method without using MySqlBackup.NET:

by using the following sql query

SHOW CREATE TABLE <table name>

read more at:
https://dev.mysql.com/doc/refman/8.0/en/show-create-table.html

Sample code:

string constr = "server=127.0.0.1;user=root;pwd=1234;database=testdb;convertzerodatetime=true;";

using (MySqlConnection conn = new MySqlConnection(constr))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        conn.Open();
        cmd.Connection = conn;

        DataTable dtTables = new DataTable();
        cmd.CommandText = "show full tables where table_type='BASE TABLE';";
        MySqlDataAdapter da = new MySqlDataAdapter(cmd);
        da.Fill(dtTables);

        foreach (DataRow drTable in dtTables.Rows)
        {
            string tablename = drTable[0] + "";
            string filename = $@"C:\backup\{tablename}.sql";

            DataTable dtTb = new DataTable();
            cmd.CommandText = $"show create table `{tablename}`;";
            da.SelectCommand = cmd;
            da.Fill(dtTb);

            string sqlCreateTable = dtTb.Rows[0]["Create Table"] + "";

            File.WriteAllText(filename, sqlCreateTable);
        }

        conn.Close();
    }
}