0
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);
}
}
But the question is just how do you choose the file? If so, just use an Openfiledialog. Try the following: Openfiledialog ofd= new Openfiledialog(); ofd.Showdialog();
– Renato Afonso
Actually I wanted to know how does the restore in general, the question of selecting the file is one of the doubts, but thank you!
– Gabriel Arruda
I won’t post an answer because I’m running out of time and I’ve never used this solution, but I’ve seen the use of backup/Store with Microsoft’s SMO library. https://msdn.microsoft.com/pt-br/library/microsoft.sqlserver.management.smo.restore.aspx It is a more robust solution
– user26552