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. )
  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
  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
  36. IF @ACTIONTYPE = 'DB_BACKUP'
  37. BEGIN
  38. BEGIN TRY
  39. BACKUP DATABASE @DATABASE
  40. TO DISK = @FILEPATH;
  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
  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;
  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. }
  23. #region Windows Form Designer generated code
  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();
  431. }
  433. #endregion
  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;
  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;";
  16. public FrmDbBackup()
  17. {
  18. InitializeComponent();
  19. sqlCon = new SqlConnection(conString);
  20. sqlCon.Open();
  21. }
  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. }
  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. }
  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();
  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. }
  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. }
  116. private void LinkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
  117. {
  118. FolderBrowserDialog1.ShowDialog();
  119. LinkLabel1.Text = FolderBrowserDialog1.SelectedPath;
  120. }
  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);
  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. }
  191. private void btnClose_Click(object sender, EventArgs e)
  192. {
  193. this.Close();
  194. }
  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.