How to Send Backup or Restore command to Command Window

Question

OK, I think I need to clarify something about my post that I forgot to mention previously. My "test application" in Visual Studio 2010 has a SQL Server 2008 R2 EXPRESS database. However, the database is Not from a stand-alone SQL Server Express install. Rather, the data file, i.e., .mdf and .ldf are from selecting in VS "Project\Add New Item\Data\Service-based Database". Thus my "BkUp_SMO.mdf" data file.

I'm not sure if the above makes a difference, but I've tried multiple examples of using Microsoft.SqlServer.Management objects, SMO, but without success. I have added the required .DLLs, i.e, Microsoft.SqlServer.ConnectionInfo, Microsoft.SqlServer.Management.Sdk.Sfc, Microsoft.SqlServer.Smo, Microsoft.SqlServer.SmoExtended.

In my code I have "using" statements for both Microsoft.SqlServer.Management.Common and Microsoft.SqlServer.Management.Smo. I have even added a "using" for System.Deployment.Application in order to set a string value for the path back to the ClickOnce deployment folder where the DB and log file resides, using String dbPath = ApplicationDeployment.CurrentDeployment.DataDirectory;

In addition to the article referenced below, I have also tried examples from another article, i.e., "Backing up an SQL Database in C#" Backing up an SQL Database in C#

Is it not possible to perform a Backup and Restore on a Visual Studio created SQL database?

I have written a test application in C#, with the intent of sending a SQL Server Backup or Restore command via a Command Line. I have based some of my code on an article titled: Backup and Restore Your SQL Server Database from the Command Line

Backup and Restore Your SQL Server Database from the Command Line

The full application will be a user_App, where I don't want the end-user to have to open a command window and type in anything, hence I'm trying to send the required commands by C# code as shown below. My problem is that, the code runs without an exception, the CMD window opens and closes, but without any backup of my SQL Server 2008 R2 data file (.mdf) taking place.

Please suggest what I'm missing in my code, or a better way to accomplish this. Also, will a Full backup automatically backup the log file (.ldf) as well?

First code attempt
private void btnChoose_Click(object sender, EventArgs e)
{
    if (optBkupCMD.Checked)
    {
        StringBuilder bkup = new StringBuilder();
        bkup.Append("SqlCmd -E -S ");
        bkup.Append(Environment.MachineName);//servername appears to be same as computer name.
        bkup.Append(" –Q “BACKUP DATABASE [BkUp_SMO.mdf] TO DISK=’C:\\Backups\\BkUp_SMO.bak'”");
        string theBackup = bkup.ToString();

    using (Process process = new Process())
    {
        process.StartInfo.FileName = "cmd.exe";
        process.StartInfo.RedirectStandardInput = true;
        process.StartInfo.RedirectStandardOutput = true;
        process.StartInfo.CreateNoWindow = false;
        process.StartInfo.UseShellExecute = false;
        process.StartInfo.Arguments = @"/C";
        process.Start();
        process.StandardInput.WriteLine(theBackup);
        process.StandardInput.Flush();
        process.StandardInput.Close();
        process.WaitForExit();
        Console.WriteLine(process.StandardOutput.ReadToEnd());
    }
else if (optRestoreCMD.Checked)
{
    StringBuilder rstr = new StringBuilder();
    rstr.Append("SqlCmd -E -S ");
    rstr.Append(Environment.MachineName);
    rstr.Append(" –Q “RESTORE DATABASE [BkUp_SMO.mdf] FROM DISK=’C:\\Backups\\BkUp_SMO.bak'”");
    string restore = rstr.ToString();

    using (Process process = new Process())
    {
        process.StartInfo.FileName = "cmd.exe";
        process.StartInfo.RedirectStandardInput = true;
        process.StartInfo.RedirectStandardOutput = true;
        process.StartInfo.CreateNoWindow = false;
        process.StartInfo.UseShellExecute = false;
        process.StartInfo.Arguments = @"/C";
        process.Start();
        process.StandardInput.WriteLine(restore);
        process.StandardInput.Flush();
        process.StandardInput.Close();
        process.WaitForExit();
        Console.WriteLine(process.StandardOutput.ReadToEnd());
    }
}

}

My 2nd code attempt.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Threading;
using Microsoft.Win32;
using System.Deployment.Application;
using System.Diagnostics;
using System.IO;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace DB_Bkup_using_SMO
{
    public partial class Bkup_in_CSharp : Form
    {
        public Bkup_in_CSharp()
        {
            InitializeComponent();
        }

    private void btnBkViaCsharp_Click(object sender, EventArgs e)
    {
        string filePath = ApplicationDeployment.CurrentDeployment.DataDirectory;
        BackupDatabase(filePath);
    }

    private void btnRestViaCsharp_Click(object sender, EventArgs e)
    {
        string filePath = ApplicationDeployment.CurrentDeployment.DataDirectory;
        RestoreDatabase(filePath);
    }

    ///<summary>
    ///Backup a whole database to the specified file.
    ///</summary>
    ///<remarks>
    ///The database must not be in use when backing up.
    ///The folder holding the file must have appropriate permissions given
    ///</remarks>
    ///<param name="backupFile">Full path to file to hold the backup</param>
    public static void BackupDatabase(string backupFile)
    {
        try
        {
            ServerConnection con = new ServerConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\BkUp_SMO.mdf;Integrated Security=True;User Instance=True");

            Server server = new Server(con);
            Backup source = new Backup();
            source.Database = "BkUp_SMO.mdf";
            source.Action = BackupActionType.Database;

            source.LogTruncation = BackupTruncateLogType.Truncate;
            BackupDeviceItem destination = new BackupDeviceItem(backupFile, DeviceType.File);
            source.Devices.Add(destination);

            source.SqlBackup(server);
            con.Disconnect();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message + " " + ex.InnerException);
        }
    }

    ///<summary>
    ///Restore a whole database from a backup file.
    ///</summary>
    ///<remarks>
    ///The database must be in use when backing up.
    ///The folder holding the file must have appropriate permissions given.
    ///</remarks>
    ///<param name="backupFile">Full path to file to holding the backup</param>
    public static void RestoreDatabase(string backupFile)
    {
        try
        {
            ServerConnection con = new ServerConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\BkUp_SMO.mdf;Integrated Security=True;User Instance=True");

            Server server = new Server(con);
            Restore destination = new Restore();
            destination.Database = "BkUp_SMO.mdf";
            destination.Action = RestoreActionType.Database;
            destination.Action = RestoreActionType.Log;
            BackupDeviceItem source = new BackupDeviceItem(backupFile, DeviceType.File);
            destination.Devices.Add(source);
            destination.ReplaceDatabase = true;
            destination.SqlRestore(server);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message + " " + ex.InnerException);
        }

    }
}
}

Answer 1

This is my code to backup also SQL Server 2008 R2.

This kind of basic example codes are manywhere if you try to search.

Just try but no need to mark this as an answer becuase it's many out here.

string masterdb_ConnectionString = string.Format(@"Data Source={0};Initial Catalog=Master;Connect Timeout=79;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False;Integrated Security=True;", System.Environment.MachineName);

using (SqlConnection masterdbConn = new SqlConnection())
{
    masterdbConn.ConnectionString = mastedb_rConnectionString;
    masterdbConn.Open();

    using (SqlCommand multiuser_rollback_dbcomm = new SqlCommand())
    {
        multiuser_rollback_dbcomm.Connection = masterdbConn;
        multiuser_rollback_dbcomm.CommandText= @"ALTER DATABASE yourdbname SET MULTI_USER WITH ROLLBACK IMMEDIATE";
        multiuser_rollback_dbcomm.CommandTimeout = 79;

        multiuser_rollback_dbcomm.ExecuteNonQuery();
    }
    masterdbConn.Close();
}

SqlConnection.ClearAllPools();

string yourdb_ConnectionString= "connectionstring for yourdb here";

using (SqlConnection backupConn = new SqlConnection())
{
    backupConn.ConnectionString = yourdb_ConnectionString;
    backupConn.Open();

    using (SqlCommand backupcomm = new SqlCommand())
    {
        backupcomm.Connection = backupConn;
        backupcomm.CommandText = string.Format(@"BACKUP DATABASE yourdbname TO DISK='c:\yourdbname.bak'", DateTime.Today.ToString("yyyy/MM/dd"));
        backupcomm.CommandTimeout = 79;

        backupcomm.ExecuteNonQuery();
    }
    backupConn.Close();
}

Update- Is there going to be SQL Server 2008 R2 installed on clients'(users') computer?

You're already using 'Integrated Security=True' which means the user using this connectionstring will have all(full) permissions as Administrator. Connecting to Master Database is obviously no problem at all.

Regarding IMMEDIATE ROLLBACK, this finishes all the un-finished transactions finished just like 'hands off from the DB before we backup'. In other word to say, Before we close a restaurant, if we don't announce that this restaurant is going to be closed, if we suddenly close the restaurant, some customers might remain still having foods eventhough the restaurant is closing.

Have a look at, ALTER DATABASE IMMEDIATE ROLLBACK, Technet

This example uses the termination option WITH ROLLBACK IMMEDIATE in the first ALTER DATABASE statement. All incomplete transactions will be rolled back and any other connections to the AdventureWorks2008R2 sample database will be immediately disconnected.

And at last, It seems you're trying with SMO. I experienced difficulty for days with it but finally failed and went another way.