locked
How to write a program to command line backup of an MsSQL server RRS feed

  • Question

  • User283528319 posted

    Hi all,

    How can I write a simple software which backs up SQL server database every night at 23:00.

    which uses embedded command line prompt

    Monday, October 14, 2019 2:43 PM

Answers

  • User-719153870 posted

    Hi fatihbarut,

    I need something to produce .bak then .zip and mail it.

    I think you can easily find documents about all above three operations, such as below ones:

    I also build a simple webform demo that used above documents mentioned code which you can refer to.

    Since you know it might cause some security issues that you could do some changes to your code or even to your whole structure.

    ASPX:

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                <%--Input your connection string :<asp:TextBox ID="txtConnstring" runat="server"></asp:TextBox><br />--%>
                Input your file location<asp:TextBox ID="txtLocation" runat="server"></asp:TextBox><br />
                Input your database name<asp:TextBox ID="txtDataBase" runat="server"></asp:TextBox><br />
                Input your emailaddress<asp:TextBox ID="txtemail" runat="server"></asp:TextBox><br />
                Input your emailpassword<asp:TextBox ID="txtpass" runat="server" TextMode="Password"></asp:TextBox><br />
                Input your targetemailaddress<asp:TextBox ID="txtaddress" runat="server"></asp:TextBox><br />
                <asp:Button ID="Button1" runat="server" Text="Run" OnClick="Button1_Click" />
            </div>
        </form>
    </body>
    </html>

    CS:

    using System;
    using System.Data.SqlClient;
    using System.IO;
    using System.IO.Compression;
    using System.Net.Mail;

    protected void Button1_Click(object sender, EventArgs e) { Sendemail( CompressZip(GenerateBak())); } protected string GenerateBak() { string con = "Data Source=.;Initial Catalog=" + txtDataBase.Text + ";Integrated Security=True"; SqlConnection conn = new SqlConnection(con); conn.Open(); try { SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; //below might have sql injection issue cmd.CommandText = "USE " + txtDataBase.Text + "; BACKUP DATABASE " + txtDataBase.Text + " TO DISK = '" + txtLocation.Text + "' WITH FORMAT, MEDIANAME = 'SQLServerBackups', NAME = 'Full Backup'; "; cmd.ExecuteNonQuery(); return txtLocation.Text; } catch (Exception ex) { throw (ex); } } protected string CompressZip(string filepath) { string startPath = filepath; string zipPath = filepath.Split('.')[0].ToString() + ".zip"; if (File.Exists(zipPath)) File.Delete(zipPath); if (File.Exists(filepath)) { try { using (ZipArchive archive = ZipFile.Open(zipPath, ZipArchiveMode.Create)) { archive.CreateEntryFromFile(filepath, Path.GetFileName(filepath), CompressionLevel.Fastest); } return zipPath; } catch (Exception ex) { throw (ex); } } else { return null; } } protected void Sendemail(string zipPath) { MailMessage mail = new MailMessage(); SmtpClient SmtpServer = new SmtpClient("smtp.gmail.com"); mail.From = new MailAddress(txtemail.Text);//youremail@gmail.com mail.To.Add(txtaddress.Text);//your target email address mail.Subject = "Send a database backup zip file"; mail.Body = "mail with attachment"; Attachment attachment; attachment = new Attachment(zipPath); mail.Attachments.Add(attachment); SmtpServer.Port = 587; SmtpServer.Credentials = new System.Net.NetworkCredential(txtemail.Text, txtpass.Text); SmtpServer.EnableSsl = true; SmtpServer.Send(mail); }

    Hope that can help.

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 15, 2019 5:11 AM

All replies

  • User475983607 posted

    fatihbarut

    Hi all,

    How can I write a simple software which backs up SQL server database every night at 23:00.

    which uses embedded command line prompt

    This was covered in your similar thread.  Use the Sql Agent to create and schedule a job.

    https://support.microsoft.com/en-us/help/930615/how-to-schedule-a-database-backup-operation-by-using-sql-server-manage

    It is possible to write a command line app to do the same and use Windows scheduler to invoke the Powershell.

    https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/create-a-full-database-backup-sql-server?view=sql-server-ver15

    Or bat using osql or sqlcmd.

    https://docs.microsoft.com/en-us/sql/ssms/scripting/sqlcmd-use-the-utility?view=sql-server-ver15

    Monday, October 14, 2019 2:55 PM
  • User283528319 posted

    those are not solution

    I need something to produce .bak then .zip and mail it.

    Monday, October 14, 2019 3:40 PM
  • User475983607 posted

    those are not solution

    I need something to produce .bak then .zip and mail it.

    They are solution but you have to write a little code.  SQL Agent is the easiest, IMHO.

    Monday, October 14, 2019 5:40 PM
  • User283528319 posted

    fatihbarut

    those are not solution

    I need something to produce .bak then .zip and mail it.

    They are solution but you have to write a little code.  SQL Agent is the easiest, IMHO.

    believe me it is not :)

    Monday, October 14, 2019 5:54 PM
  • User475983607 posted

    believe me it is not :)

    The links illustrate standard patterns and practices.   You are free to design and build whatever solution you like.  Keep in mind, email is notoriously insecure.  I do not recommend emailing a bak file.  You should place the file on the SQL server or a secured network share.  I usually keep 3 days worth of backups.

    Monday, October 14, 2019 6:13 PM
  • User283528319 posted

    Keep in mind, email is notoriously insecure.

    yeah, I heard it a lot.

    But why?

    Will google (gmail) steal my precious information from .bak file?

    Monday, October 14, 2019 6:30 PM
  • User475983607 posted

    fatihbarut

    yeah, I heard it a lot.

    But why?

    There are a lot of reasons and you can Google the vulnerabilities.  The information is very open.  Email is stored on disk and most likely backed up just like any system.  Who knows how long your database backups will exist.

    fatihbarut

    Will google (gmail) steal my precious information from .bak file?

    You're purposely providing the information to Google.   I'm sure you're customers will not be pleased if they entrusted you to protect their personal or precious information. 

    Anyway, I provided best practices and that's about all I can do. 

    Monday, October 14, 2019 6:47 PM
  • User-719153870 posted

    Hi fatihbarut,

    I need something to produce .bak then .zip and mail it.

    I think you can easily find documents about all above three operations, such as below ones:

    I also build a simple webform demo that used above documents mentioned code which you can refer to.

    Since you know it might cause some security issues that you could do some changes to your code or even to your whole structure.

    ASPX:

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                <%--Input your connection string :<asp:TextBox ID="txtConnstring" runat="server"></asp:TextBox><br />--%>
                Input your file location<asp:TextBox ID="txtLocation" runat="server"></asp:TextBox><br />
                Input your database name<asp:TextBox ID="txtDataBase" runat="server"></asp:TextBox><br />
                Input your emailaddress<asp:TextBox ID="txtemail" runat="server"></asp:TextBox><br />
                Input your emailpassword<asp:TextBox ID="txtpass" runat="server" TextMode="Password"></asp:TextBox><br />
                Input your targetemailaddress<asp:TextBox ID="txtaddress" runat="server"></asp:TextBox><br />
                <asp:Button ID="Button1" runat="server" Text="Run" OnClick="Button1_Click" />
            </div>
        </form>
    </body>
    </html>

    CS:

    using System;
    using System.Data.SqlClient;
    using System.IO;
    using System.IO.Compression;
    using System.Net.Mail;

    protected void Button1_Click(object sender, EventArgs e) { Sendemail( CompressZip(GenerateBak())); } protected string GenerateBak() { string con = "Data Source=.;Initial Catalog=" + txtDataBase.Text + ";Integrated Security=True"; SqlConnection conn = new SqlConnection(con); conn.Open(); try { SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; //below might have sql injection issue cmd.CommandText = "USE " + txtDataBase.Text + "; BACKUP DATABASE " + txtDataBase.Text + " TO DISK = '" + txtLocation.Text + "' WITH FORMAT, MEDIANAME = 'SQLServerBackups', NAME = 'Full Backup'; "; cmd.ExecuteNonQuery(); return txtLocation.Text; } catch (Exception ex) { throw (ex); } } protected string CompressZip(string filepath) { string startPath = filepath; string zipPath = filepath.Split('.')[0].ToString() + ".zip"; if (File.Exists(zipPath)) File.Delete(zipPath); if (File.Exists(filepath)) { try { using (ZipArchive archive = ZipFile.Open(zipPath, ZipArchiveMode.Create)) { archive.CreateEntryFromFile(filepath, Path.GetFileName(filepath), CompressionLevel.Fastest); } return zipPath; } catch (Exception ex) { throw (ex); } } else { return null; } } protected void Sendemail(string zipPath) { MailMessage mail = new MailMessage(); SmtpClient SmtpServer = new SmtpClient("smtp.gmail.com"); mail.From = new MailAddress(txtemail.Text);//youremail@gmail.com mail.To.Add(txtaddress.Text);//your target email address mail.Subject = "Send a database backup zip file"; mail.Body = "mail with attachment"; Attachment attachment; attachment = new Attachment(zipPath); mail.Attachments.Add(attachment); SmtpServer.Port = 587; SmtpServer.Credentials = new System.Net.NetworkCredential(txtemail.Text, txtpass.Text); SmtpServer.EnableSsl = true; SmtpServer.Send(mail); }

    Hope that can help.

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 15, 2019 5:11 AM