How to restore a database backup from a c#application?

Question

I have searched how to restore a backup of BD through a c#application, but I had difficulty understanding the logic. I have already programmed a form that performs the backup and have no idea how a restore would work, for example, how I would select the backup file?

Follow the code I use to back up if it helps in any way.

    private void FrmBackup_Load(object sender, EventArgs e)
    {
        txtServidor.Text = "USER-PC";
        txtDB.Text = "DBClinica_TCC";

    }

    private void btnSqlComand_Click(object sender, EventArgs e)
    {

        SqlConnection LCN = new SqlConnection();
        SqlCommand LCom = new SqlCommand();
        string LStrSql = "";
        //String de Conexão deve ser substituída pela do seu Servidor
        SqlConnectionStringBuilder Lstr = new SqlConnectionStringBuilder();
        Lstr.DataSource = txtServidor.Text;
        Lstr.InitialCatalog = txtDB.Text;
        if(rbtSql.Checked == true)
        {
            Lstr.IntegratedSecurity = false;
        }
        else
        {
            Lstr.IntegratedSecurity = true;
        }
        Lstr.UserID = txtLoginBD.Text;
        Lstr.Password = txtSenhaBD.Text;

LCN.ConnectionString = Lstr.ToString();
try
{
    //Abrindo a conexão
    LCN.Open();
    LCom.Connection = LCN; 
    DateTime d=DateTime.Now;
    //Criando o Comando que será executado para gerar o Backup       

    LStrSql = "BACKUP DATABASE [" + txtDB.Text + "] TO DISK='" + txtArquivo.Text +d.Day.ToString()+d.Month.ToString()+ "' WITH COPY_ONLY";
    LCom.CommandText = LStrSql;
    LCom.ExecuteNonQuery();
    LCN.Close();
    MessageBox.Show("Backup Gerado com Sucesso");

    Server\MSSQL.2\MSSQL\Backup
}
catch (Exception ex)
{
    MessageBox.Show("Houve um erro ao tentar executar o Backup: "+ex.Message);
    LCN.Close();
}

code used to restore:
use a OpenFileDialog para pegar o arquvo de backup

private void btnSelect_Click(object sender, EventArgs e)
    {
        this.ofdRest.Multiselect = false;
        this.ofdRest.Title = "Selecionar Backup";
        ofdRest.InitialDirectory = @"C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup";
        ofdRest.CheckFileExists = true;
        ofdRest.CheckPathExists = true;

        DialogResult dr = this.ofdRest.ShowDialog();

        if(dr == DialogResult.OK)
        {
            foreach(String arquivo in ofdRest.FileNames)
            {
                txtArquivo.Text += arquivo;
            }
        }
    }

    private void btnRestore_Click(object sender, EventArgs e)
    {
        SqlConnectionStringBuilder Lstr = new SqlConnectionStringBuilder();
         Lstr.DataSource = txtServidor.Text;
        Lstr.InitialCatalog = txtDB.Text;
        if (rbtSql.Checked == true)
        {
            Lstr.IntegratedSecurity = false;
        }
        else
        {
            Lstr.IntegratedSecurity = true;
        }
        Lstr.UserID = txtLoginBD.Text;
        Lstr.Password = txtSenhaBD.Text;
        SqlConnection cs = new SqlConnection(Lstr.ToString());

        try
        {
            cs.Open();
            String sqlquery = "Use Master ALTER DATABASE [" + txtDB.Text + "] SET OFFLINE WITH ROLLBACK IMMEDIATE RESTORE DATABASE [" + txtDB.Text + "] FROM DISK ='" + ofdRest.FileName + "' ALTER DATABASE [" + txtDB.Text + "] SET ONLINE WITH ROLLBACK IMMEDIATE";
            SqlCommand cmd = new SqlCommand(sqlquery, cs);
            cmd.ExecuteNonQuery();
            cs.Close();
            cs.Dispose();
            MessageBox.Show("restore complete");
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);

        }
    }

Answer 1

There are many ways to do this.

I suggest you create a service to execute the command, which when fired, closes all connections to the bank and executes the command.

The command to be executed would be as follows:

RESTORE DATABASE AdventureWorks2012  
   FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak'  
   WITH FILE = 6  
      NORECOVERY;  
RESTORE DATABASE AdventureWorks2012  
   FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak'  
   WITH FILE = 9  
      RECOVERY; 

Using the value FILE = 6 when doing a Full Restore and the value FILE = 9 when it is a partial restoration

You find details here:

Answer 2

Based on what you did for the backup, do the same for the Restore. The only precaution is that to do the Restore, all connections have to be disconnected.

SqlConnection cs = new SqlConnection(strConnString);

        try
        {
            cs.Open();
            String sqlquery = "Use Master ALTER DATABASE databasename SET OFFLINE WITH ROLLBACK IMMEDIATE RESTORE DATABASE databasename FROM DISK ='" + txtRestoreFileLoc.Text + "' ALTER DATABASE databasename SET ONLINE WITH ROLLBACK IMMEDIATE";
            SqlCommand cmd = new SqlCommand(sqlquery, cs);
            cmd.ExecuteNonQuery();
            cs.Close();
            cs.Dispose();
            MessageBox.Show("restore complete");
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);

        }