C# sqlserver数据库备份脚本

需要定期备份多个客户端的数据库，并保存回中心服务器，当需要还原时从服务器上获取备份的数据库脚本进行还原，用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("执行失败");
	        }
	    }
    }

}