Backup of SQL Server Database using Command Prompt in C# and VB.Net

Refer

SQL Server Express Automated Backup Console Application C#

Hi chinmayy,

Refer below sample for creating the backup file using Console application.

The following code uses TSQL script to run through and create the .bak file in the location specified.

Code

C#

using System.Text;
using System.Configuration;
using System.Data.SqlClient;

class Program
{
    static void Main(string[] args)
    {
        Console.WriteLine("Enter Database name: ");
        string backupPath = "D:\\Files\\";
        // Generating Backup Script.
        StringBuilder sb = new StringBuilder();
        sb.AppendLine("DECLARE @name VARCHAR(50) ");
        sb.AppendLine("DECLARE @path VARCHAR(256) ");
        sb.AppendLine("DECLARE @fileName VARCHAR(256) ");
        sb.AppendLine("DECLARE @fileDate VARCHAR(20) = CONCAT(CONVERT(VARCHAR(8), GETDATE(), 112), CONVERT(VARCHAR(6), GETDATE(), 115)) ");
        sb.AppendLine("SET @path = '" + backupPath + "' ");
        sb.AppendLine("DECLARE db_cursor CURSOR FOR ");
        sb.AppendLine("SELECT name ");
        sb.AppendLine("FROM master.dbo.sysdatabases ");
        sb.AppendLine("WHERE name = '" + Console.ReadLine() + "' ");
        sb.AppendLine("OPEN db_cursor ");
        sb.AppendLine("FETCH NEXT FROM db_cursor INTO @name ");
        sb.AppendLine("WHILE @@FETCH_STATUS = 0 ");
        sb.AppendLine("BEGIN ");
        sb.AppendLine("SET @fileName = @path + @name + '_' + @fileDate + '.bak' ");
        sb.AppendLine("BACKUP DATABASE @name TO DISK = @fileName ");
        sb.AppendLine("FETCH NEXT FROM db_cursor INTO @name ");
        sb.AppendLine("END ");
        sb.AppendLine("CLOSE db_cursor ");
        sb.AppendLine("DEALLOCATE db_cursor; ");
        // Executing the Backup Script.
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand(sb.ToString(), con))
            {
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
        Console.WriteLine("Back successfully created.");
        Console.ReadLine();
    }
}

VB.Net

Imports System.Text
Imports System.Configuration
Imports System.Data.SqlClient

Module Module1
    Sub Main()
        Console.WriteLine("Enter Database name: ")
        Dim backupPath As String = "D:\Files\"
        ' Generating Backup Script.
        Dim sb As StringBuilder = New StringBuilder()
        sb.AppendLine("DECLARE @name VARCHAR(50) ")
        sb.AppendLine("DECLARE @path VARCHAR(256) ")
        sb.AppendLine("DECLARE @fileName VARCHAR(256) ")
        sb.AppendLine("DECLARE @fileDate VARCHAR(20) = CONCAT(CONVERT(VARCHAR(8), GETDATE(), 112), CONVERT(VARCHAR(6), GETDATE(), 115)) ")
        sb.AppendLine("SET @path = '" & backupPath & "' ")
        sb.AppendLine("DECLARE db_cursor CURSOR FOR ")
        sb.AppendLine("SELECT name ")
        sb.AppendLine("FROM master.dbo.sysdatabases ")
        sb.AppendLine("WHERE name = '" & Console.ReadLine() & "' ")
        sb.AppendLine("OPEN db_cursor ")
        sb.AppendLine("FETCH NEXT FROM db_cursor INTO @name ")
        sb.AppendLine("WHILE @@FETCH_STATUS = 0 ")
        sb.AppendLine("BEGIN ")
        sb.AppendLine("SET @fileName = @path + @name + '_' + @fileDate + '.bak' ")
        sb.AppendLine("BACKUP DATABASE @name TO DISK = @fileName ")
        sb.AppendLine("FETCH NEXT FROM db_cursor INTO @name ")
        sb.AppendLine("END ")
        sb.AppendLine("CLOSE db_cursor ")
        sb.AppendLine("DEALLOCATE db_cursor; ")
        'Executing the Backup Script.
        Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using con As SqlConnection = New SqlConnection(constr)
            Using cmd As SqlCommand = New SqlCommand(sb.ToString(), con)
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
            End Using
        End Using
        Console.WriteLine("Back successfully created.")
        Console.ReadLine()
    End Sub
End Module

After build the project the .exe file will be available in the Debug folder.

Then follow the below steps to execute the exe file using the Command Prompt.

1. Open the command prompt using windows + R.

2. Type cmd.

3. Inside the command window write the following command.

start "" "D:\Sample_655358\Sample_655358_CS\bin\Debug\Sample_655358_CS.exe"
start "" "D:\Sample_655358\Sample_655358_CS\bin\Debug\Sample_655358_VB.exe"

Then, enter the Database name in the next command window and click on enter.

Finally the .bak file is generated in the specified folder.