远程备份数据库并自动下载备份文件

 

    class Program
    {   
        static Thread thread;
        static string guid;
        static string dirDate;

        static void Main(string[] args)
        {
            Console.CursorVisible = false;

            string _dirDate = DateTime.Now.ToShortDateString();
             dirDate = _dirDate.Replace(".", "");
            string _localTargetDir = System.Configuration.ConfigurationManager.AppSettings["_localTargetDir"];
            if (_localTargetDir == "")
            {
                _localTargetDir = Directory.GetCurrentDirectory();
            }
            if(!_localTargetDir.EndsWith("\\"))_localTargetDir+="\\";

            string localTargetDir = _localTargetDir ;
            if (!Directory.Exists(localTargetDir)) Directory.CreateDirectory(localTargetDir);

            string SvrBakDir = @"e:\gzyexam\";
             guid = Guid.NewGuid().ToString("P");

            string _dbNames = System.Configuration.ConfigurationManager.AppSettings["_dbNames"];

            string[] dbNames = _dbNames.Split(',');

            foreach (var item in dbNames)
            {
                Console.WriteLine("正在清理“" + item + "”数据库…");
                string deleteDataBeforBackUp=System.Configuration.ConfigurationManager.AppSettings["deleteDataBeforBackUp"];
                Run("sqlcmd -S 192.168.0.9\\sqlexpress -d "+item+" -U sa -P gzy123#$% -Q \""+deleteDataBeforBackUp+"\"");
                Console.WriteLine("正在备份“" + item + "”数据库…");
                string fullBakFileName = SvrBakDir + item + guid + ".bak";
                BackUpDataBase(item, fullBakFileName);
                while (true)
                {
                    if (File.Exists(@"\\192.168.0.9\gzyexam\" + item + guid + ".bak"))
                    {
                        Thread.Sleep(1000);
                        GetFilesFromShare(@"\\192.168.0.9\gzyexam\", localTargetDir, item + guid + ".bak");
                        break;
                    }
                }

                ////修改本地文件名   把 GUID 去掉 ，服务器有GUID是防止冲突
                //string localFileName=localTargetDir+"\\"+item + guid + ".bak";
                //if(File.Exists(localFileName))
                //{
                //    FileInfo f_info = new FileInfo(localFileName);
                //    f_info.Attributes.
                //}
            }
            
      
            Console.CursorVisible = true;
            Console.WriteLine("Done");
            Console.Read();
        }

        static void BackUpDataBase(string dbName, string fullBakFileName)
        {
           
            string sql = "backup database " + dbName + " to disk='"+ fullBakFileName+ "'";
            Run("sqlcmd -S 192.168.0.9\\sqlexpress -U sa -P gzy123#$% -Q \"" + sql + "\"");
        }

        static string[] GetLogicalName(string dbName, SqlConnection con)
        {
            List<string> res=new List<string>();
            SqlCommand cmd = new SqlCommand("select " + dbName + " from sys.databases", con);
            using (con)
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    res.Add( (string)reader["name"]);
                    reader.Read();
                    res.Add((string)reader["name"]);
                }
            }
            return res.ToArray();
        }
             

        #region 下载文件
        
     

        static void GetFilesFromShare(string shareLocation, string TargetDir, string searchPattern="*", SearchOption searchOption=SearchOption.TopDirectoryOnly)
        {

            string[] dd = Directory.GetFiles(shareLocation, searchPattern, searchOption);
            foreach (var item in dd)
            {
                string name = Path.GetFileName(item).Replace(guid,"");
                string tFilename = TargetDir + "\\" + name;
                DateTime startTime = DateTime.Now;
                CopyAsync(item, tFilename);
                float FileSize = GetLength(item) / 1024F / 1024;
                Console.WriteLine("正在下载…");
                Console.WriteLine("正在将“{0}”文件拷贝到“{1}”", item, tFilename);
                Console.WriteLine("目标文件当前大小：{0}MB", FileSize);
                int rowNum = 1;
                while (true)
                {
                    int CursorTop = Console.CursorTop;
                    Thread.Sleep(100);
                    if (thread != null && thread.ThreadState == System.Threading.ThreadState.Running)
                    {
                        NetworkInterface[] ni = NetworkInterface.GetAllNetworkInterfaces();
                        foreach (NetworkInterface networkInterface in ni)
                        {
                            if (networkInterface.NetworkInterfaceType == NetworkInterfaceType.Ethernet)
                            {
                                Console.WriteLine("当前拷贝速度：{0}MByte/s", networkInterface.Speed / 8F / 1024F / 1024);
                                double percent = ((networkInterface.Speed / 8F / 1024F / 1024) * (DateTime.Now - startTime).TotalSeconds) / FileSize;
                                Console.WriteLine("当前拷贝进度：{0}", percent < 1 ? percent.ToString("p2") : "100.00%");
                                rowNum = Console.CursorTop - CursorTop + 1;
                                Console.SetCursorPosition(0, CursorTop);
                            }
                        }
                    }
                    else if (thread != null && thread.ThreadState == System.Threading.ThreadState.Stopped)
                    {
                        Console.SetCursorPosition(0, CursorTop + rowNum);
                        break;
                    }
                }

            }
        }

        static string s;
        static string t;
        static void CopyAsync(string ss, string tt)
        {
            s=ss;
            t=tt;
            ThreadStart ts=C;
             thread = new Thread(ts);
            thread.IsBackground = false;
            thread.Start();
            
        }
        static void C()
        {
            File.Copy(s, t,true);
        }
        static long GetLength(string filenmae)
        {
            FileInfo finfo = new FileInfo(filenmae);

            return finfo.Length;
        }

        #endregion

        static void Run(string arg, string filename = "cmd.exe", bool Hidden = false, bool asyn = false)
        {
            Process p = new Process();
            if (Hidden)
            {
                p.StartInfo.CreateNoWindow = true;
            }
            if (filename != "cmd.exe")
            {
                p.StartInfo.UseShellExecute = false;
            }
            p.StartInfo.FileName = filename;
            p.StartInfo.Arguments = " /c " + arg;
            p.Start();
            if (!asyn)
            {
                p.WaitForExit();
                p.Close();
            }

        }

        #region 备份文件


        #endregion
    }

利用vs2010的打包项目，添加生成后事件：

del $(ProjectDir)$(Configuration)\bak\gzyExam_s.bak\r\n
del $(ProjectDir)$(Configuration)\bak\gzyExam_s_c.bak\r\n
start /w $(ProjectDir)备份数据库并从服务器获取文件.exe

这样每次生成部署包的时候自动从服务器下载数据库备份文件到部署包。

这些所有的操作我在本地是成功的。

 

 

但有一些前提：

数据库服务器上必须建立共享目录

生成的部署包必须是分散的文件，而不是一个cab压缩包。

数据库Bak文件存放的目标目录需要配置刚好 ，之所以是生成后事件而不是生成前事件，是因为生成后才有对应的文件夹