如何将SQLSERVER数据库备份到多个文件

Question

如题所述

Answer 1

/*
--用BCP试试,不行再用下面的存储过程
EXEC master..xp_cmdshell 'bcp "select * from test.dbo.Apo_village"
queryout "c:/Apo_SFZ.xlsx" -c -S"服务器" -U"sa" -P"密码"'
*/

--这是用C#写的存储过程,不知道你会不会编译到SQL Server
--在数据库这样调用就是了
--Exec BulkCopyToXls 'SQL查询语句','路径','文件名',最大记录数
--Exec BulkCopyToXls 'select * from 表','G:\Test','Table',60000

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class myProcedure
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void BulkCopyToXls(SqlString sql, SqlString savePath, SqlString tableName, SqlInt32 maxRecordCount)
    {
        if (sql.IsNull || savePath.IsNull || tableName.IsNull)
        {
            SqlContext.Pipe.Send(" è¾“入信息不完整！");
        }
        //每个excel文件最大容纳65534
        ushort _maxRecordCount = ushort.MaxValue - 1;
        if (maxRecordCount.IsNull == false && maxRecordCount.Value < ushort.MaxValue && maxRecordCount.Value > 0)
            _maxRecordCount = (ushort)maxRecordCount.Value;
        ExportXls(sql.Value, savePath.Value, tableName.Value, _maxRecordCount);
    }
    private static void ExportXls(string sql, string savePath, string tableName, System.UInt16 maxRecordCount)
    {
        //创建文件路径
        if (System.IO.Directory.Exists(savePath) == false)
        {
            System.IO.Directory.CreateDirectory(savePath);
        }
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            conn.Open();
            using (SqlCommand command = conn.CreateCommand())
            {
                command.CommandText = sql;
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    int i = 0;
                    int totalCount = 0;
                    int tick = System.Environment.TickCount;
                    SqlContext.Pipe.Send(" å¼€å§‹å¯¼å‡ºæ•°æ®");
                    while (true)
                    {
                        string fileName = string.Format(@"{0}/{1}.{2}.xls", savePath, tableName, i++);
                        int iExp = Write(reader, maxRecordCount, fileName);
                        long size = new System.IO.FileInfo(fileName).Length;
                        totalCount += iExp;
                        SqlContext.Pipe.Send(string.Format(" æ–‡ä»¶{0}, å…±{1} æ¡, å¤§å°{2} å­—节", fileName, iExp, size.ToString("###,###")));
                        if (iExp < maxRecordCount) break;
                    }
                    tick = System.Environment.TickCount - tick;
                    SqlContext.Pipe.Send(" å¯¼å‡ºæ•°æ®å®Œæˆ");
                    SqlContext.Pipe.Send("-------");
                    SqlContext.Pipe.Send(string.Format(" å…±{0} æ¡æ•°æ®ï¼Œè€—æ—¶{1}ms", totalCount, tick));
                }
            }
        }
    }  private static void WriteObject(ExcelWriter writer, object obj, System.UInt16 x, System.UInt16 y)
    {
        //判断写数字还是写字符
        string type = obj.GetType().Name.ToString();
        switch (type)
        {
            case "SqlBoolean":
            case "SqlByte":
            case "SqlDecimal":
            case "SqlDouble":
            case "SqlInt16":
            case "SqlInt32":
            case "SqlInt64":
            case "SqlMoney":
            case "SqlSingle":
                if (obj.ToString().ToLower() == "null")
                    writer.WriteString(x, y, obj.ToString());
                else
                    writer.WriteNumber(x, y, Convert.ToDouble(obj.ToString()));
                break;
            default:
                writer.WriteString(x, y, obj.ToString());
                break;
        }
    }
   
    private static int Write(SqlDataReader reader, System.UInt16 count, string fileName)
    {
        int iExp = count;
        ExcelWriter writer = new ExcelWriter(fileName);
        writer.BeginWrite();
        //写字段信息
        for (System.UInt16 j = 0; j < reader.FieldCount; j++)
        {
            writer.WriteString(0, j, reader.GetName(j));
        }
        //循环一行一行读入数据
        for (System.UInt16 i = 1; i <= count; i++)
        {
            if (reader.Read() == false)
            {
                iExp = i - 1;
                break;
            }
            //循环一格一格写入数据
            for (System.UInt16 j = 0; j < reader.FieldCount; j++)
            {
                WriteObject(writer, reader.GetSqlValue(j), i, j);
            }
        }
        writer.EndWrite();
        return iExp;
    }
   
    public class ExcelWriter
    {
        System.IO.FileStream _wirter;
        //创建文件
        public ExcelWriter(string strPath)
        {
            _wirter = new System.IO.FileStream(strPath, System.IO.FileMode.OpenOrCreate);
        }
        
        //写数组
        private void _writeFile(System.UInt16[] values)
        {
            foreach (System.UInt16 v in values)
            {
                byte[] b = System.BitConverter.GetBytes(v);
                _wirter.Write(b, 0, b.Length);
            }
        }
       
        //写文件头
        public void BeginWrite()
        {
            _writeFile(new System.UInt16[] { 0x809, 8, 0, 0x10, 0, 0 });
        }
       //文件尾
        public void EndWrite()
        {
            _writeFile(new System.UInt16[] { 0xa, 0 });
            _wirter.Close();
        }
       //写数字到单元格
        public void WriteNumber(System.UInt16 x, System.UInt16 y, double value)
        {
            _writeFile(new System.UInt16[] { 0x203, 14, x, y, 0 });
            byte[] b = System.BitConverter.GetBytes(value);
            _wirter.Write(b, 0, b.Length);
        }
       //写字符到单元格
        public void WriteString(System.UInt16 x, System.UInt16 y, string value)
        {
            byte[] b = System.Text.Encoding.Default.GetBytes(value);
            _writeFile(new System.UInt16[] { 0x204, (System.UInt16)(b.Length + 8), x, y, 0, (System.UInt16)b.Length });
            _wirter.Write(b, 0, b.Length);
        }
    }
};