How to remotely back up a SQL Server database

server| Backup | data | database

Method One (does not use SQLDMO):

///
Backup methods
///
SqlConnection conn = new SqlConnection ("server=.;D Atabase=master; User Id=sa; Password=sa; ");

SqlCommand CMDBK = new SqlCommand ();
Cmdbk.commandtype = CommandType.Text;
Cmdbk.connection = conn;
Cmdbk.commandtext = @ "BACKUP database test to disk= ' C:\ba ' with Init";

Try
{
Conn. Open ();
Cmdbk.executenonquery ();
MessageBox.Show ("Backup successed.");
}
catch (Exception ex)
{
MessageBox.Show (ex. message);
}
Finally
{
Conn. Close ();
Conn. Dispose ();
}

///
Restore method
///
SqlConnection conn = new SqlConnection ("server=.;D Atabase=master; User Id=sa; Password=sa; Trusted_connection=false ");
Conn. Open ();

KILL DataBase Process
SqlCommand cmd = new SqlCommand ("Select spid from sysprocesses, sysdatabases WHERE sysprocesses.dbid=sysdatabases.dbid D sysdatabases. Name= ' Test ', conn);
SqlDataReader Dr;
Dr = cmd. ExecuteReader ();
ArrayList list = new ArrayList ();
while (Dr. Read ())
{
List. ADD (Dr. GetInt16 (0));
}
Dr. Close ();
for (int i = 0; i < list. Count; i++)
{
cmd = new SqlCommand (string. Format ("KILL {0}", List[i]), conn);
Cmd. ExecuteNonQuery ();
}

SqlCommand CMDRT = new SqlCommand ();
Cmdrt.commandtype = CommandType.Text;
Cmdrt.connection = conn;
Cmdrt.commandtext = @ "RESTORE database test from disk= ' C:\ba '";

Try
{
Cmdrt.executenonquery ();
MessageBox.Show ("Restore successed.");
}
catch (Exception ex)
{
MessageBox.Show (ex. message);
}
Finally
{
Conn. Close ();
}

Method two (using SQLDMO):

///
Backup methods
///
SQLDMO. Backup backup = new SQLDMO. Backupclass ();
SQLDMO. SQL Server server = new SQLDMO. Sqlserverclass ();
Show progress bar
SQLDMO. Backupsink_percentcompleteeventhandler progress = new SQLDMO. Backupsink_percentcompleteeventhandler (step);
Backup. PercentComplete + = progress;

Try
{
Server. LoginSecure = false;
Server. Connect (".", "sa", "sa");
Backup. Action = SQLDMO. Sqldmo_backup_type. Sqldmobackup_database;
Backup. Database = "Test";
Backup. Files = @ "D:\test\myProg\backupTest";
Backup. BackupSetName = "Test";
Backup. BackupSetDescription = "Backup The database of test";
Backup. Initialize = true;
Backup. SQLBackup (server);
MessageBox.Show ("Backup successed.");
}
catch (Exception ex)
{
MessageBox.Show (ex. message);
}
Finally
{
Server. DisConnect ();
}
This.pbDB.Value = 0;

///
Restore method
///
SQLDMO. Restore restore = new SQLDMO. Restoreclass ();
SQLDMO. SQL Server server = new SQLDMO. Sqlserverclass ();
Show progress bar
SQLDMO. Restoresink_percentcompleteeventhandler progress = new SQLDMO. Restoresink_percentcompleteeventhandler (step);
Restore. PercentComplete + = progress;

KILL DataBase Process
SqlConnection conn = new SqlConnection ("server=.;D Atabase=master; User Id=sa; Password=sa; Trusted_connection=false ");
Conn. Open ();
SqlCommand cmd = new SqlCommand ("Select spid from sysprocesses, sysdatabases WHERE sysprocesses.dbid=sysdatabases.dbid D sysdatabases. Name= ' Test ', conn);
SqlDataReader Dr;
Dr = cmd. ExecuteReader ();
ArrayList list = new ArrayList ();
while (Dr. Read ())
{
List. ADD (Dr. GetInt16 (0));
}
Dr. Close ();
for (int i = 0; i < list. Count; i++)
{
cmd = new SqlCommand (string. Format ("KILL {0}", List[i]), conn);
Cmd. ExecuteNonQuery ();
}
Conn. Close ();

Try
{
Server. LoginSecure = false;
Server. Connect (".", "sa", "sa");
Restore. Action = SQLDMO. Sqldmo_restore_type. Sqldmorestore_database;
Restore. Database = "Test";
Restore. Files = @ "D:\test\myProg\backupTest";
Restore. FileNumber = 1;
Restore. ReplaceDatabase = true;
Restore. SQLRestore (server);
MessageBox.Show ("Restore successed.");
}
catch (Exception ex)
{
MessageBox.Show (ex. message);
}
Finally
{
Server. DisConnect ();
}
This.pbDB.Value = 0;