کلاس BackupAssistant
public class BackupAssistant
{
string server = "", database = "", user = "", pwd = "";
public string ServerName
{
set { server = value; }
get { return server; }
}
public string DatabaseName
{
set { database = value; }
get { return database; }
}
public string UserName
{
set { user = value; }
get { return user; }
}
public string Password
{
set { pwd = value; }
get { return pwd; }
}
public void BackupDatabase(string destinationPath)
{
System.Globalization.PersianCalendar p = new System.Globalization.PersianCalendar();
string Name = "Archive_" + string.Format("{0}-{1}-{2}",
p.GetYear(DateTime.Now).ToString(),
p.GetMonth(DateTime.Now).ToString("0#"),
p.GetDayOfMonth(DateTime.Now).ToString("0#")) + "_" +
string.Format("{0}-{1}-{2}", DateTime.Now.Hour, DateTime.Now.Minute, DateTime.Now.Second);
KillAllConnections();
SqlConnection con = new SqlConnection("Server=" + ServerName + "; User=" + UserName + "; Pwd=" + Password + ";");
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
if (con.State == System.Data.ConnectionState.Closed) con.Open();
cmd.CommandText = "BACKUP DATABASE [" + DatabaseName + "] TO DISK = N'" + destinationPath + "\\" + Name + ".sqlbk" + "' WITH FORMAT, INIT, NAME = N'" + Name + "', SKIP, NOREWIND, NOUNLOAD, STATS = 10";
cmd.ExecuteNonQuery();
cmd.CommandText = " BACKUP log [" + DatabaseName + "] TO DISK = N'" + destinationPath + "\\" + Name + ".sqllog" + "' WITH FORMAT, INIT, NAME = N'" + Name + "', SKIP, NOREWIND, NOUNLOAD, STATS = 10";
cmd.ExecuteNonQuery();
con.Close();
}
private void KillAllConnections()
{
object id;
SqlConnection con = new SqlConnection("Server=" + ServerName + "; User=" + UserName + "; Pwd=" + Password + ";");
SqlCommand cmd = new SqlCommand("SELECT DB_ID('" + database + "') as [Database ID]", con);
if (con.State == System.Data.ConnectionState.Closed) con.Open();
id = cmd.ExecuteScalar();
cmd.CommandText = "DECLARE SpidsToKill CURSOR FOR SELECT spid FROM master..sysprocesses WHERE dbid = @dbid" +
" DECLARE @Spid int DECLARE @SQL varchar(255) " +
"OPEN SpidsToKill FETCH NEXT FROM SpidsToKill INTO @Spid WHILE ( @@FETCH_STATUS = 0 ) " +
"BEGIN SELECT @SQL = 'KILL ' + CONVERT( varchar(10), @Spid ) EXEC ( @SQL ) FETCH NEXT FROM SpidsToKill INTO @Spid END " +
"CLOSE SpidsToKill DEALLOCATE SpidsToKill";
cmd.Parameters.AddWithValue("@dbid", id);
cmd.ExecuteNonQuery();
con.Close();
}
}
کلاس Restore Assistant شامل همان تعاریف بالاست. فقط بجای تابع BackupDatabse تابع زیر جایگزین شده:
public void RestoreDatabase(string MdfPath, string LdfPath)
{
KillAllConnections();
SqlConnection con = new SqlConnection("Server=" + ServerName + "; User=" + UserName + "; Pwd=" + Password + ";");
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
if (con.State == System.Data.ConnectionState.Closed) con.Open();
cmd.CommandText = " BACKUP log [" + DatabaseName + "] TO DISK = N'" + LdfPath + "' WITH FORMAT, INIT, NAME = N'" + LdfPath.Trim(".sqllog".ToCharArray()) + "', SKIP, NOREWIND, NOUNLOAD, STATS = 10";
cmd.ExecuteNonQuery();
cmd.CommandText = "RESTORE DATABASE [" + DatabaseName + "] FROM DISK = N'" + MdfPath + "' WITH REPLACE";
cmd.ExecuteNonQuery();
con.Close();
}
برای گرفتن backup :
try
{
BackupRestoreAssistant.BackupAssistant bk = new BackupRestoreAssistant.BackupAssistant();
bk.DatabaseName = database;
bk.ServerName = server;
bk.UserName = user;
bk.Password = pwd;
bk.BackupDatabase(txt_directory.Text);
MessageBox.Show("پشتیبان ایجاد شد", "Backup", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Backup", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
برای restore کردن :
try
{
BackupRestoreAssistant.RestoreAssistant bk = new BackupRestoreAssistant.RestoreAssistant();
bk.DatabaseName = database;
bk.ServerName = server;
bk.UserName = user;
bk.Password = pwd;
bk.RestoreDatabase(txt_path1.Text,txt_path2.Text);
MessageBox.Show("بازگردانی انجام شد", "Restore", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, " Restore ", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
