How to restore a database backup from an application in C #?

0

I've been looking into how to restore a BD backup through a C # application, but I had a hard time understanding logic. I have already programmed a form that does the backup and I do not have and idea of how a restoration would work, for example, how would I select the backup file?

Follow the code I use to back it 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();
}

used code to stop restore:
I 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);

        }
    }
    
asked by anonymous 21.11.2016 / 17:43

2 answers

2

Based on what you did for backup, do the same for restore. The only caution is that to restore, all connections must be turned off.

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);

        }
    
21.11.2016 / 18:09
2

There are several ways to do this.

I suggest that you create a service to execute the command, which when triggered closes all connections to the database and executes the command.

The command to run 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 FILE = 6 value when doing a Full Restore and the FILE = 9 value when it is a partial restore

You can find details here :

    
21.11.2016 / 18:09