数据库备份或还原仅在我放置 |数据目录|连接字符串到应用配置中
本文关键字:连接 数据目录 字符串 应用 配置 还原 备份 数据库 | 更新日期: 2023-09-27 18:37:13
我正在使用Visual Studio 2012 Service base数据库。我认为它是备份,因为我看到了.BAK文件,但是当我恢复时,它不起作用。两者都在运行时没有错误,但我认为我的代码有问题。这是我的代码:
private void btnBackUp_Click(object sender, EventArgs e)
{
try
{
progressBar1.Visible = true;
progressBar1.Value = 15;
bool bBackUpStatus = true;
Cursor.Current = Cursors.WaitCursor;
if (Directory.Exists(@"D:'Backup_MAConvent"))
{
if (File.Exists(@"D:'Backup_MAConvent'MAConvent_Backup.bak"))
{
if (MessageBox.Show(@"Do you want to replace it?", "Back", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
File.Delete(@"D:'Backup_MAConvent'MAConvent_Backup.bak");
}
else
bBackUpStatus = false;
}
}
else
Directory.CreateDirectory(@"D:'Backup_MAConvent");
if (bBackUpStatus)
{
con.Open();
progressBar1.Value = 25;
string path1 = System.IO.Path.GetFullPath(@"SchoolDatabase.mdf");
SqlCommand cmd2 = new SqlCommand("backup database [" + path1 + @"] to disk ='D:'Backup_MAConvent'MAConvent_Backup.bak' with init,stats=10", con);
cmd2.ExecuteNonQuery();
progressBar1.Value = 35;
con.Close();
timer1.Start();
MessageBox.Show("Backup of the Database saved Successfully", "Back", MessageBoxButtons.OK, MessageBoxIcon.Information);
timer1.Stop();
progressBar1.Value = 10;
progressBar1.Visible = false;
}
}
catch
{
MessageBox.Show(@"Backup Error, Please close the software & restart and then try again to backup", "Backup", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void btnRestore_Click(object sender, EventArgs e)
{
Cursor.Current = Cursors.WaitCursor;
progressBar1.Visible = true;
progressBar1.Value = 15;
try
{
if (File.Exists(@"D:'Backup_MAConvent'MAConvent_Backup.bak"))
{
if (MessageBox.Show("Are you sure you restore?", "Back", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
con.Open();
progressBar1.Value = 25;
SqlCommand cmd;
cmd = new SqlCommand("use master", con);
cmd.ExecuteNonQuery();
progressBar1.Value = 35;
string path1 = System.IO.Path.GetFullPath(@"SchoolDatabase.mdf");
cmd = new SqlCommand("restore database [" + path1 + @"] from disk = 'D:'Backup_MAConvent'MAConvent_Backup.bak'", con);
cmd.ExecuteNonQuery();
con.Close();
timer1.Start();
MessageBox.Show("Database has been Restored", "Restoration", MessageBoxButtons.OK, MessageBoxIcon.Information);
timer1.Stop();
progressBar1.Value = 10;
progressBar1.Visible = false;
}
}
else
{
MessageBox.Show(@"This is not in the correct path, Please close the software & restart and then try again to restore", "Restoration", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception exp)
{
MessageBox.Show(exp.Message);
}
}
private void timer1_Tick(object sender, EventArgs e)
{
try
{
progressBar1.Value = 50;
progressBar1.PerformStep();
if (progressBar1.Value <= progressBar1.Maximum)
timer1.Stop();
progressBar1.Value = progressBar1.Maximum;
}
catch
{
}
}
下面是我的连接字符串:
<configSections>
</configSections>
<connectionStrings>
<add name="SchoolManagement.Properties.Settings.SchoolDatabaseConnectionString"
connectionString="Data Source=.'SQLEXPRESS;AttachDbFilename=G:'Users'ARORAS'documents'visual studio 2012'Projects'SchoolManagement'SchoolManagement'SchoolDatabase.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
<startup useLegacyV2RuntimeActivationPolicy="true">
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.1" />
</startup>
但是当我将连接字符串更改为 |数据目录|备份和还原都正常工作。如下
connectionString="Data Source=.'SQLEXPRESS;AttachDbFilename=|DataDirectory|'SchoolDatabase.mdf;Integrated Security=True;User Instance=True"
但我不想使用 |数据目录|字符串。请建议在备份和还原代码中更改什么?请帮忙提前致谢
我自己找到了答案。如果将来有人遇到同样的问题,我在这里提供答案。值得注意的是,项目中有 2 个数据库,即一个数据库是主应用程序目录数据库,另一个是输出数据库。当我们开始调试时,主数据库将所有表及其数据复制到输出目录数据库中,通常'bin'Debug'database.mdf
。因此,如果我们在连接字符串中使用|DataDirectory|
,备份将正常工作,但这不是一个好方法,因为通过这样做,我们将所有数据还原到''bin数据库中,该数据库仅用于输出。因此,我们应该提供完整的连接字符串,如下所示:
<connectionStrings>
<add name="SchoolManagement.Properties.Settings.SchoolDatabaseConnectionString"
connectionString="Data Source=.'SQLEXPRESS;AttachDbFilename=G:'Users'ABC'documents'visual studio 2012'Projects'SchoolManagement'SchoolManagement'SchoolDatabase.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
请记住,这是针对 VS 服务基础数据库连接字符串。我们必须将主数据库的完整路径写入备份表单的路径字符串中。见下文:
string path1 = System.IO.Path.GetFullPath(@"G:'Users'ABC'documents'visual studio 2012'Projects'SchoolManagement'SchoolManagement'SchoolDatabase.mdf");
SqlCommand cmd2 = new SqlCommand("backup database [" + path1 + @"] to disk ='D:'Backup_MAConvent'MAConvent_Backup.bak' with init,stats=10", con);
cmd2.ExecuteNonQuery();