需要定期备份多个客户端的数据库,并保存回中心服务器,当需要还原时从服务器上获取备份的数据库脚本进行还原,用C#编写了个数据库备份和还原脚本的方法,基本实现代码如下。
我只备份了表格和存储过程
备份
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using Microsoft.SqlServer.Management.Common;
这三个必不可少,引用这个三个dll
路径一般在Microsoft Visual Studio的安装目录下,我自己的是在:
D:\Program Files (x86)\Microsoft Visual Studio\2019\Preview\Common7\IDE\Extensions\Microsoft\SQLCommon\140\
备份方法
private static string CreateScript(string dbName, ServerConnection serverConnection, bool scriptDrops, bool scriptData, bool scriptSchema, string line)
{
// Connect to the local, default instance of SQL Server.
Server srv = new Server(serverConnection);
// Reference the database.
Database db = srv.Databases[dbName];
// Define a Scripter object and set the required scripting options.
Scripter scrp = new Scripter(srv);
scrp.Options.ScriptDrops = scriptDrops;//是否生成删除脚本
scrp.Options.WithDependencies = true;
scrp.Options.Indexes = true; // To include indexes
scrp.Options.DriAllConstraints = true; // to include referential constraints in the script
scrp.Options.ScriptData = scriptData;//是否生成数据脚本
scrp.Options.ScriptSchema = scriptSchema;//是否生成结构脚本
scrp.Options.DriForeignKeys = true;
//var script = "";
// Iterate through the tables in database and script each one. Display the script.
foreach (Table tb in db.Tables)
{
// check if the table is not a system table
if (!tb.IsSystemObject)
{
if (!((tb.Name.ToUpper().Contains("HIST") || tb.Name.Contains("AppEvents") || tb.Name.Contains("OperationHistory") || tb.Name.Contains("StopFrameEvents")) && scriptData))
{
if (!scriptDrops)
{
if (scriptData)
{
line += "^--“插入表" + tb.Name + "数据”\r\n";
Console.WriteLine("生成表" + tb.Name + "数据脚本" + "\r\n");
}
else
{
line += "^--“创建表" + tb.Name + "”\r\n";
Console.WriteLine( "生成创建表" + tb.Name + "脚本\r\n");
}
}
else
{
line += "^--“删除表" + tb.Name + "”\r\n";
Console.WriteLine("生成删除表" + tb.Name + "脚本\r\n");
}
var scrps = scrp.EnumScript(new Urn[] { tb.Urn });
// Generating script for table tb
foreach (string st in scrps)
{
//Console.WriteLine(st);
if (st.Contains("DROP TABLE"))
{
string ab = "IF EXISTS(Select 1 From Sysobjects Where Name='" + tb.Name + "')";
line += ab + "\r\n";
}
line += st + "\r\n";
}
}
}
}
//导出架构时,才运行导出存储过程代码
if (!scriptData)
{
StoredProcedure sp = db.StoredProcedures["getFault"];
//foreach (StoredProcedure sp in db.StoredProcedures)
//{
if (!sp.IsSystemObject)
{
if (!scriptDrops)
{
line += "^--“创建存储过程" + sp.Name + "”\r\n";
Console.WriteLine( "生成创建存储过程" + sp.Name + "脚本\r\n");
}
else
{
line += "^--“删除存储过程" + sp.Name + "”\r\n";
Console.WriteLine("生成删除存储过程" + sp.Name + "脚本\r\n");
}
foreach (string st in scrp.EnumScript(new Urn[] { sp.Urn }))
{
//Console.WriteLine(st);
if (st.Contains("DROP PROCEDURE"))
{
string ab = "if exists (select * from sysobjects where type = 'P' and name = '" + sp.Name + "')";
line += ab + "\r\n";
}
line += st.Replace("SET ANSI_NULLS ON", "").Replace("SET QUOTED_IDENTIFIER ON", "") + "\r\n";
}
}
//}
}
return line;
}
在Main函数中
string dbName = "zkdata"; // database name
string connectonstring = "Data Source=.;Initial Catalog=xxxx;Integrated Security=false;Pwd=xxxx;User ID=xxxx";
SqlConnection conn = new SqlConnection(connectonstring);
ServerConnection serverConnection = new ServerConnection(conn);
//备份脚本
string line = "";
line = CreateScript(dbName, serverConnection, true,false,true, line);//导出删除表格和存储过程脚本
line = CreateScript(dbName, serverConnection, false,false,true, line);//导出表格结构和存储过程
line = CreateScript(dbName, serverConnection, false, true, false, line);//导出数据
StreamWriter fs = File.CreateText(@"Script.txt");// 运行后在script.txt建在bin目录下
fs.Write(line);
fs.Close();
还原
还原脚本我使用了yueue.ADOKeycap数据库组件,具体使用方法请找度娘
还原方法
static yueue.ADOKeycap.Database dbsys = MyDatabase.GetData();
public static bool ExecuteScript(string script)
{
try
{
int i = dbsys.ExecuteNonQuery(script);
return true;
}
catch(Exception ex)
{
Console.WriteLine(ex);
return false;
}
}
在Main函数中
//还原脚本
string scriptStr = File.ReadAllText(@"Script.txt", Encoding.UTF8);//
string[] scripts = scriptStr.Split('^');
foreach (string script in scripts)
{
if (script.Length > 0)
{
int startIndex = script.IndexOf('“');
int endIndex = script.IndexOf('”');
string tip = script.Substring(startIndex + 1, endIndex - startIndex - 1);
Console.WriteLine(tip);
string script1 = script.Remove(startIndex - 2, endIndex + 3);
if (script1.Length > 0)
{
bool b = ExecuteScript(script1);
if (b)
{
Console.WriteLine("执行成功");
}
else
{
Console.WriteLine("执行失败");
}
}
}
}