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

CREATE TABLE [dbo].[tblBackupInfo](
[IID] [int] IDENTITY(1,1) NOT NULL,
[DayInterval] [int] NULL,
[NoOfFiles] [int] NULL,
[DatabaseName] [nvarchar](500) NULL,
[Location] [nvarchar](max) NULL,
[SoftwareDate] [datetime] NULL,
[LastEditDate] [datetime] NULL,
[CreationDate] [datetime] NOT NULL
)
CREATE TABLE [dbo].[tblBackupDetails](
[IID] [int] IDENTITY(1,1) NOT NULL,
[BackupName] [varchar](50) NULL,
[Location] [varchar](500) NULL,
[BackupDate] [datetime] NULL,
[BackupType] [varchar](50) NULL,
[CreationDate] [datetime] NOT NULL
)

Stored Procedure

CREATE PROCEDURE [dbo].[DATABASE_BACKUP]
(
@DatabaseName VARCHAR(1000) = NULL,
@Location VARCHAR(1000) = NULL,
@Type VARCHAR(25) = NULL,
@BackupName VARCHAR(500) = NULL,
@FILEPATH VARCHAR(2000) = NULL,
@DATABASE VARCHAR(1000) = NULL,
@DayInterval INT = NULL,
@NoOfFiles INT = NULL,
@SoftwareDate DATE = NULL,
@ACTIONTYPE VARCHAR(50)
)
AS
BEGIN
IF @ACTIONTYPE = 'BACKUP_INFO'
BEGIN
SELECT DATABASENAME,ISNULL(NoOfFiles,0) AS NoOfFiles,LOCATION,DayInterval FROM tblBackupInfo
SELECT TOP 1 BackupType,BackupDate,Location FROM tblBackupDetails ORDER BY IID DESC
END
IF @ACTIONTYPE = 'INSERT_BACKUP_INFO'
BEGIN
IF NOT EXISTS (SELECT * FROM tblBackupInfo)
BEGIN
INSERT INTO tblBackupInfo (DayInterval,NoOfFiles,DatabaseName,Location,SoftwareDate,CreationDate)
VALUES (@DayInterval,@NoOfFiles,@DatabaseName,@Location,@SoftwareDate,GETDATE())
END
ELSE
BEGIN
UPDATE tblBackupInfo SET DayInterval=@DayInterval,NoOfFiles=@NoOfFiles,DatabaseName=@DatabaseName,
Location=@Location,SoftwareDate=@SoftwareDate,LastEditDate=GETDATE()
END
END
IF @ACTIONTYPE = 'DB_BACKUP'
BEGIN
BEGIN TRY
BACKUP DATABASE @DATABASE
TO DISK = @FILEPATH;
INSERT INTO tblBackupDetails VALUES(@BackupName,@FILEPATH,@SoftwareDate,@Type,GETDATE())
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_SEVERITY() AS ErrorSeverity,ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END
IF @ACTIONTYPE = 'REMOVE_LOCATION'
BEGIN
SELECT Location FROM tblBackupDetails WHERE IID NOT IN (
SELECT TOP (SELECT NoOfFiles FROM tblBackupInfo) IID FROM tblBackupDetails ORDER BY IID DESC)
END
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

namespace DBBACKUP
{
partial class FrmDbBackup
{
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.IContainer components = null;
/// <summary>
/// Clean up any resources being used.
/// </summary>
/// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
protected override void Dispose(bool disposing)
{
if (disposing && (components != null))
{
components.Dispose();
}
base.Dispose(disposing);
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.components = new System.ComponentModel.Container();
this.Label21 = new System.Windows.Forms.Label();
this.btnClose = new System.Windows.Forms.Button();
this.Panel1 = new System.Windows.Forms.Panel();
this.Label9 = new System.Windows.Forms.Label();
this.ProgressBarEx5 = new System.Windows.Forms.ProgressBar();
this.label6 = new System.Windows.Forms.Label();
this.txtDbName = new System.Windows.Forms.TextBox();
this.label5 = new System.Windows.Forms.Label();
this.label4 = new System.Windows.Forms.Label();
this.label3 = new System.Windows.Forms.Label();
this.DateTimePicker1 = new System.Windows.Forms.DateTimePicker();
this.Label8 = new System.Windows.Forms.Label();
this.LinkLabel1 = new System.Windows.Forms.LinkLabel();
this.Label7 = new System.Windows.Forms.Label();
this.btnSave = new System.Windows.Forms.Button();
this.btnBackup = new System.Windows.Forms.Button();
this.label13 = new System.Windows.Forms.Label();
this.label22 = new System.Windows.Forms.Label();
this.label20 = new System.Windows.Forms.Label();
this.Timer1 = new System.Windows.Forms.Timer(this.components);
this.FolderBrowserDialog1 = new System.Windows.Forms.FolderBrowserDialog();
this.linkLabel2 = new System.Windows.Forms.Label();
this.linkLabel3 = new System.Windows.Forms.Label();
this.label1 = new System.Windows.Forms.Label();
this.txtSpan = new System.Windows.Forms.TextBox();
this.txtNoOfFiles = new System.Windows.Forms.TextBox();
this.label2 = new System.Windows.Forms.Label();
this.label10 = new System.Windows.Forms.Label();
this.label11 = new System.Windows.Forms.Label();
this.label12 = new System.Windows.Forms.Label();
this.lblLastBackupInfo = new System.Windows.Forms.Label();
this.Panel1.SuspendLayout();
this.SuspendLayout();
//
// Label21
//
this.Label21.BackColor = System.Drawing.Color.SteelBlue;
this.Label21.Dock = System.Windows.Forms.DockStyle.Top;
this.Label21.Font = new System.Drawing.Font("Times New Roman", 12F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.Label21.ForeColor = System.Drawing.Color.White;
this.Label21.Location = new System.Drawing.Point(0, 0);
this.Label21.Name = "Label21";
this.Label21.Size = new System.Drawing.Size(542, 25);
this.Label21.TabIndex = 3;
this.Label21.Text = "BACKUP SETTINGS";
this.Label21.TextAlign = System.Drawing.ContentAlignment.MiddleCenter;
//
// btnClose
//
this.btnClose.BackColor = System.Drawing.Color.SteelBlue;
this.btnClose.FlatAppearance.BorderSize = 0;
this.btnClose.FlatAppearance.MouseDownBackColor = System.Drawing.Color.MistyRose;
this.btnClose.FlatAppearance.MouseOverBackColor = System.Drawing.Color.MistyRose;
this.btnClose.FlatStyle = System.Windows.Forms.FlatStyle.Flat;
this.btnClose.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.75F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.btnClose.ForeColor = System.Drawing.Color.White;
this.btnClose.Location = new System.Drawing.Point(513, 0);
this.btnClose.Name = "btnClose";
this.btnClose.Size = new System.Drawing.Size(28, 24);
this.btnClose.TabIndex = 1284;
this.btnClose.TabStop = false;
this.btnClose.Text = "X ";
this.btnClose.UseVisualStyleBackColor = false;
this.btnClose.Click += new System.EventHandler(this.btnClose_Click);
//
// Panel1
//
this.Panel1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
this.Panel1.Controls.Add(this.Label9);
this.Panel1.Controls.Add(this.ProgressBarEx5);
this.Panel1.Location = new System.Drawing.Point(58, 138);
this.Panel1.Name = "Panel1";
this.Panel1.Size = new System.Drawing.Size(446, 117);
this.Panel1.TabIndex = 1354;
this.Panel1.Visible = false;
//
// Label9
//
this.Label9.AutoSize = true;
this.Label9.Font = new System.Drawing.Font("Segoe UI", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.Label9.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(64)))), ((int)(((byte)(64)))), ((int)(((byte)(64)))));
this.Label9.Location = new System.Drawing.Point(9, 23);
this.Label9.Name = "Label9";
this.Label9.Size = new System.Drawing.Size(240, 17);
this.Label9.TabIndex = 33;
this.Label9.Text = "Database Backup Initialize, Please Wait...";
//
// ProgressBarEx5
//
this.ProgressBarEx5.Location = new System.Drawing.Point(12, 50);
this.ProgressBarEx5.Name = "ProgressBarEx5";
this.ProgressBarEx5.Size = new System.Drawing.Size(421, 18);
this.ProgressBarEx5.TabIndex = 33;
this.ProgressBarEx5.Visible = false;
//
// label6
//
this.label6.AutoSize = true;
this.label6.Font = new System.Drawing.Font("Segoe UI", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.label6.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(64)))), ((int)(((byte)(64)))), ((int)(((byte)(64)))));
this.label6.Location = new System.Drawing.Point(24, 332);
this.label6.Name = "label6";
this.label6.Size = new System.Drawing.Size(102, 17);
this.label6.TabIndex = 1360;
this.label6.Text = "Database Name";
//
// txtDbName
//
this.txtDbName.Location = new System.Drawing.Point(175, 114);
this.txtDbName.Name = "txtDbName";
this.txtDbName.Size = new System.Drawing.Size(289, 25);
this.txtDbName.TabIndex = 1359;
//
// label5
//
this.label5.AutoSize = true;
this.label5.Font = new System.Drawing.Font("Segoe UI", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.label5.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(64)))), ((int)(((byte)(64)))), ((int)(((byte)(64)))));
this.label5.Location = new System.Drawing.Point(24, 117);
this.label5.Name = "label5";
this.label5.Size = new System.Drawing.Size(113, 17);
this.label5.TabIndex = 1358;
this.label5.Text = "Database Name : ";
//
// label4
//
this.label4.AutoSize = true;
this.label4.Font = new System.Drawing.Font("Segoe UI", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.label4.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(64)))), ((int)(((byte)(64)))), ((int)(((byte)(64)))));
this.label4.Location = new System.Drawing.Point(24, 307);
this.label4.Name = "label4";
this.label4.Size = new System.Drawing.Size(78, 17);
this.label4.TabIndex = 1356;
this.label4.Text = "Backup Path";
//
// label3
//
this.label3.AutoSize = true;
this.label3.Enabled = false;
this.label3.Font = new System.Drawing.Font("Segoe UI Semibold", 9.75F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.label3.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(64)))), ((int)(((byte)(64)))), ((int)(((byte)(64)))));
this.label3.Location = new System.Drawing.Point(394, 30);
this.label3.Name = "label3";
this.label3.Size = new System.Drawing.Size(47, 17);
this.label3.TabIndex = 1355;
this.label3.Text = "Date : ";
//
// DateTimePicker1
//
this.DateTimePicker1.Enabled = false;
this.DateTimePicker1.Font = new System.Drawing.Font("Segoe UI Semibold", 9.75F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.DateTimePicker1.Format = System.Windows.Forms.DateTimePickerFormat.Short;
this.DateTimePicker1.Location = new System.Drawing.Point(438, 26);
this.DateTimePicker1.Name = "DateTimePicker1";
this.DateTimePicker1.Size = new System.Drawing.Size(100, 25);
this.DateTimePicker1.TabIndex = 1348;
//
// Label8
//
this.Label8.AutoSize = true;
this.Label8.Font = new System.Drawing.Font("Segoe UI", 14.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.Label8.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(64)))), ((int)(((byte)(64)))), ((int)(((byte)(64)))));
this.Label8.Location = new System.Drawing.Point(8, 273);
this.Label8.Name = "Label8";
this.Label8.Size = new System.Drawing.Size(156, 25);
this.Label8.TabIndex = 1353;
this.Label8.Text = "Manually Backup";
//
// LinkLabel1
//
this.LinkLabel1.AutoSize = true;
this.LinkLabel1.Location = new System.Drawing.Point(173, 63);
this.LinkLabel1.Name = "LinkLabel1";
this.LinkLabel1.Size = new System.Drawing.Size(0, 17);
this.LinkLabel1.TabIndex = 1352;
this.LinkLabel1.LinkClicked += new System.Windows.Forms.LinkLabelLinkClickedEventHandler(this.LinkLabel1_LinkClicked);
//
// Label7
//
this.Label7.AutoSize = true;
this.Label7.Font = new System.Drawing.Font("Segoe UI", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.Label7.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(64)))), ((int)(((byte)(64)))), ((int)(((byte)(64)))));
this.Label7.Location = new System.Drawing.Point(24, 63);
this.Label7.Name = "Label7";
this.Label7.Size = new System.Drawing.Size(132, 17);
this.Label7.TabIndex = 1351;
this.Label7.Text = "Save Directory Path : ";
//
// btnSave
//
this.btnSave.BackColor = System.Drawing.Color.White;
this.btnSave.FlatStyle = System.Windows.Forms.FlatStyle.Flat;
this.btnSave.Font = new System.Drawing.Font("Segoe UI Semibold", 9F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.btnSave.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(64)))), ((int)(((byte)(64)))), ((int)(((byte)(64)))));
this.btnSave.Location = new System.Drawing.Point(223, 153);
this.btnSave.Name = "btnSave";
this.btnSave.Size = new System.Drawing.Size(89, 25);
this.btnSave.TabIndex = 1349;
this.btnSave.Text = "Save";
this.btnSave.UseVisualStyleBackColor = false;
this.btnSave.Click += new System.EventHandler(this.btnSave_Click);
//
// btnBackup
//
this.btnBackup.BackColor = System.Drawing.Color.White;
this.btnBackup.FlatStyle = System.Windows.Forms.FlatStyle.Flat;
this.btnBackup.Font = new System.Drawing.Font("Segoe UI Semibold", 9F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.btnBackup.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(64)))), ((int)(((byte)(64)))), ((int)(((byte)(64)))));
this.btnBackup.Image = global::DBBACKUP.Properties.Resources.database;
this.btnBackup.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
this.btnBackup.Location = new System.Drawing.Point(367, 352);
this.btnBackup.Name = "btnBackup";
this.btnBackup.Size = new System.Drawing.Size(137, 33);
this.btnBackup.TabIndex = 1350;
this.btnBackup.Text = "Backup Database";
this.btnBackup.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
this.btnBackup.UseVisualStyleBackColor = false;
this.btnBackup.Click += new System.EventHandler(this.btnBackup_Click);
//
// label13
//
this.label13.BackColor = System.Drawing.Color.SteelBlue;
this.label13.Dock = System.Windows.Forms.DockStyle.Right;
this.label13.Font = new System.Drawing.Font("Times New Roman", 12F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.label13.Location = new System.Drawing.Point(540, 25);
this.label13.Name = "label13";
this.label13.Size = new System.Drawing.Size(2, 365);
this.label13.TabIndex = 1362;
//
// label22
//
this.label22.BackColor = System.Drawing.Color.SteelBlue;
this.label22.Dock = System.Windows.Forms.DockStyle.Bottom;
this.label22.Font = new System.Drawing.Font("Times New Roman", 12F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.label22.Location = new System.Drawing.Point(0, 388);
this.label22.Name = "label22";
this.label22.Size = new System.Drawing.Size(540, 2);
this.label22.TabIndex = 1363;
//
// label20
//
this.label20.BackColor = System.Drawing.Color.SteelBlue;
this.label20.Dock = System.Windows.Forms.DockStyle.Left;
this.label20.Font = new System.Drawing.Font("Times New Roman", 12F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.label20.Location = new System.Drawing.Point(0, 25);
this.label20.Name = "label20";
this.label20.Size = new System.Drawing.Size(2, 363);
this.label20.TabIndex = 1364;
//
// Timer1
//
this.Timer1.Tick += new System.EventHandler(this.Timer1_Tick);
//
// FolderBrowserDialog1
//
this.FolderBrowserDialog1.SelectedPath = "C:\\ProgramData\\PRM System\\Backup\\";
//
// linkLabel2
//
this.linkLabel2.AutoSize = true;
this.linkLabel2.Location = new System.Drawing.Point(134, 307);
this.linkLabel2.Name = "linkLabel2";
this.linkLabel2.Size = new System.Drawing.Size(0, 17);
this.linkLabel2.TabIndex = 1365;
//
// linkLabel3
//
this.linkLabel3.AutoSize = true;
this.linkLabel3.Location = new System.Drawing.Point(134, 332);
this.linkLabel3.Name = "linkLabel3";
this.linkLabel3.Size = new System.Drawing.Size(0, 17);
this.linkLabel3.TabIndex = 1366;
//
// label1
//
this.label1.AutoSize = true;
this.label1.Font = new System.Drawing.Font("Segoe UI", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.label1.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(64)))), ((int)(((byte)(64)))), ((int)(((byte)(64)))));
this.label1.Location = new System.Drawing.Point(24, 88);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(156, 17);
this.label1.TabIndex = 1367;
this.label1.Text = "Auto Backup Time Span : ";
//
// txtSpan
//
this.txtSpan.Location = new System.Drawing.Point(175, 85);
this.txtSpan.MaxLength = 2;
this.txtSpan.Name = "txtSpan";
this.txtSpan.Size = new System.Drawing.Size(57, 25);
this.txtSpan.TabIndex = 1368;
this.txtSpan.KeyPress += new System.Windows.Forms.KeyPressEventHandler(this.txtSpan_KeyPress);
//
// txtNoOfFiles
//
this.txtNoOfFiles.Location = new System.Drawing.Point(373, 85);
this.txtNoOfFiles.MaxLength = 2;
this.txtNoOfFiles.Name = "txtNoOfFiles";
this.txtNoOfFiles.Size = new System.Drawing.Size(57, 25);
this.txtNoOfFiles.TabIndex = 1370;
this.txtNoOfFiles.KeyPress += new System.Windows.Forms.KeyPressEventHandler(this.txtSpan_KeyPress);
//
// label2
//
this.label2.AutoSize = true;
this.label2.Font = new System.Drawing.Font("Segoe UI", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.label2.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(64)))), ((int)(((byte)(64)))), ((int)(((byte)(64)))));
this.label2.Location = new System.Drawing.Point(240, 89);
this.label2.Name = "label2";
this.label2.Size = new System.Drawing.Size(136, 17);
this.label2.TabIndex = 1369;
this.label2.Text = "No Of Files To Keep : ";
//
// label10
//
this.label10.AutoSize = true;
this.label10.Font = new System.Drawing.Font("Segoe UI", 14.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.label10.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(64)))), ((int)(((byte)(64)))), ((int)(((byte)(64)))));
this.label10.Location = new System.Drawing.Point(8, 28);
this.label10.Name = "label10";
this.label10.Size = new System.Drawing.Size(126, 25);
this.label10.TabIndex = 1371;
this.label10.Text = "Backup Setup";
//
// label11
//
this.label11.BackColor = System.Drawing.Color.LightGray;
this.label11.Font = new System.Drawing.Font("Times New Roman", 12F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.label11.Location = new System.Drawing.Point(0, 190);
this.label11.Name = "label11";
this.label11.Size = new System.Drawing.Size(540, 2);
this.label11.TabIndex = 1372;
//
// label12
//
this.label12.AutoSize = true;
this.label12.Font = new System.Drawing.Font("Segoe UI", 14.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.label12.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(64)))), ((int)(((byte)(64)))), ((int)(((byte)(64)))));
this.label12.Location = new System.Drawing.Point(8, 196);
this.label12.Name = "label12";
this.label12.Size = new System.Drawing.Size(149, 25);
this.label12.TabIndex = 1373;
this.label12.Text = "Last Backup Info";
//
// lblLastBackupInfo
//
this.lblLastBackupInfo.Font = new System.Drawing.Font("Segoe UI", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.lblLastBackupInfo.ForeColor = System.Drawing.Color.FromArgb(((int)(((byte)(64)))), ((int)(((byte)(64)))), ((int)(((byte)(64)))));
this.lblLastBackupInfo.Location = new System.Drawing.Point(24, 223);
this.lblLastBackupInfo.Name = "lblLastBackupInfo";
this.lblLastBackupInfo.Size = new System.Drawing.Size(506, 45);
this.lblLastBackupInfo.TabIndex = 1374;
this.lblLastBackupInfo.Text = "Last backup was taken {0} at {1} in location {2}.";
//
// FrmDbBackup
//
this.AutoScaleDimensions = new System.Drawing.SizeF(7F, 17F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.BackColor = System.Drawing.Color.White;
this.ClientSize = new System.Drawing.Size(542, 390);
this.Controls.Add(this.Panel1);
this.Controls.Add(this.lblLastBackupInfo);
this.Controls.Add(this.label12);
this.Controls.Add(this.label11);
this.Controls.Add(this.label10);
this.Controls.Add(this.txtNoOfFiles);
this.Controls.Add(this.label2);
this.Controls.Add(this.linkLabel3);
this.Controls.Add(this.linkLabel2);
this.Controls.Add(this.label20);
this.Controls.Add(this.label22);
this.Controls.Add(this.label13);
this.Controls.Add(this.label6);
this.Controls.Add(this.txtDbName);
this.Controls.Add(this.label5);
this.Controls.Add(this.label4);
this.Controls.Add(this.btnBackup);
this.Controls.Add(this.DateTimePicker1);
this.Controls.Add(this.Label8);
this.Controls.Add(this.LinkLabel1);
this.Controls.Add(this.Label7);
this.Controls.Add(this.btnSave);
this.Controls.Add(this.btnClose);
this.Controls.Add(this.Label21);
this.Controls.Add(this.txtSpan);
this.Controls.Add(this.label1);
this.Controls.Add(this.label3);
this.Font = new System.Drawing.Font("Segoe UI", 9.75F);
this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.None;
this.Margin = new System.Windows.Forms.Padding(3, 4, 3, 4);
this.Name = "FrmDbBackup";
this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
this.Text = "frmDbBackup";
this.Load += new System.EventHandler(this.FrmDbBackup_Load);
this.Panel1.ResumeLayout(false);
this.Panel1.PerformLayout();
this.ResumeLayout(false);
this.PerformLayout();
}
#endregion
internal System.Windows.Forms.Label Label21;
internal System.Windows.Forms.Button btnClose;
internal System.Windows.Forms.Panel Panel1;
internal System.Windows.Forms.Label Label9;
internal System.Windows.Forms.ProgressBar ProgressBarEx5;
internal System.Windows.Forms.Label label6;
private System.Windows.Forms.TextBox txtDbName;
internal System.Windows.Forms.Label label5;
internal System.Windows.Forms.Label label4;
internal System.Windows.Forms.Label label3;
internal System.Windows.Forms.Button btnBackup;
internal System.Windows.Forms.Label Label8;
internal System.Windows.Forms.LinkLabel LinkLabel1;
internal System.Windows.Forms.Label Label7;
internal System.Windows.Forms.Button btnSave;
internal System.Windows.Forms.Label label13;
internal System.Windows.Forms.Label label22;
internal System.Windows.Forms.Label label20;
internal System.Windows.Forms.Timer Timer1;
internal System.Windows.Forms.FolderBrowserDialog FolderBrowserDialog1;
private System.Windows.Forms.Label linkLabel2;
private System.Windows.Forms.Label linkLabel3;
internal System.Windows.Forms.Label label1;
private System.Windows.Forms.TextBox txtSpan;
internal System.Windows.Forms.DateTimePicker DateTimePicker1;
private System.Windows.Forms.TextBox txtNoOfFiles;
internal System.Windows.Forms.Label label2;
internal System.Windows.Forms.Label label10;
internal System.Windows.Forms.Label label11;
internal System.Windows.Forms.Label label12;
internal System.Windows.Forms.Label lblLastBackupInfo;
}
}

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.

using System;
using System.IO;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
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.

SqlCommand cmd;
SqlConnection sqlCon;
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.

public FrmDbBackup()
{
InitializeComponent();
sqlCon = new SqlConnection(conString);
sqlCon.Open();
}

Code for FrmDbBackup.cs

using System;
using System.IO;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace DBBACKUP
{
public partial class FrmDbBackup : Form
{
SqlCommand cmd;
SqlConnection sqlCon;
string conString = "Data Source=.; Initial Catalog=DemoTest; User Id=sa; Password=password;";
public FrmDbBackup()
{
InitializeComponent();
sqlCon = new SqlConnection(conString);
sqlCon.Open();
}
private void FrmDbBackup_Load(object sender, EventArgs e)
{
LoadBackinfo();
if (LinkLabel1.Text == string.Empty)
{
LinkLabel1.Text = "Click To Set Directory Path";
}
}
private void LoadBackinfo()
{
if (sqlCon.State == ConnectionState.Closed)
{
sqlCon.Open();
}
DataSet dsData = new DataSet();
cmd = new SqlCommand("DATABASE_BACKUP", sqlCon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ACTIONTYPE", "BACKUP_INFO");
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(dsData);
if (dsData.Tables.Count > 0)
{
if (dsData.Tables[0].Rows.Count > 0)
{
LinkLabel1.Text = dsData.Tables[0].Rows[0]["LOCATION"].ToString();
txtNoOfFiles.Text = dsData.Tables[0].Rows[0]["NoOfFiles"].ToString();
txtSpan.Text = dsData.Tables[0].Rows[0]["DayInterval"].ToString();
txtDbName.Text = dsData.Tables[0].Rows[0]["DATABASENAME"].ToString();
linkLabel2.Text = dsData.Tables[0].Rows[0]["LOCATION"].ToString();
linkLabel3.Text = dsData.Tables[0].Rows[0]["DATABASENAME"].ToString() + "-" + DateTime.Now.ToString("ddMMyyyyHHmmssfff") + ".bak";
}
if (dsData.Tables[1].Rows.Count > 0)
{
lblLastBackupInfo.Text = string.Format("Last backup was taken {0} at {1} in location {2}.", dsData.Tables[1].Rows[0]["BackupType"].ToString(),
dsData.Tables[1].Rows[0]["BackupDate"].ToString(), dsData.Tables[1].Rows[0]["Location"].ToString());
}
else
lblLastBackupInfo.Text = "No Backups !!!";
}
}
private void btnSave_Click(object sender, EventArgs e)
{
if (LinkLabel1.Text == "Click To Set Directory Path")
{
MessageBox.Show("Click To Set Directory Path", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else if (txtSpan.Text == string.Empty)
{
MessageBox.Show("Enter how many last backup files required ", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
int numFlag;
if (sqlCon.State == ConnectionState.Closed)
{
sqlCon.Open();
}
cmd = new SqlCommand("DATABASE_BACKUP", sqlCon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ACTIONTYPE", "INSERT_BACKUP_INFO");
cmd.Parameters.AddWithValue("@DatabaseName", txtDbName.Text); // Your Database Name
cmd.Parameters.AddWithValue("@Location", LinkLabel1.Text);
cmd.Parameters.AddWithValue("@DayInterval", txtSpan.Text);
cmd.Parameters.AddWithValue("@SoftwareDate", DateTimePicker1.Text);
cmd.Parameters.AddWithValue("@NoOfFiles", txtNoOfFiles.Text);
numFlag = cmd.ExecuteNonQuery();
if (numFlag > 0)
{
MessageBox.Show("Data saved successfully.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
LoadBackinfo();
}
else
{
MessageBox.Show("Data not saved. Plaese Try Again.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
private void Timer1_Tick(object sender, EventArgs e)
{
ProgressBarEx5.Value += 1;
if (ProgressBarEx5.Value == 100)
{
ProgressBarEx5.Visible = false;
Timer1.Stop();
Panel1.Visible = false;
ProgressBarEx5.Text = "Finished";
}
}
private void LinkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
FolderBrowserDialog1.ShowDialog();
LinkLabel1.Text = FolderBrowserDialog1.SelectedPath;
}
private void btnBackup_Click(object sender, EventArgs e)
{
if (linkLabel2.Text == string.Empty)
{
MessageBox.Show("Please Set Backup Setting", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else if (linkLabel3.Text == string.Empty)
{
MessageBox.Show("Please Set Backup Setting", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
string filaPath;
if (!linkLabel2.Text.EndsWith(@"\"))
{
filaPath = linkLabel2.Text + @"\" + linkLabel3.Text;
}
else
{
filaPath = linkLabel2.Text + linkLabel3.Text;
}
int numFlag;
if (sqlCon.State == ConnectionState.Closed)
{
sqlCon.Open();
}
cmd = new SqlCommand("DATABASE_BACKUP", sqlCon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ACTIONTYPE", "DB_BACKUP");
cmd.Parameters.AddWithValue("@DATABASE", txtDbName.Text); // Your Database Name
cmd.Parameters.AddWithValue("@FILEPATH", filaPath);
cmd.Parameters.AddWithValue("@BackupName", linkLabel3.Text);
cmd.Parameters.AddWithValue("@SoftwareDate", DateTimePicker1.Text);
cmd.Parameters.AddWithValue("@Type", "Manually");
numFlag = cmd.ExecuteNonQuery();
DataTable dtLoc = new DataTable();
cmd = new SqlCommand("DATABASE_BACKUP", sqlCon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ACTIONTYPE", "REMOVE_LOCATION");
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dtLoc);
for (int i = 0; i < dtLoc.Rows.Count; i++)
{
string delLoc = dtLoc.Rows[i][0].ToString();
string filepath = delLoc;
if (File.Exists(filepath))
{
File.Delete(filepath);
}
}
if (numFlag > 0)
{
Panel1.Visible = true;
Panel1.Location = new Point(58, 138);
Panel1.Height = 117;
Panel1.Width = 446;
ProgressBarEx5.Visible = true;
ProgressBarEx5.Value = 0;
Timer1.Start();
LoadBackinfo();
}
else
{
MessageBox.Show("Plaese Try Again.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
private void btnClose_Click(object sender, EventArgs e)
{
this.Close();
}
private void txtSpan_KeyPress(object sender, KeyPressEventArgs e)
{
if ((e.KeyChar >= 48 && e.KeyChar <= 57) || e.KeyChar == 46 || e.KeyChar == 8)
{
e.Handled = false;
}
else
{
e.Handled = true;
}
}
}
}

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.

4 Comments

Join the conversation! Your thoughts help the community grow.

vivek chelliah5y ago
Hi i need restore db backup same sp
- Amit Mohanty4y ago
  Use this query to restore database. RESTORE DATABASE DemoTest FROM DISK = 'E:\DemoTest_full.bak'
Abhishek Mishra7y ago
Quiet useful content.
- Amit Mohanty7y ago
  Thank you Abhishek