Post
Ask Question

SQL Server DataBase Backup Manually In C# WinForms

Introduction

 
In this article, we are going to learn the process of getting the database backup in C# WinForm application using Stored Procedure.
 

Create Tables and Stored Procedure

 
First, we create two tables to store database backup info.
 
Open SQL Server to create a database with any suitable name and then, create two tables and a stored procedure.
 
Here, I am using DemoTest as the database name and tblBackupInfo and tblBackupDetails as the tables name.
 
Tables Structure
  1. CREATE TABLE [dbo].[tblBackupInfo](  
  2.     [IID] [int] IDENTITY(1,1) NOT NULL,  
  3.     [DayInterval] [int] NULL,  
  4.     [NoOfFiles] [int] NULL,  
  5.     [DatabaseName] [nvarchar](500) NULL,  
  6.     [Location] [nvarchar](max) NULL,  
  7.     [SoftwareDate] [datetime] NULL,  
  8.     [LastEditDate] [datetime] NULL,  
  9.     [CreationDate] [datetime] NOT NULL  
  10. )  
  11.   
  12. CREATE TABLE [dbo].[tblBackupDetails](  
  13.     [IID] [int] IDENTITY(1,1) NOT NULL,  
  14.     [BackupName] [varchar](50) NULL,  
  15.     [Location] [varchar](500) NULL,  
  16.     [BackupDate] [datetime] NULL,  
  17.     [BackupType] [varchar](50) NULL,  
  18.     [CreationDate] [datetime] NOT NULL  
  19. ) 
Stored Procedure
  1. CREATE PROCEDURE [dbo].[DATABASE_BACKUP]  
  2. (  
  3.     @DatabaseName VARCHAR(1000) = NULL,  
  4.     @Location VARCHAR(1000) = NULL,  
  5.     @Type VARCHAR(25) = NULL,  
  6.     @BackupName VARCHAR(500) = NULL,  
  7.     @FILEPATH VARCHAR(2000) = NULL,  
  8.     @DATABASE VARCHAR(1000) = NULL,  
  9.     @DayInterval INT = NULL,  
  10.     @NoOfFiles INT = NULL,  
  11.     @SoftwareDate DATE = NULL,  
  12.     @ACTIONTYPE VARCHAR(50)  
  13. )  
  14. AS  
  15. BEGIN  
  16.     IF @ACTIONTYPE = 'BACKUP_INFO'  
  17.     BEGIN  
  18.         SELECT DATABASENAME,ISNULL(NoOfFiles,0) AS NoOfFiles,LOCATION,DayInterval FROM tblBackupInfo  
  19.         SELECT TOP 1 BackupType,BackupDate,Location FROM tblBackupDetails ORDER BY IID DESC  
  20.     END  
  21.   
  22.     IF @ACTIONTYPE = 'INSERT_BACKUP_INFO'  
  23.     BEGIN  
  24.         IF NOT EXISTS (SELECT * FROM tblBackupInfo)  
  25.         BEGIN  
  26.             INSERT INTO tblBackupInfo (DayInterval,NoOfFiles,DatabaseName,Location,SoftwareDate,CreationDate)  
  27.             VALUES (@DayInterval,@NoOfFiles,@DatabaseName,@Location,@SoftwareDate,GETDATE())  
  28.         END  
  29.         ELSE  
  30.         BEGIN  
  31.             UPDATE tblBackupInfo SET DayInterval=@DayInterval,NoOfFiles=@NoOfFiles,DatabaseName=@DatabaseName,  
  32.             Location=@Location,SoftwareDate=@SoftwareDate,LastEditDate=GETDATE()  
  33.         END  
  34.     END  
  35.   
  36.     IF @ACTIONTYPE = 'DB_BACKUP'  
  37.     BEGIN  
  38.         BEGIN TRY  
  39.             BACKUP DATABASE @DATABASE  
  40.             TO DISK = @FILEPATH;  
  41.   
  42.             INSERT INTO tblBackupDetails VALUES(@BackupName,@FILEPATH,@SoftwareDate,@Type,GETDATE())              
  43.         END TRY  
  44.         BEGIN CATCH  
  45.             SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_SEVERITY() AS ErrorSeverity,ERROR_STATE() AS ErrorState,  
  46.                    ERROR_PROCEDURE() AS ErrorProcedure,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage;  
  47.         END CATCH  
  48.     END  
  49.   
  50.     IF @ACTIONTYPE = 'REMOVE_LOCATION'  
  51.     BEGIN  
  52.         SELECT Location FROM tblBackupDetails WHERE IID NOT IN (  
  53.             SELECT TOP (SELECT NoOfFiles FROM tblBackupInfo) IID FROM tblBackupDetails ORDER BY IID DESC)  
  54.     END  
  55. END  

Creating Window Application

 
After successfully creating tables and stored procedure, now, let us move to the Windows application.
 
Open Visual Studio and create a Windows application named as “DBBACKUP”. Delete the default form “Form1” and add a new form named as “FrmDbBackup”. Design the form like the image given below.
 
 
Code For FrmBackup.Designer.cs
  1. namespace DBBACKUP  
  2. {  
  3.     partial class FrmDbBackup  
  4.     {  
  5.         /// <summary>  
  6.         /// Required designer variable.  
  7.         /// </summary>  
  8.         private System.ComponentModel.IContainer components = null;  
  9.   
  10.         /// <summary>  
  11.         /// Clean up any resources being used.  
  12.         /// </summary>  
  13.         /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>  
  14.         protected override void Dispose(bool disposing)  
  15.         {  
  16.             if (disposing && (components != null))  
  17.             {  
  18.                 components.Dispose();  
  19.             }  
  20.             base.Dispose(disposing);  
  21.         }  
  22.  
  23.         #region Windows Form Designer generated code  
  24.   
  25.         /// <summary>  
  26.         /// Required method for Designer support - do not modify  
  27.         /// the contents of this method with the code editor.  
  28.         /// </summary>  
  29.         private void InitializeComponent()  
  30.         {  
  31.             this.components = new System.ComponentModel.Container();  
  32.             this.Label21 = new System.Windows.Forms.Label();  
  33.             this.btnClose = new System.Windows.Forms.Button();  
  34.             this.Panel1 = new System.Windows.Forms.Panel();  
  35.             this.Label9 = new System.Windows.Forms.Label();  
  36.             this.ProgressBarEx5 = new System.Windows.Forms.ProgressBar();  
  37.             this.label6 = new System.Windows.Forms.Label();  
  38.             this.txtDbName = new System.Windows.Forms.TextBox();  
  39.             this.label5 = new System.Windows.Forms.Label();  
  40.             this.label4 = new System.Windows.Forms.Label();  
  41.             this.label3 = new System.Windows.Forms.Label();  
  42.             this.DateTimePicker1 = new System.Windows.Forms.DateTimePicker();  
  43.             this.Label8 = new System.Windows.Forms.Label();  
  44.             this.LinkLabel1 = new System.Windows.Forms.LinkLabel();  
  45.             this.Label7 = new System.Windows.Forms.Label();  
  46.             this.btnSave = new System.Windows.Forms.Button();  
  47.             this.btnBackup = new System.Windows.Forms.Button();  
  48.             this.label13 = new System.Windows.Forms.Label();  
  49.             this.label22 = new System.Windows.Forms.Label();  
  50.             this.label20 = new System.Windows.Forms.Label();  
  51.             this.Timer1 = new System.Windows.Forms.Timer(this.components);  
  52.             this.FolderBrowserDialog1 = new System.Windows.Forms.FolderBrowserDialog();  
  53.             this.linkLabel2 = new System.Windows.Forms.Label();  
  54.             this.linkLabel3 = new System.Windows.Forms.Label();  
  55.             this.label1 = new System.Windows.Forms.Label();  
  56.             this.txtSpan = new System.Windows.Forms.TextBox();  
  57.             this.txtNoOfFiles = new System.Windows.Forms.TextBox();  
  58.             this.label2 = new System.Windows.Forms.Label();  
  59.             this.label10 = new System.Windows.Forms.Label();  
  60.             this.label11 = new System.Windows.Forms.Label();  
  61.             this.label12 = new System.Windows.Forms.Label();  
  62.             this.lblLastBackupInfo = new System.Windows.Forms.Label();  
  63.             this.Panel1.SuspendLayout();  
  64.             this.SuspendLayout();  
  65.             //   
  66.             // Label21  
  67.             //   
  68.             this.Label21.BackColor = System.Drawing.Color.SteelBlue;  
  69.             this.Label21.Dock = System.Windows.Forms.DockStyle.Top;  
  70.             this.Label21.Font = new System.Drawing.Font("Times New Roman", 12F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));  
  71.             this.Label21.ForeColor = System.Drawing.Color.White;  
  72.             this.Label21.Location = new System.Drawing.Point(0, 0);  
  73.             this.Label21.Name = "Label21";  
  74.             this.Label21.Size = new System.Drawing.Size(542, 25);  
  75.             this.Label21.TabIndex = 3;  
  76.             this.Label21.Text = "BACKUP  SETTINGS";  
  77.             this.Label21.TextAlign = System.Drawing.ContentAlignment.MiddleCenter;  
  78.             //   
  79.             // btnClose  
  80.             //   
  81.             this.btnClose.BackColor = System.Drawing.Color.SteelBlue;  
  82.             this.btnClose.FlatAppearance.BorderSize = 0;  
  83.             this.btnClose.FlatAppearance.MouseDownBackColor = System.Drawing.Color.MistyRose;  
  84.             this.btnClose.FlatAppearance.MouseOverBackColor = System.Drawing.Color.MistyRose;  
  85.             this.btnClose.FlatStyle = System.Windows.Forms.FlatStyle.Flat;  
  86.             this.btnClose.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.75F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));  
  87.             this.btnClose.ForeColor = System.Drawing.Color.White;  
  88.             this.btnClose.Location = new System.Drawing.Point(513, 0);  
  89.             this.btnClose.Name = "btnClose";  
  90.             this.btnClose.Size = new System.Drawing.Size(28, 24);  
  91.             this.btnClose.TabIndex = 1284;  
  92.             this.btnClose.TabStop = false;  
  93.             this.btnClose.Text = "X                           ";  
  94.             this.btnClose.UseVisualStyleBackColor = false;  
  95.             this.btnClose.Click += new System.EventHandler(this.btnClose_Click);  
  96.             //   
  97.             // Panel1  
  98.             //   
  99.             this.Panel1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;  
  100.             this.Panel1.Controls.Add(this.Label9);  
  101.             this.Panel1.Controls.Add(this.ProgressBarEx5);  
  102.             this.Panel1.Location = new System.Drawing.Point(58, 138);  
  103.             this.Panel1.Name = "Panel1";  
  104.             this.Panel1.Size = new System.Drawing.Size(446, 117);  
  105.             this.Panel1.TabIndex = 1354;  
  106.             this.Panel1.Visible = false;  
  107.             //   
  108.             // Label9  
  109.             //   
  110.             this.Label9.AutoSize = true;  
  111.             this.Label9.Font = new System.Drawing.Font("Segoe UI", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));  
  112.             this.Label9.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(64)))), ((int)(((byte)(64)))), ((int)(((byte)(64)))));  
  113.             this.Label9.Location = new System.Drawing.Point(9, 23);  
  114.             this.Label9.Name = "Label9";  
  115.             this.Label9.Size = new System.Drawing.Size(240, 17);  
  116.             this.Label9.TabIndex = 33;  
  117.             this.Label9.Text = "Database Backup Initialize, Please Wait...";  
  118.             //   
  119.             // ProgressBarEx5  
  120.             //   
  121.             this.ProgressBarEx5.Location = new System.Drawing.Point(12, 50);  
  122.             this.ProgressBarEx5.Name = "ProgressBarEx5";  
  123.             this.ProgressBarEx5.Size = new System.Drawing.Size(421, 18);  
  124.             this.ProgressBarEx5.TabIndex = 33;  
  125.             this.ProgressBarEx5.Visible = false;  
  126.             //   
  127.             // label6  
  128.             //   
  129.             this.label6.AutoSize = true;  
  130.             this.label6.Font = new System.Drawing.Font("Segoe UI", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));  
  131.             this.label6.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(64)))), ((int)(((byte)(64)))), ((int)(((byte)(64)))));  
  132.             this.label6.Location = new System.Drawing.Point(24, 332);  
  133.             this.label6.Name = "label6";  
  134.             this.label6.Size = new System.Drawing.Size(102, 17);  
  135.             this.label6.TabIndex = 1360;  
  136.             this.label6.Text = "Database Name";  
  137.             //   
  138.             // txtDbName  
  139.             //   
  140.             this.txtDbName.Location = new System.Drawing.Point(175, 114);  
  141.             this.txtDbName.Name = "txtDbName";  
  142.             this.txtDbName.Size = new System.Drawing.Size(289, 25);  
  143.             this.txtDbName.TabIndex = 1359;  
  144.             //   
  145.             // label5  
  146.             //   
  147.             this.label5.AutoSize = true;  
  148.             this.label5.Font = new System.Drawing.Font("Segoe UI", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));  
  149.             this.label5.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(64)))), ((int)(((byte)(64)))), ((int)(((byte)(64)))));  
  150.             this.label5.Location = new System.Drawing.Point(24, 117);  
  151.             this.label5.Name = "label5";  
  152.             this.label5.Size = new System.Drawing.Size(113, 17);  
  153.             this.label5.TabIndex = 1358;  
  154.             this.label5.Text = "Database Name : ";  
  155.             //   
  156.             // label4  
  157.             //   
  158.             this.label4.AutoSize = true;  
  159.             this.label4.Font = new System.Drawing.Font("Segoe UI", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));  
  160.             this.label4.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(64)))), ((int)(((byte)(64)))), ((int)(((byte)(64)))));  
  161.             this.label4.Location = new System.Drawing.Point(24, 307);  
  162.             this.label4.Name = "label4";  
  163.             this.label4.Size = new System.Drawing.Size(78, 17);  
  164.             this.label4.TabIndex = 1356;  
  165.             this.label4.Text = "Backup Path";  
  166.             //   
  167.             // label3  
  168.             //   
  169.             this.label3.AutoSize = true;  
  170.             this.label3.Enabled = false;  
  171.             this.label3.Font = new System.Drawing.Font("Segoe UI Semibold", 9.75F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));  
  172.             this.label3.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(64)))), ((int)(((byte)(64)))), ((int)(((byte)(64)))));  
  173.             this.label3.Location = new System.Drawing.Point(394, 30);  
  174.             this.label3.Name = "label3";  
  175.             this.label3.Size = new System.Drawing.Size(47, 17);  
  176.             this.label3.TabIndex = 1355;  
  177.             this.label3.Text = "Date : ";  
  178.             //   
  179.             // DateTimePicker1  
  180.             //   
  181.             this.DateTimePicker1.Enabled = false;  
  182.             this.DateTimePicker1.Font = new System.Drawing.Font("Segoe UI Semibold", 9.75F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));  
  183.             this.DateTimePicker1.Format = System.Windows.Forms.DateTimePickerFormat.Short;  
  184.             this.DateTimePicker1.Location = new System.Drawing.Point(438, 26);  
  185.             this.DateTimePicker1.Name = "DateTimePicker1";  
  186.             this.DateTimePicker1.Size = new System.Drawing.Size(100, 25);  
  187.             this.DateTimePicker1.TabIndex = 1348;  
  188.             //   
  189.             // Label8  
  190.             //   
  191.             this.Label8.AutoSize = true;  
  192.             this.Label8.Font = new System.Drawing.Font("Segoe UI", 14.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));  
  193.             this.Label8.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(64)))), ((int)(((byte)(64)))), ((int)(((byte)(64)))));  
  194.             this.Label8.Location = new System.Drawing.Point(8, 273);  
  195.             this.Label8.Name = "Label8";  
  196.             this.Label8.Size = new System.Drawing.Size(156, 25);  
  197.             this.Label8.TabIndex = 1353;  
  198.             this.Label8.Text = "Manually Backup";  
  199.             //   
  200.             // LinkLabel1  
  201.             //   
  202.             this.LinkLabel1.AutoSize = true;  
  203.             this.LinkLabel1.Location = new System.Drawing.Point(173, 63);  
  204.             this.LinkLabel1.Name = "LinkLabel1";  
  205.             this.LinkLabel1.Size = new System.Drawing.Size(0, 17);  
  206.             this.LinkLabel1.TabIndex = 1352;  
  207.             this.LinkLabel1.LinkClicked += new System.Windows.Forms.LinkLabelLinkClickedEventHandler(this.LinkLabel1_LinkClicked);  
  208.             //   
  209.             // Label7  
  210.             //   
  211.             this.Label7.AutoSize = true;  
  212.             this.Label7.Font = new System.Drawing.Font("Segoe UI", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));  
  213.             this.Label7.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(64)))), ((int)(((byte)(64)))), ((int)(((byte)(64)))));  
  214.             this.Label7.Location = new System.Drawing.Point(24, 63);  
  215.             this.Label7.Name = "Label7";  
  216.             this.Label7.Size = new System.Drawing.Size(132, 17);  
  217.             this.Label7.TabIndex = 1351;  
  218.             this.Label7.Text = "Save Directory Path : ";  
  219.             //   
  220.             // btnSave  
  221.             //   
  222.             this.btnSave.BackColor = System.Drawing.Color.White;  
  223.             this.btnSave.FlatStyle = System.Windows.Forms.FlatStyle.Flat;  
  224.             this.btnSave.Font = new System.Drawing.Font("Segoe UI Semibold", 9F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));  
  225.             this.btnSave.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(64)))), ((int)(((byte)(64)))), ((int)(((byte)(64)))));  
  226.             this.btnSave.Location = new System.Drawing.Point(223, 153);  
  227.             this.btnSave.Name = "btnSave";  
  228.             this.btnSave.Size = new System.Drawing.Size(89, 25);  
  229.             this.btnSave.TabIndex = 1349;  
  230.             this.btnSave.Text = "Save";  
  231.             this.btnSave.UseVisualStyleBackColor = false;  
  232.             this.btnSave.Click += new System.EventHandler(this.btnSave_Click);  
  233.             //   
  234.             // btnBackup  
  235.             //   
  236.             this.btnBackup.BackColor = System.Drawing.Color.White;  
  237.             this.btnBackup.FlatStyle = System.Windows.Forms.FlatStyle.Flat;  
  238.             this.btnBackup.Font = new System.Drawing.Font("Segoe UI Semibold", 9F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));  
  239.             this.btnBackup.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(64)))), ((int)(((byte)(64)))), ((int)(((byte)(64)))));  
  240.             this.btnBackup.Image = global::DBBACKUP.Properties.Resources.database;  
  241.             this.btnBackup.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;  
  242.             this.btnBackup.Location = new System.Drawing.Point(367, 352);  
  243.             this.btnBackup.Name = "btnBackup";  
  244.             this.btnBackup.Size = new System.Drawing.Size(137, 33);  
  245.             this.btnBackup.TabIndex = 1350;  
  246.             this.btnBackup.Text = "Backup Database";  
  247.             this.btnBackup.TextAlign = System.Drawing.ContentAlignment.MiddleRight;  
  248.             this.btnBackup.UseVisualStyleBackColor = false;  
  249.             this.btnBackup.Click += new System.EventHandler(this.btnBackup_Click);  
  250.             //   
  251.             // label13  
  252.             //   
  253.             this.label13.BackColor = System.Drawing.Color.SteelBlue;  
  254.             this.label13.Dock = System.Windows.Forms.DockStyle.Right;  
  255.             this.label13.Font = new System.Drawing.Font("Times New Roman", 12F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));  
  256.             this.label13.Location = new System.Drawing.Point(540, 25);  
  257.             this.label13.Name = "label13";  
  258.             this.label13.Size = new System.Drawing.Size(2, 365);  
  259.             this.label13.TabIndex = 1362;  
  260.             //   
  261.             // label22  
  262.             //   
  263.             this.label22.BackColor = System.Drawing.Color.SteelBlue;  
  264.             this.label22.Dock = System.Windows.Forms.DockStyle.Bottom;  
  265.             this.label22.Font = new System.Drawing.Font("Times New Roman", 12F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));  
  266.             this.label22.Location = new System.Drawing.Point(0, 388);  
  267.             this.label22.Name = "label22";  
  268.             this.label22.Size = new System.Drawing.Size(540, 2);  
  269.             this.label22.TabIndex = 1363;  
  270.             //   
  271.             // label20  
  272.             //   
  273.             this.label20.BackColor = System.Drawing.Color.SteelBlue;  
  274.             this.label20.Dock = System.Windows.Forms.DockStyle.Left;  
  275.             this.label20.Font = new System.Drawing.Font("Times New Roman", 12F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));  
  276.             this.label20.Location = new System.Drawing.Point(0, 25);  
  277.             this.label20.Name = "label20";  
  278.             this.label20.Size = new System.Drawing.Size(2, 363);  
  279.             this.label20.TabIndex = 1364;  
  280.             //   
  281.             // Timer1  
  282.             //   
  283.             this.Timer1.Tick += new System.EventHandler(this.Timer1_Tick);  
  284.             //   
  285.             // FolderBrowserDialog1  
  286.             //   
  287.             this.FolderBrowserDialog1.SelectedPath = "C:\\ProgramData\\PRM System\\Backup\\";  
  288.             //   
  289.             // linkLabel2  
  290.             //   
  291.             this.linkLabel2.AutoSize = true;  
  292.             this.linkLabel2.Location = new System.Drawing.Point(134, 307);  
  293.             this.linkLabel2.Name = "linkLabel2";  
  294.             this.linkLabel2.Size = new System.Drawing.Size(0, 17);  
  295.             this.linkLabel2.TabIndex = 1365;  
  296.             //   
  297.             // linkLabel3  
  298.             //   
  299.             this.linkLabel3.AutoSize = true;  
  300.             this.linkLabel3.Location = new System.Drawing.Point(134, 332);  
  301.             this.linkLabel3.Name = "linkLabel3";  
  302.             this.linkLabel3.Size = new System.Drawing.Size(0, 17);  
  303.             this.linkLabel3.TabIndex = 1366;  
  304.             //   
  305.             // label1  
  306.             //   
  307.             this.label1.AutoSize = true;  
  308.             this.label1.Font = new System.Drawing.Font("Segoe UI", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));  
  309.             this.label1.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(64)))), ((int)(((byte)(64)))), ((int)(((byte)(64)))));  
  310.             this.label1.Location = new System.Drawing.Point(24, 88);  
  311.             this.label1.Name = "label1";  
  312.             this.label1.Size = new System.Drawing.Size(156, 17);  
  313.             this.label1.TabIndex = 1367;  
  314.             this.label1.Text = "Auto Backup Time Span : ";  
  315.             //   
  316.             // txtSpan  
  317.             //   
  318.             this.txtSpan.Location = new System.Drawing.Point(175, 85);  
  319.             this.txtSpan.MaxLength = 2;  
  320.             this.txtSpan.Name = "txtSpan";  
  321.             this.txtSpan.Size = new System.Drawing.Size(57, 25);  
  322.             this.txtSpan.TabIndex = 1368;  
  323.             this.txtSpan.KeyPress += new System.Windows.Forms.KeyPressEventHandler(this.txtSpan_KeyPress);  
  324.             //   
  325.             // txtNoOfFiles  
  326.             //   
  327.             this.txtNoOfFiles.Location = new System.Drawing.Point(373, 85);  
  328.             this.txtNoOfFiles.MaxLength = 2;  
  329.             this.txtNoOfFiles.Name = "txtNoOfFiles";  
  330.             this.txtNoOfFiles.Size = new System.Drawing.Size(57, 25);  
  331.             this.txtNoOfFiles.TabIndex = 1370;  
  332.             this.txtNoOfFiles.KeyPress += new System.Windows.Forms.KeyPressEventHandler(this.txtSpan_KeyPress);  
  333.             //   
  334.             // label2  
  335.             //   
  336.             this.label2.AutoSize = true;  
  337.             this.label2.Font = new System.Drawing.Font("Segoe UI", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));  
  338.             this.label2.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(64)))), ((int)(((byte)(64)))), ((int)(((byte)(64)))));  
  339.             this.label2.Location = new System.Drawing.Point(240, 89);  
  340.             this.label2.Name = "label2";  
  341.             this.label2.Size = new System.Drawing.Size(136, 17);  
  342.             this.label2.TabIndex = 1369;  
  343.             this.label2.Text = "No Of Files To Keep : ";  
  344.             //   
  345.             // label10  
  346.             //   
  347.             this.label10.AutoSize = true;  
  348.             this.label10.Font = new System.Drawing.Font("Segoe UI", 14.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));  
  349.             this.label10.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(64)))), ((int)(((byte)(64)))), ((int)(((byte)(64)))));  
  350.             this.label10.Location = new System.Drawing.Point(8, 28);  
  351.             this.label10.Name = "label10";  
  352.             this.label10.Size = new System.Drawing.Size(126, 25);  
  353.             this.label10.TabIndex = 1371;  
  354.             this.label10.Text = "Backup Setup";  
  355.             //   
  356.             // label11  
  357.             //   
  358.             this.label11.BackColor = System.Drawing.Color.LightGray;  
  359.             this.label11.Font = new System.Drawing.Font("Times New Roman", 12F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));  
  360.             this.label11.Location = new System.Drawing.Point(0, 190);  
  361.             this.label11.Name = "label11";  
  362.             this.label11.Size = new System.Drawing.Size(540, 2);  
  363.             this.label11.TabIndex = 1372;  
  364.             //   
  365.             // label12  
  366.             //   
  367.             this.label12.AutoSize = true;  
  368.             this.label12.Font = new System.Drawing.Font("Segoe UI", 14.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));  
  369.             this.label12.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(64)))), ((int)(((byte)(64)))), ((int)(((byte)(64)))));  
  370.             this.label12.Location = new System.Drawing.Point(8, 196);  
  371.             this.label12.Name = "label12";  
  372.             this.label12.Size = new System.Drawing.Size(149, 25);  
  373.             this.label12.TabIndex = 1373;  
  374.             this.label12.Text = "Last Backup Info";  
  375.             //   
  376.             // lblLastBackupInfo  
  377.             //   
  378.             this.lblLastBackupInfo.Font = new System.Drawing.Font("Segoe UI", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));  
  379.             this.lblLastBackupInfo.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(64)))), ((int)(((byte)(64)))), ((int)(((byte)(64)))));  
  380.             this.lblLastBackupInfo.Location = new System.Drawing.Point(24, 223);  
  381.             this.lblLastBackupInfo.Name = "lblLastBackupInfo";  
  382.             this.lblLastBackupInfo.Size = new System.Drawing.Size(506, 45);  
  383.             this.lblLastBackupInfo.TabIndex = 1374;  
  384.             this.lblLastBackupInfo.Text = "Last backup was taken {0} at {1} in location {2}.";  
  385.             //   
  386.             // FrmDbBackup  
  387.             //   
  388.             this.AutoScaleDimensions = new System.Drawing.SizeF(7F, 17F);  
  389.             this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;  
  390.             this.BackColor = System.Drawing.Color.White;  
  391.             this.ClientSize = new System.Drawing.Size(542, 390);  
  392.             this.Controls.Add(this.Panel1);  
  393.             this.Controls.Add(this.lblLastBackupInfo);  
  394.             this.Controls.Add(this.label12);  
  395.             this.Controls.Add(this.label11);  
  396.             this.Controls.Add(this.label10);  
  397.             this.Controls.Add(this.txtNoOfFiles);  
  398.             this.Controls.Add(this.label2);  
  399.             this.Controls.Add(this.linkLabel3);  
  400.             this.Controls.Add(this.linkLabel2);  
  401.             this.Controls.Add(this.label20);  
  402.             this.Controls.Add(this.label22);  
  403.             this.Controls.Add(this.label13);  
  404.             this.Controls.Add(this.label6);  
  405.             this.Controls.Add(this.txtDbName);  
  406.             this.Controls.Add(this.label5);  
  407.             this.Controls.Add(this.label4);  
  408.             this.Controls.Add(this.btnBackup);  
  409.             this.Controls.Add(this.DateTimePicker1);  
  410.             this.Controls.Add(this.Label8);  
  411.             this.Controls.Add(this.LinkLabel1);  
  412.             this.Controls.Add(this.Label7);  
  413.             this.Controls.Add(this.btnSave);  
  414.             this.Controls.Add(this.btnClose);  
  415.             this.Controls.Add(this.Label21);  
  416.             this.Controls.Add(this.txtSpan);  
  417.             this.Controls.Add(this.label1);  
  418.             this.Controls.Add(this.label3);  
  419.             this.Font = new System.Drawing.Font("Segoe UI", 9.75F);  
  420.             this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.None;  
  421.             this.Margin = new System.Windows.Forms.Padding(3, 4, 3, 4);  
  422.             this.Name = "FrmDbBackup";  
  423.             this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;  
  424.             this.Text = "frmDbBackup";  
  425.             this.Load += new System.EventHandler(this.FrmDbBackup_Load);  
  426.             this.Panel1.ResumeLayout(false);  
  427.             this.Panel1.PerformLayout();  
  428.             this.ResumeLayout(false);  
  429.             this.PerformLayout();  
  430.   
  431.         }  
  432.  
  433.         #endregion  
  434.   
  435.         internal System.Windows.Forms.Label Label21;  
  436.         internal System.Windows.Forms.Button btnClose;  
  437.         internal System.Windows.Forms.Panel Panel1;  
  438.         internal System.Windows.Forms.Label Label9;  
  439.         internal System.Windows.Forms.ProgressBar ProgressBarEx5;  
  440.         internal System.Windows.Forms.Label label6;  
  441.         private System.Windows.Forms.TextBox txtDbName;  
  442.         internal System.Windows.Forms.Label label5;  
  443.         internal System.Windows.Forms.Label label4;  
  444.         internal System.Windows.Forms.Label label3;  
  445.         internal System.Windows.Forms.Button btnBackup;  
  446.         internal System.Windows.Forms.Label Label8;  
  447.         internal System.Windows.Forms.LinkLabel LinkLabel1;  
  448.         internal System.Windows.Forms.Label Label7;  
  449.         internal System.Windows.Forms.Button btnSave;  
  450.         internal System.Windows.Forms.Label label13;  
  451.         internal System.Windows.Forms.Label label22;  
  452.         internal System.Windows.Forms.Label label20;  
  453.         internal System.Windows.Forms.Timer Timer1;  
  454.         internal System.Windows.Forms.FolderBrowserDialog FolderBrowserDialog1;  
  455.         private System.Windows.Forms.Label linkLabel2;  
  456.         private System.Windows.Forms.Label linkLabel3;  
  457.         internal System.Windows.Forms.Label label1;  
  458.         private System.Windows.Forms.TextBox txtSpan;  
  459.         internal System.Windows.Forms.DateTimePicker DateTimePicker1;  
  460.         private System.Windows.Forms.TextBox txtNoOfFiles;  
  461.         internal System.Windows.Forms.Label label2;  
  462.         internal System.Windows.Forms.Label label10;  
  463.         internal System.Windows.Forms.Label label11;  
  464.         internal System.Windows.Forms.Label label12;  
  465.         internal System.Windows.Forms.Label lblLastBackupInfo;  
  466.     }  
  467. }  
In the above form there are three section one is Backup Setup, Last Backup Info and Manually Backup.
 
In “Backup Setup” section we have to provide the database setup info like Database Name, Save Directoty Path, Auto Backup Time Span, No of Files To Keep.
 
Save Directoty Path: To where we have save the database backup file.
Auto Backup Time Span: Interval of time (In Days) to take backup autometically. This fields for take backup autometically. Here we use manually.
 
No of Files To Keep: How many backup files you want to keep in backup folder.
 
Database Name : Name of database.

In “Last Backup Info” shows when the last backup was taken.

“Manually Backup” section is used to take database backup manually by click on Database Backup button.
 

DataBase Backup Operation

 
After designing the form, now, we will do the database setup manually take database coding. For that, we need to import the following namespaces.
  1. using System;  
  2. using System.IO;  
  3. using System.Data;  
  4. using System.Drawing;  
  5. using System.Windows.Forms;  
  6. using System.Data.SqlClient;  
Before going to the database backup operation, we have to set the connection to the database and declare Connection variables.
 
For that, we will use the conString variable like below.
  1. SqlCommand cmd;  
  2. SqlConnection sqlCon;  
  3. string conString = "Data Source=.; Initial Catalog=DemoTest; User Id=sa; Password=password;";  
In the above “conString”, Data Source is your server name, Initial Catalog is your database name, and User Id & Password are your login credentials for logging in to the SQL Server. Now, initialize the connection inside the page constructor.
  1. public FrmDbBackup()  
  2. {  
  3.     InitializeComponent();  
  4.     sqlCon = new SqlConnection(conString);  
  5.     sqlCon.Open();  
  6. }  
Code for FrmDbBackup.cs
  1. using System;  
  2. using System.IO;  
  3. using System.Data;  
  4. using System.Drawing;  
  5. using System.Windows.Forms;  
  6. using System.Data.SqlClient;  
  7.   
  8. namespace DBBACKUP  
  9. {  
  10.     public partial class FrmDbBackup : Form  
  11.     {  
  12.         SqlCommand cmd;  
  13.         SqlConnection sqlCon;  
  14.         string conString = "Data Source=.; Initial Catalog=DemoTest; User Id=sa; Password=password;";  
  15.   
  16.         public FrmDbBackup()  
  17.         {  
  18.             InitializeComponent();  
  19.             sqlCon = new SqlConnection(conString);  
  20.             sqlCon.Open();  
  21.         }  
  22.   
  23.         private void FrmDbBackup_Load(object sender, EventArgs e)  
  24.         {  
  25.             LoadBackinfo();  
  26.             if (LinkLabel1.Text == string.Empty)  
  27.             {  
  28.                 LinkLabel1.Text = "Click To Set Directory Path";  
  29.             }  
  30.         }  
  31.   
  32.         private void LoadBackinfo()  
  33.         {  
  34.             if (sqlCon.State == ConnectionState.Closed)  
  35.             {  
  36.                 sqlCon.Open();  
  37.             }  
  38.             DataSet dsData = new DataSet();  
  39.             cmd = new SqlCommand("DATABASE_BACKUP", sqlCon);  
  40.             cmd.CommandType = CommandType.StoredProcedure;  
  41.             cmd.Parameters.AddWithValue("@ACTIONTYPE", "BACKUP_INFO");  
  42.             SqlDataAdapter sda = new SqlDataAdapter(cmd);  
  43.             sda.Fill(dsData);  
  44.             if (dsData.Tables.Count > 0)  
  45.             {  
  46.                 if (dsData.Tables[0].Rows.Count > 0)  
  47.                 {  
  48.                     LinkLabel1.Text = dsData.Tables[0].Rows[0]["LOCATION"].ToString();  
  49.                     txtNoOfFiles.Text = dsData.Tables[0].Rows[0]["NoOfFiles"].ToString();  
  50.                     txtSpan.Text = dsData.Tables[0].Rows[0]["DayInterval"].ToString();  
  51.                     txtDbName.Text = dsData.Tables[0].Rows[0]["DATABASENAME"].ToString();  
  52.                     linkLabel2.Text = dsData.Tables[0].Rows[0]["LOCATION"].ToString();  
  53.                     linkLabel3.Text = dsData.Tables[0].Rows[0]["DATABASENAME"].ToString() + "-" + DateTime.Now.ToString("ddMMyyyyHHmmssfff") + ".bak";  
  54.                 }  
  55.                 if (dsData.Tables[1].Rows.Count > 0)  
  56.                 {  
  57.                     lblLastBackupInfo.Text = string.Format("Last backup was taken {0} at {1} in location {2}.", dsData.Tables[1].Rows[0]["BackupType"].ToString(),  
  58.                         dsData.Tables[1].Rows[0]["BackupDate"].ToString(), dsData.Tables[1].Rows[0]["Location"].ToString());  
  59.                 }  
  60.                 else  
  61.                     lblLastBackupInfo.Text = "No Backups !!!";  
  62.             }  
  63.         }  
  64.   
  65.         private void btnSave_Click(object sender, EventArgs e)  
  66.         {  
  67.             if (LinkLabel1.Text == "Click To Set Directory Path")  
  68.             {  
  69.                 MessageBox.Show("Click To Set Directory Path", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);  
  70.             }  
  71.             else if (txtSpan.Text == string.Empty)  
  72.             {  
  73.                 MessageBox.Show("Enter how many last backup files required ", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);  
  74.             }  
  75.             else  
  76.             {  
  77.                 int numFlag;  
  78.                 if (sqlCon.State == ConnectionState.Closed)  
  79.                 {  
  80.                     sqlCon.Open();  
  81.                 }  
  82.                 cmd = new SqlCommand("DATABASE_BACKUP", sqlCon);  
  83.                 cmd.CommandType = CommandType.StoredProcedure;  
  84.                 cmd.Parameters.AddWithValue("@ACTIONTYPE", "INSERT_BACKUP_INFO");  
  85.                 cmd.Parameters.AddWithValue("@DatabaseName", txtDbName.Text); // Your Database Name  
  86.                 cmd.Parameters.AddWithValue("@Location", LinkLabel1.Text);  
  87.                 cmd.Parameters.AddWithValue("@DayInterval", txtSpan.Text);  
  88.                 cmd.Parameters.AddWithValue("@SoftwareDate", DateTimePicker1.Text);  
  89.                 cmd.Parameters.AddWithValue("@NoOfFiles", txtNoOfFiles.Text);  
  90.                 numFlag = cmd.ExecuteNonQuery();  
  91.   
  92.                 if (numFlag > 0)  
  93.                 {  
  94.                     MessageBox.Show("Data saved successfully.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);  
  95.                     LoadBackinfo();  
  96.                 }  
  97.                 else  
  98.                 {  
  99.                     MessageBox.Show("Data not saved. Plaese Try Again.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);  
  100.                 }  
  101.             }  
  102.         }  
  103.   
  104.         private void Timer1_Tick(object sender, EventArgs e)  
  105.         {  
  106.             ProgressBarEx5.Value += 1;  
  107.             if (ProgressBarEx5.Value == 100)  
  108.             {  
  109.                 ProgressBarEx5.Visible = false;  
  110.                 Timer1.Stop();  
  111.                 Panel1.Visible = false;  
  112.                 ProgressBarEx5.Text = "Finished";  
  113.             }  
  114.         }  
  115.   
  116.         private void LinkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)  
  117.         {  
  118.             FolderBrowserDialog1.ShowDialog();  
  119.             LinkLabel1.Text = FolderBrowserDialog1.SelectedPath;  
  120.         }  
  121.   
  122.         private void btnBackup_Click(object sender, EventArgs e)  
  123.         {  
  124.             if (linkLabel2.Text == string.Empty)  
  125.             {  
  126.                 MessageBox.Show("Please Set Backup Setting", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);  
  127.             }  
  128.             else if (linkLabel3.Text == string.Empty)  
  129.             {  
  130.                 MessageBox.Show("Please Set Backup Setting", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);  
  131.             }  
  132.             else  
  133.             {  
  134.                 string filaPath;  
  135.                 if (!linkLabel2.Text.EndsWith(@"\"))  
  136.                 {  
  137.                     filaPath = linkLabel2.Text + @"\" + linkLabel3.Text;  
  138.                 }  
  139.                 else  
  140.                 {  
  141.                     filaPath = linkLabel2.Text + linkLabel3.Text;  
  142.                 }  
  143.                 int numFlag;  
  144.                 if (sqlCon.State == ConnectionState.Closed)  
  145.                 {  
  146.                     sqlCon.Open();  
  147.                 }  
  148.                 cmd = new SqlCommand("DATABASE_BACKUP", sqlCon);  
  149.                 cmd.CommandType = CommandType.StoredProcedure;  
  150.                 cmd.Parameters.AddWithValue("@ACTIONTYPE", "DB_BACKUP");  
  151.                 cmd.Parameters.AddWithValue("@DATABASE", txtDbName.Text); // Your Database Name  
  152.                 cmd.Parameters.AddWithValue("@FILEPATH", filaPath);  
  153.                 cmd.Parameters.AddWithValue("@BackupName", linkLabel3.Text);  
  154.                 cmd.Parameters.AddWithValue("@SoftwareDate", DateTimePicker1.Text);  
  155.                 cmd.Parameters.AddWithValue("@Type", "Manually");  
  156.                 numFlag = cmd.ExecuteNonQuery();  
  157.                 DataTable dtLoc = new DataTable();  
  158.                 cmd = new SqlCommand("DATABASE_BACKUP", sqlCon);  
  159.                 cmd.CommandType = CommandType.StoredProcedure;  
  160.                 cmd.Parameters.AddWithValue("@ACTIONTYPE", "REMOVE_LOCATION");  
  161.                 SqlDataAdapter da = new SqlDataAdapter(cmd);  
  162.                 da.Fill(dtLoc);  
  163.                 for (int i = 0; i < dtLoc.Rows.Count; i++)  
  164.                 {  
  165.                     string delLoc = dtLoc.Rows[i][0].ToString();  
  166.                     string filepath = delLoc;  
  167.                     if (File.Exists(filepath))  
  168.                     {  
  169.                         File.Delete(filepath);  
  170.   
  171.                     }  
  172.                 }  
  173.                 if (numFlag > 0)  
  174.                 {  
  175.                     Panel1.Visible = true;  
  176.                     Panel1.Location = new Point(58, 138);  
  177.                     Panel1.Height = 117;  
  178.                     Panel1.Width = 446;  
  179.                     ProgressBarEx5.Visible = true;  
  180.                     ProgressBarEx5.Value = 0;  
  181.                     Timer1.Start();  
  182.                     LoadBackinfo();  
  183.                 }  
  184.                 else  
  185.                 {  
  186.                     MessageBox.Show("Plaese Try Again.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);  
  187.                 }  
  188.             }  
  189.         }  
  190.   
  191.         private void btnClose_Click(object sender, EventArgs e)  
  192.         {  
  193.             this.Close();  
  194.         }  
  195.   
  196.         private void txtSpan_KeyPress(object sender, KeyPressEventArgs e)  
  197.         {  
  198.             if ((e.KeyChar >= 48 && e.KeyChar <= 57) || e.KeyChar == 46 || e.KeyChar == 8)  
  199.             {  
  200.                 e.Handled = false;  
  201.             }  
  202.             else  
  203.             {  
  204.                 e.Handled = true;  
  205.             }  
  206.         }  
  207.     }  
  208. }  
Now, build and run the project.

First we have set Backup Setup where we have to fill
Save Directoty Path: To where we have save the database backup file. Click on  “Click To Set Directory Path” and select a location where you want to save backup files.
Auto Backup Time Span: Interval of time (In Days) to take backup autometically. This fields for take backup autometically. Here we are for manual so put 0(Zero).
No of Files To Keep: Give how many backup files you want to keep in backup folder.
Database Name : Name of your database.
Then click on “Save” button. It will save the setup details to the database and look like this.
 
 After save Backup Setup details you will see Backup Path and Database Name value is coming in Manually Backup section.
Now click on Backup Database button it will create a database backup file and stored in our given folder
and also show the last backup info in Last Backup Info section look like below image.
 
 


Similar Articles