SQLserver备份还原系统实现

SQLserver备份还原系统

概述

为什么需要网站来实现对数据库的备份还原，理由如下：

• 可以远程备份还原，上传自己本地备份进行还原，方便管理
• 有些linux电脑安装了SQLserver数据库，还原没有本地客户端支持，只能敲命令
• 支持编写自定义功能，扩展本地客户端功能。

---

初步设计

1、程序建表问题，自动创建表格，可以采用自己编写注解的方式进行，类似hibernate注解，启动程序的时候解析bean的注解并进行生成表格。
2、程序界面采用一款好看的主题inspinia_admin进行二次扩展。
3、程序后台架构采用SpringBoot + SpringMVC + Spring + Mybatis
4、程序前台架构除了采用主题，扩展了几个常用插件jquery.form.js【异步提交form】+jquery loading【等待提示】+jquery confirm【确认弹框插件】等常用的前台组件

部分界面截图

登录界面

主界面

响应式网站所以手机端访问也兼容，下面是移动端浏览器截图

移动端登录

移动端主界面

部分关键代码

整体架构　：

自定义注解实现自动建表，这里以前有详细讲述ssm 自定义注解实现mybatis自动维护表结构以及利用freemarker生成代码
编写自定义注解类

//表示注解加在接口、类、枚举等@Target(ElementType.TYPE)//VM将在运行期也保留注释，因此可以通过反射机制读取注解的信息@Retention(RetentionPolicy.RUNTIME)//将此注解包含在javadoc中@Documented//允许子类继承父类中的注解@Inheritedpublic @interface AutoCode {    /**     * 是否可以覆盖原先自动生成的文件     * 默认不覆盖已有的文件     * @return     */    public abstract boolean isOverride() default false;    /**     * 默认false     * 是否包含pojo对象的父类，这里只支持一层继承关系     * @return     */    public abstract boolean includeSupperClass() default false;}

//该注解用于方法声明@Target(ElementType.FIELD)//VM将在运行期也保留注释，因此可以通过反射机制读取注解的信息@Retention(RetentionPolicy.RUNTIME)//将此注解包含在javadoc中@Documented//允许子类继承父类中的注解@Inheritedpublic @interface Column {    /**     * 默认当前字段名。     * @return     */    public abstract String name() default "";    /**     * 默认值是normal     * 仅可以填写【primary,normal】其他无效。     * 可以多个字段都是primary做联合主键。     * @return     */    public abstract String flag() default "normal";    /**     * 默认值是varchar(50)     * 【varchar(50),decimal(18,2),int,smallint,...】     * 字段类型，支持SQLSERVER数据库类型     * @return     */    public abstract String type() default "varchar(50)";    /**     * 字段默认值     * 支持 newid()方法     * 支持变量如下【@hbdwbh,@dwbh,@year,@month】     * @return     */    public abstract String defaultValue() default "";    /**     * 【identity(1,1),NOT NULL,NULL】     * 自增，非空，空，默认空值     * 默认不写表示primary则是NOT NULL normal是NULL 如果是normal 想要NOT NULL 可以加OTH="NOT NULL"     * @return     */    public abstract String oth() default "";    //=====================================JS 设定部分===========================================//    /**     * 生成js model时候的数据类型，如果不指定，他会根据type来判断类型     * 例如boolean 数据库没有这个类型，而js需要，所以单独出来。但是如果跟数据库一致，则不需要配置     * 默认取type()值。     * @return     */    public abstract String jstype() default "";    /**     * 生成grid 或者tree 的列名称 如果为空则不显示在页面上     * @return     */    public abstract String jsname() default "";    /**     * 设置列在 js grid 里面的宽度     * 默认所有列1:1平均分。     * @return     */    public abstract int jswidth() default 0;    /**     * 是否允许为空     * 默认允许为空     * @return     */    public abstract boolean jsAllowBlank() default true;    /**     * 主页面隐藏该列，但增加修改页面会显示出来。如果都不要这个列显示，只需要把jsname=''即可。     * 默认显示(false)     * @return     */    public abstract boolean jshidden() default false;    /**     * 当其为空则会自动获取jstype来判断，当jstype为空时候会自动获取type进行判断     * 这里针对增加修改的展示的 xtype进行定义特殊的组件例如 radioGroup,checkBox等不能用     * 数据库字段类型或者jstype类型判断的类型进行定义。     * e.g.  radiogroup [部分配置项 例如"items:[{                        inputValue: 'visa',                        name : 'xxx',                        boxLabel: 'VISA',                        checked: true                    }, {                        inputValue: 'mastercard',                        name : 'xxx',                        boxLabel: 'MasterCard'                    }]]     * @return     */    public abstract String jsxtype() default "";    //=================================tree ============================//    /**     * 如果是树的话需要指定主列     * @return     */    public abstract boolean treecolumn() default false;    /**     * 标识该属性为tree id     * 与treeparentid对应     * @return     */    public abstract boolean treeId() default false;    /**     * 标识树父节点是哪个字段     * @return     */    public abstract boolean treeparentId() default false;    /**     * 要求数据库类型和属性类型必须为int。     * @return     */    public abstract boolean treeleaf() default false;    /**     * 对树起作用，会自动汇总子节点的数值在增删改子节点的时候。     * @return     */    public abstract boolean treeSum() default false;    /**     * e.g.  function(value,cellmeta,record,row,col,store){if(value=='1'){return '支出类型'}else {return '收入类型'}}     * 与extjs renderer 编写的方法内容一致。采取直接替换。     * @return     */    public abstract String render() default "";    /**     * e.g.  where hbdwbh = ? and dwbh = ? and keyid = value      * 只过滤 hbdwbh,dwbh,以及设定的当前列。     * 对于不分年的表，若要对里面的年份也进行过滤，需要自己修改代码。     * @return     */    public abstract boolean jsValidator() default false;    /**     * e.g.  1  101  10101  102 10201      * 自动根据数据库记录按上方规则生成主键代码。     * 只能放置于 treeId 是true 的列     * @return     */    public abstract boolean autoGenneral() default false;}

我们只要在bean里写上注解即可。

@Table(name = "SIQ_DatabaseBackupVersion")public class DBBackupInfoBean implements Serializable {    /**     * UID     */    private static final long serialVersionUID = -8313977153833723277L;    @Column    private String dvid;    @Column(type="varchar(500)")    private String dvname;    @Column    private String dvtime;    @Column    private String addUser;    @Column(type="varchar(500)")    private String dvinfo;    @Column(type="varchar(500)")    private String dvpath;    @Column    private String dbname;    @Column(type = "int")    private int autobackup;    /**     * Getters & Setters     *     * @return     */}

解析类如下：

/** * 自动扫描pojo包下的实体类，进行注解解析生成表结构语句并维护表结构 * @author chenfuqiang * */@Service("autoFixTable")@Transactionalpublic class AutoFixTableSQLServerImpl implements AutoFixTable{    /**     * 要扫描的model所在的pack     */    private String pack = Global.PACKAGEOFPOJO;    @Resource    private CommonDao commonDao;    @Override    public StateInfo run(int year) {        long begin = System.currentTimeMillis();        Logger.getLogger(this.getClass()).info("---开始自动修复表结构---");        Set<Class<?>> classes = ClassTools.getClasses(pack);        StateInfo stateInfo = dealClassSQL(classes,year);        long end = System.currentTimeMillis();        Logger.getLogger(this.getClass()).info("---结束自动修复表结构---");        Logger.getLogger(this.getClass()).info("总共花费了"+((end-begin)*0.001)+"秒");        return stateInfo;    }    public StateInfo dealClassSQL(Set<Class<?>> classes,int year) {        StateInfo stateInfo = new StateInfo();        List<String> sqlAdd = new ArrayList<String>();        List<String> sqlUpt = new ArrayList<String>();        List<String> sqlPK = new ArrayList<String>();        List<String> sqlEtc = new ArrayList<String>();        Map<String,String> table_indexName = this.getKeyName();        StringBuffer keyBuf = new StringBuffer();        StringBuffer keyEditBuf = new StringBuffer();        StringBuffer allBuf = new StringBuffer();        StringBuffer addBuf = new StringBuffer();        StringBuffer editBuf = new StringBuffer();        StringBuffer pkBuf = new StringBuffer();        StringBuffer dvBuf = new StringBuffer();        if(year == 0 ) {            year = Integer.parseInt(new SimpleDateFormat("yyyy").format(new Date()));        }        for (Class<?> clas : classes){            addBuf.setLength(0);            keyBuf.setLength(0);            keyEditBuf.setLength(0);            pkBuf.setLength(0);            editBuf.setLength(0);            dvBuf.setLength(0);            allBuf.setLength(0);            Table table = clas.getAnnotation(Table.class);            if(table == null) {                continue;            }            String tablename = table.name();            if(CommonUtil.isEmpty(tablename)) {                tablename = clas.getSimpleName();            }            tablename = tablename.toUpperCase().replace("@YEAR", String.valueOf(year));            //==================================================================================//            //去掉索引，然后再修改列            String indexName = table_indexName.get(tablename.toUpperCase());            if(!CommonUtil.isEmpty(indexName)){                pkBuf.append("ALTER TABLE "+tablename+" drop CONSTRAINT "+indexName+" \r\n");                sqlPK.add(pkBuf.toString());            }            addBuf.append("CREATE TABLE [dbo].[").append(tablename).append("]( \r\n");            Field[] fields = clas.getDeclaredFields();//          这里支持集成的父类，要支持只要把下面的fields 附加到子类的fields即可。            if(table.includeSupperClass()) {                if(clas.getSuperclass()!=null){                    Class<?> clsSup = clas.getSuperclass();                    fields = (Field[]) ArrayUtils.addAll(fields,clsSup.getDeclaredFields());                }            }            for (Field field : fields){                Column column = field.getAnnotation(Column.class);                if(column == null) {continue;}                String columnname = CommonUtil.isEmpty(column.name())?field.getName():column.name();                String flag = column.flag();                String dv = column.defaultValue();                String oth = column.oth();//identity(1,1)                if(!CommonUtil.isEmpty(dv)) {                    dv = dv.toUpperCase().replace("@YEAR", String.valueOf(year));                }                String type = column.type();                addBuf.append("[").append(columnname).append("] ").append(type).append(" ");                if(!CommonUtil.isEmpty(oth)) {                    addBuf.append(" "+oth+" ");                }                if("PRIMARY".equals(CommonUtil.nullToStr(flag).toUpperCase())) {                    keyBuf.append("[").append(columnname).append("] ASC,\r\n");                    keyEditBuf.append(columnname).append(",");                    addBuf.append(" NOT NULL ");                }                if(!CommonUtil.isEmpty(dv)) {                    addBuf.append(" DEFAULT (").append(dv).append(") ");                    dvBuf.append("Update ").append(tablename).append(" Set ").append(columnname).append("=").append(dv).append(" ");                    dvBuf.append("Where ").append(columnname).append(" is null").append(" \r\n");                }                addBuf.append(",\r\n");                //===================================UPDATE FIELDS=================================//                editBuf.append("IF EXISTS(SELECT * FROM syscolumns WHERE ID=OBJECT_ID('"+tablename+"') AND NAME='"+columnname+"') \r\n");                editBuf.append("BEGIN \r\n");                editBuf.append("ALTER TABLE ").append(tablename).append(" ALTER column ").append(columnname).append(" ").append(type).append(" ");                if("PRIMARY".equals(CommonUtil.nullToStr(flag).toUpperCase())) {                    editBuf.append(" NOT NULL ");                }else {                    if(!CommonUtil.isEmpty(oth)) {                        editBuf.append(" NOT NULL ");                    }                }                editBuf.append(" \r\n");                editBuf.append("END \r\n");                editBuf.append("IF NOT EXISTS(SELECT * FROM syscolumns WHERE ID=OBJECT_ID('"+tablename+"') AND NAME='"+columnname+"') \r\n");                editBuf.append("BEGIN \r\n");                editBuf.append("ALTER TABLE ").append(tablename).append(" add ").append(columnname).append(" ").append(type).append(" ");                if("PRIMARY".equals(CommonUtil.nullToStr(flag).toUpperCase())) {                    editBuf.append(" NOT NULL ");                }else {                    if(!CommonUtil.isEmpty(oth)) {                        editBuf.append(" NOT NULL ");                    }                }                editBuf.append(" \r\n");                editBuf.append("END \r\n");            }            if(keyBuf.length() != 0) {                addBuf.append("CONSTRAINT [PK_" + tablename+ "] PRIMARY KEY CLUSTERED ( \r\n");                addBuf.append(keyBuf.substring(0,keyBuf.length()-3));                addBuf.append(") ON [PRIMARY] \r\n");            }else {                addBuf.delete(addBuf.length()-3, addBuf.length()-1);            }                addBuf.append(") ON [PRIMARY] \r\n");            allBuf.append("IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].["+ tablename+ "]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) \r\n");            allBuf.append("BEGIN \r\n");            allBuf.append(editBuf.toString());            allBuf.append("END \r\n");            sqlUpt.add(allBuf.toString());            allBuf.append("IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].["+ tablename+ "]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) \r\n");            allBuf.append("BEGIN \r\n");            allBuf.append(addBuf.toString());            allBuf.append("END \r\n");            sqlAdd.add(allBuf.toString());            //修改主键需要在列都修改完执行完之后再修改主键，因为有些列是NULL,修改完列后就是NOT NULL            //=====================================UPDATE TABLE ===============================//            //修改默认值            if(dvBuf.length() != 0) {                sqlEtc.add(dvBuf.toString());            }            //修改主键            if(keyBuf.length() != 0) {                allBuf.setLength(0);                allBuf.append("IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].["+ tablename+ "]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) \r\n");                allBuf.append("BEGIN \r\n");                allBuf.append("alter table "+tablename+" add constraint pk_"+tablename+" primary key ("+keyEditBuf.substring(0,keyEditBuf.length()-1)+") \r\n");                allBuf.append("END \r\n");                sqlEtc.add(allBuf.toString());            }        }        //======================================JDBC===============================================//        try {            if(sqlAdd.size()>0) {                commonDao.transactionUpdate(sqlAdd);                Logger.getLogger(this.getClass()).info("--新增数据库表完成--");            }            if(sqlUpt.size()>0) {                commonDao.transactionUpdate(sqlUpt);                Logger.getLogger(this.getClass()).info("--修改数据库表完成--");            }            if(sqlPK.size()>0) {                commonDao.transactionUpdate(sqlPK);                Logger.getLogger(this.getClass()).info("--主键删除完成--");            }            if(sqlEtc.size()>0) {                commonDao.transactionUpdate(sqlEtc);                Logger.getLogger(this.getClass()).info("--其他操作完成--");            }        } catch (Exception e) {            e.printStackTrace();            Logger.getLogger(this.getClass()).error(e.getMessage());            stateInfo.setFlag(false);            stateInfo.setMsg(this.getClass(),e.getMessage());        }        return stateInfo;    }    public Map<String,String> getKeyName() {//      select a.name,b.name from sysobjects a left join sysobjects b on a.parent_obj = b.id where 1=1 //      and a.xtype='pk'        StringBuffer sqlBuffer = new StringBuffer();        sqlBuffer.append("select b.name AS [表名], ");        sqlBuffer.append("a.name  AS [主键名称] ");        sqlBuffer.append("from  dbo.sysobjects a left join  dbo.sysobjects b on a.parent_obj = b.id where 1=1 ");        sqlBuffer.append("and a.xtype='pk' ");        List<Map<String,Object>> list = commonDao.getListForMap(sqlBuffer.toString());        Map<String,String> result = new HashMap<String, String>();        for(Map<String,Object> m : list) {            result.put(String.valueOf(m.get("表名")).toUpperCase(), String.valueOf(m.get("主键名称")));        }        return result;    }}

备份还原关键代码：
新增备份文件：

@Override    public StateInfo addDBInfo(User user, DBBackupInfoBean backupInfoBean,String Flag) {        StateInfo stateInfo = new StateInfo();        try {            Date date = new Date();            backupInfoBean.setAddUser(user.getUsername());            backupInfoBean.setAutobackup(0);            backupInfoBean.setDvtime(Global.df.format(date));            StringBuffer sqlTemp = new StringBuffer();            /**             * 备份数据库SQL语句             */            List<String> sqlList = new ArrayList<String>();            if(Flag == null) {                String path = System.getProperty("user.dir")+"/backupfile";                File file = new File(path);                if (file.exists() == false) {                    file.mkdirs();                }                backupInfoBean.setDvpath(path+"/"+backupInfoBean.getDvname()+Global.dfpath.format(date)+".SiQ");                Logger.getLogger(this.getClass()).info("备份文件路径："+backupInfoBean.getDvpath());                sqlTemp.append("backup database " + backupInfoBean.getDbname()+ " to disk='" + backupInfoBean.getDvpath() + "' with init");                sqlList.add(sqlTemp.toString());            }            sqlTemp.setLength(0);            sqlTemp.append("Insert into SIQ_DatabaseBackupVersion(dvid,dvname,dvtime,addUser,dvinfo,dvpath,dbname,autobackup) values (");            sqlTemp.append("newid(),'").append(CommonUtil.isEmpty(backupInfoBean.getDvname())?backupInfoBean.getDbname().toUpperCase():backupInfoBean.getDvname()).append("',");            sqlTemp.append("'").append(backupInfoBean.getDvtime()).append("',");            sqlTemp.append("'").append(backupInfoBean.getAddUser()).append("',");            sqlTemp.append("'").append(CommonUtil.isEmpty(backupInfoBean.getDvinfo())?"用户没有填写":backupInfoBean.getDvinfo()).append("',");            sqlTemp.append("'").append(backupInfoBean.getDvpath()).append("',");            sqlTemp.append("'").append(backupInfoBean.getDbname()).append("',");            sqlTemp.append(backupInfoBean.getAutobackup());            sqlTemp.append(")");            sqlList.add(sqlTemp.toString());            sqlTemp.setLength(0);            dao.transactionUpdate(sqlList);        }catch (Exception e) {            stateInfo.setFlag(false);            stateInfo.setMsg(this.getClass(),e.getMessage());        }        return stateInfo;    }

还原备份文件

@Override    public StateInfo restore(String dbname, String vid) {        StateInfo stateInfo = new StateInfo();        try {            Map<String,Object> dvInfo = this.getBackupInfo(vid);            String filePath = String.valueOf(dvInfo.get("dvpath"));            File backupFile = new File(filePath);            /**             * 判断文件是否存在，不存在则给予提示。             * 不存在可能由于误删了备份文件夹backupfile里面的文件。             */            if (backupFile.exists()) {                StringBuilder sqlBuffer = new StringBuilder();                try{                    sqlBuffer.append("create proc p_killspid ");                    sqlBuffer.append("@dbname sysname ");                    sqlBuffer.append("as  ");                    sqlBuffer.append("declare @s nvarchar(1000) ");                    sqlBuffer.append("declare tb cursor local for ");                    sqlBuffer.append("select s='kill '+cast(spid as varchar) ");                    sqlBuffer.append("from master..sysprocesses  ");                    sqlBuffer.append("where dbid=db_id(@dbname) ");                    sqlBuffer.append("open tb ");                    sqlBuffer.append("fetch next from tb into @s ");                    sqlBuffer.append("while @@fetch_status=0 ");                    sqlBuffer.append("begin ");                    sqlBuffer.append("exec(@s) ");                    sqlBuffer.append("fetch next from tb into @s ");                    sqlBuffer.append("end ");                    sqlBuffer.append("close tb ");                    sqlBuffer.append("deallocate tb ");                    dao.executeSQL(sqlBuffer.toString());                } catch (Exception e) {                    //不需要捕获，第二次创建会报错，无所谓这边。                }                sqlBuffer.setLength(0);                /**                 * 获取当前被还原的备份文件路径                 */                sqlBuffer.append("RESTORE FILELISTONLY FROM DISK = N'" + filePath + "'");                List<Map<String,Object>> tempInfos = dao.getListForMap(sqlBuffer.toString());                if(CommonUtil.isNotEmptyList(tempInfos)&&tempInfos.size()>1) {                    /**                     * 备份文件里面存的mdf ldf存在的路径                     */                    String olddb = String.valueOf(tempInfos.get(0).get("LogicalName"));                    String olddb_log = String.valueOf(tempInfos.get(1).get("LogicalName"));                    /**                     * 关闭被还原数据库的所有连接                     * 让所有连接到这个库的连接都断掉，不然无法还原数据库会提示被占用。                     */                    dao.executeSQL("exec p_killspid  '" + dbname + "'");                    /**                     * 还原后的数据库mdf ldf存放的路径                     */                    String path = System.getProperty("user.dir")+"/MSSQL_DATA/";                    File file = new File(path); if (file.exists() == false) {file.mkdirs();}                    /**                     * 开始还原操作                     */                    sqlBuffer.setLength(0);                    sqlBuffer.append("RESTORE DATABASE " + dbname + " FROM DISK = '" + filePath + "' ");                    sqlBuffer.append("WITH REPLACE,MOVE '" + olddb + "' TO '"                            + path + dbname + ".mdf', MOVE '" + olddb_log + "' TO '"                            + path + dbname + "_log.ldf'");                    dao.executeSQL(sqlBuffer.toString());                }else {                    stateInfo.setFlag(false);                    stateInfo.setMsg(this.getClass(),"读取备份文件内部还原信息失败！SQL:"+sqlBuffer.toString());                }            }else {                stateInfo.setFlag(false);                stateInfo.setMsg(this.getClass(),"备份文件丢失，此记录已经失效！路径："+filePath);            }        }catch (Exception e) {            stateInfo.setFlag(false);            stateInfo.setMsg(this.getClass(),e.getMessage());        }        return stateInfo;    }

接触过sqlserver的都知道他有mdf和ldf两个文件，ldf主要记录日志，那么有时候我们并不需要通过日志来恢复数据，需要删除他来节约硬盘空间，这里可以通过语句来实现：

@Override    public StateInfo clearDBLog(String dbname) {        StateInfo stateInfo = new StateInfo();        try {            StringBuffer sqlBuffer = new StringBuffer();            sqlBuffer.append("select name from "+dbname+".dbo.sysfiles where fileid = '2'");            List<Map<String,Object>> maps = dao.getListForMap(sqlBuffer.toString());            if(maps.size()>0) {                String tempDBLOG = String.valueOf(maps.get(0).get("name"));                List<String> sqls = new ArrayList<String>();                sqlBuffer.setLength(0);                sqlBuffer.append("alter database "+dbname+" set recovery simple");                sqls.add(sqlBuffer.toString());                sqlBuffer.setLength(0);                sqlBuffer.append("dbcc shrinkfile ("+tempDBLOG+",1)");                sqls.add(sqlBuffer.toString());                /**                 * 恢复完全模式，会详细记录日子。                 *///                sqlBuffer.setLength(0);//                sqlBuffer.append("alter database "+dbname+" set recovery full");//                sqls.add(sqlBuffer.toString());                dao.transactionUpdate(sqls);            }else {                stateInfo.setFlag(false);                stateInfo.setMsg(this.getClass(),"执行语句未得到结果："+sqlBuffer.toString());            }        }catch (Exception e) {            stateInfo.setFlag(false);            stateInfo.setMsg(this.getClass(),e.getMessage());        }        return stateInfo;    }

码云代码

Github地址：kkillala/mssql-web
码云地址： Tim / mssql-web
喜欢的话欢迎订阅关注。