从网络服务器备份 [英] BackUp From Network Server

问题描述

如何使用c#&从网络服务器进行数据库备份smo ???
有可能吗?
我确实从本地服务器备份，但无法从网络服务器(LAN)备份. 请帮助我..提前谢谢..
请帮助我任何人..

How to take database backup from a Network Server using c# & smo???
Is it possible??
I do backup from my Local Server But Could not From Network Server(LAN)..
Plz Help me..Thanx In advance..
plz help me anyone..

推荐答案

您可以使用此类进行此操作.我在我的项目中使用了它，并得到了预期的答案.

公共类SQLLocalBackup
{
受保护的SqlConnection _conn;
受保护的字符串_address;
受保护的字符串_user;
受保护的字符串_pass;
受保护的字符串_dbname;
公共SQLLocalBackup(字符串地址，字符串Auser，字符串Apass，字符串Adatabasename)
{
试试
{
_conn = new SqlConnection(String.Format(数据源= {0};初始目录= {1};用户ID = {2};密码= {3};"，地址，数据库名称，用户，通过));
_conn.Open();
_address =地址；
_user = Auser;
_pass = Apass;
_dbname =数据库名称;
}
catch(ex ex例外)
{

}
}
///< summary>
//////此函数检查临时表，因为我们不想干扰其他程序/函数
///</summary>
///< returns></returns>
受保护的字符串findUniqueTemporaryTableName()
{
字符串名称="afpTempBackup";
int计数器= 0;
字符串sql =";
SqlCommand _mycommand =新的SqlCommand();
_mycommand.Connection = _conn;
而(true)
{
++计数器；
sql = String.Format("SELECT OBJECT_ID(``tempdb .. ## {0}'')as xyz"，name + counter.ToString());
_mycommand.CommandText = sql;
如果(_mycommand.ExecuteScalar().ToString()==")
{
返回名称+ counter.ToString();
休息；
}
}

返回名称；
}
///< summary>
///主要备份功能
///</summary>
///< param name ="AremoteTempPath">您可以指定要为备份设置的文件夹</param>
///< param name ="AlocalPath">备份文件副本的本地路径</param>
///< param name ="AtempTableName">指定临时表名称，这样您就不会与其他程序发生冲突</param>
公共无效DoLocalBackup(字符串AremoteTempPath，字符串AlocalPath)
{
试试
{
如果(_conn == null)
返回；
SqlCommand _command =新的SqlCommand();
_command.Connection = _conn;
//本地文件名不错，因此我们知道何时完成备份
//string fileName = _dbname + DateTime.Now.Year.ToString()+-" +
//DateTime.Now.Month.ToString()+-" +
//DateTime.Now.Day.ToString()+-" +
//DateTime.Now.Millisecond.ToString()+".bak";

字符串fileName ="DBName" + DateTime.Now.Year.ToString()+-" +
DateTime.Now.Month.ToString()+-" +
DateTime.Now.Day.ToString()+-" +
DateTime.Now.Hour.ToString()+-" +
DateTime.Now.Minute.ToString()+-" +
DateTime.Now.Second.ToString()+".bak";

////调用此方法以确保不与其他程序混淆
字符串临时表名= findUniqueTemporaryTableName();

字符串_sql;

_sql = String.Format(备份数据库{0}到磁盘= N''{1} \\ {0} .bak''带格式，COPY_ONLY，INIT，NAME = N''{0}-完整数据库备份' '，SKIP，_dbname，AremoteTempPath，_dbname);
_command.CommandText = _sql;
_command.ExecuteNonQuery();
_sql = String.Format("IF OBJECT_ID(''tempdb .. ## {0}'')IS NOT NULL DROP TABLE ## {0}"，temporaryTableName);
_command.CommandText = _sql;
_command.ExecuteNonQuery();
_sql = String.Format(创建表## {0}(bck VARBINARY(MAX))"，临时表名);
_command.CommandText = _sql;
_command.ExecuteNonQuery();
_sql = String.Format("INSERT INTO ## {0} SELECT bck.* FROM OPENROWSET(BULK''{1} \\ {2} .bak''，SINGLE_BLOB)bck"，临时表名，AremoteTempPath，_dbname);
_command.CommandText = _sql;
_command.ExecuteNonQuery();
_sql = String.Format("SELECT bck FROM ## {0}"，临时表名);
SqlDataAdapter da =新的SqlDataAdapter(_sql，_conn);
DataSet ds = new DataSet();
da.Fill(ds);
DataRow dr = ds.Tables [0] .Rows [0];
byte [] backupFromServer =新的byte [0];
backupFromServer =(byte [])dr ["bck"];
int aSize = new int();
aSize = backupFromServer.GetUpperBound(0)+ 1;

FileStream fs = new FileStream(String.Format("{0} \\ {1}"，AlocalPath，fileName)，FileMode.OpenOrCreate，FileAccess.Write);
fs.Write(backupFromServer，0，aSize);
fs.Close();

_sql = String.Format("DROP TABLE ## {0}"，temporaryTableName);
_command.CommandText = _sql;
_command.ExecuteNonQuery();
}
catch(ex ex例外)
{

}
}
}

Hi You can use this class to do this. I am using this in my project and got the expected answer .

public class SQLLocalBackup
{
protected SqlConnection _conn;
protected string _address;
protected string _user;
protected string _pass;
protected string _dbname;
public SQLLocalBackup(string Aaddress, string Auser, string Apass, string Adatabasename)
{
try
{
_conn = new SqlConnection(String.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3};", Aaddress, Adatabasename, Auser, Apass));
_conn.Open();
_address = Aaddress;
_user = Auser;
_pass = Apass;
_dbname = Adatabasename;
}
catch (Exception ex)
{

}
}
/// <summary>
/// /// This function checks for temporary tables since we don''t want to interfere with other programs/functions
/// </summary>
/// <returns></returns>
protected string findUniqueTemporaryTableName()
{
string name = "afpTempBackup";
int counter = 0;
string sql = "";
SqlCommand _mycommand = new SqlCommand();
_mycommand.Connection = _conn;
while (true)
{
++counter;
sql = String.Format("SELECT OBJECT_ID(''tempdb..##{0}'') as xyz", name + counter.ToString());
_mycommand.CommandText = sql;
if (_mycommand.ExecuteScalar().ToString() == "")
{
return name + counter.ToString();
break;
}
}

return name;
}
/// <summary>
/// Main backuping function
/// </summary>
/// <param name="AremoteTempPath">You can specify what folder do you wish to be set for your backup</param>
/// <param name="AlocalPath">Local path where copy of your backup file</param>
/// <param name="AtempTableName">Specify temp table name so you wont collide with other programs</param>
public void DoLocalBackup(string AremoteTempPath, string AlocalPath)
{
try
{
if (_conn == null)
return;
SqlCommand _command = new SqlCommand();
_command.Connection = _conn;
// nice filename on local side, so we know when backup was done
//string fileName = _dbname + DateTime.Now.Year.ToString() + "-" +
// DateTime.Now.Month.ToString() + "-" +
// DateTime.Now.Day.ToString() + "-" +
// DateTime.Now.Millisecond.ToString() + ".bak";

string fileName ="DBName" + DateTime.Now.Year.ToString() + "-" +
DateTime.Now.Month.ToString() + "-" +
DateTime.Now.Day.ToString() + "-" +
DateTime.Now.Hour.ToString() + "-" +
DateTime.Now.Minute.ToString() + "-" +
DateTime.Now.Second.ToString() + ".bak";

// we invoke this method to ensure we didnt mess up with other programs
string temporaryTableName = findUniqueTemporaryTableName();

string _sql;

_sql = String.Format("BACKUP DATABASE {0} TO DISK = N''{1}\\{0}.bak'' WITH FORMAT, COPY_ONLY, INIT, NAME = N''{0} - Full Database Backup'', SKIP ", _dbname, AremoteTempPath, _dbname);
_command.CommandText = _sql;
_command.ExecuteNonQuery();
_sql = String.Format("IF OBJECT_ID(''tempdb..##{0}'') IS NOT NULL DROP TABLE ##{0}", temporaryTableName);
_command.CommandText = _sql;
_command.ExecuteNonQuery();
_sql = String.Format("CREATE TABLE ##{0} (bck VARBINARY(MAX))", temporaryTableName);
_command.CommandText = _sql;
_command.ExecuteNonQuery();
_sql = String.Format("INSERT INTO ##{0} SELECT bck.* FROM OPENROWSET(BULK ''{1}\\{2}.bak'',SINGLE_BLOB) bck", temporaryTableName, AremoteTempPath, _dbname);
_command.CommandText = _sql;
_command.ExecuteNonQuery();
_sql = String.Format("SELECT bck FROM ##{0}", temporaryTableName);
SqlDataAdapter da = new SqlDataAdapter(_sql, _conn);
DataSet ds = new DataSet();
da.Fill(ds);
DataRow dr = ds.Tables[0].Rows[0];
byte[] backupFromServer = new byte[0];
backupFromServer = (byte[])dr["bck"];
int aSize = new int();
aSize = backupFromServer.GetUpperBound(0) + 1;

FileStream fs = new FileStream(String.Format("{0}\\{1}", AlocalPath, fileName), FileMode.OpenOrCreate, FileAccess.Write);
fs.Write(backupFromServer, 0, aSize);
fs.Close();

_sql = String.Format("DROP TABLE ##{0}", temporaryTableName);
_command.CommandText = _sql;
_command.ExecuteNonQuery();
}
catch (Exception ex)
{

}
}
}

这篇关于从网络服务器备份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！