数据库的备份与还原

#include "buckrecodb.h"
#include <QFile>
#include <QFileDialog>
#include <QMessageBox>
#include <QDebug>
#include <QDir>
#include <QDateTime>
BackupAndRecover::BackupAndRecover()
{

}
//备份数据库
bool BackupAndRecover::backupDB(const QString& databaseName,QString buckupPath)
{
    bool result = false;

    QSqlDatabase database = getCurrentDB(databaseName);
    QSqlQuery query(database);

    //开启cmdShell
    QString startcmdshell = QString("exec sp_configure 'show advanced options',1 "
                                    "reconfigure "
                                    "exec sp_configure 'xp_cmdshell',1 "
                                    "reconfigure "
                                    "exec sp_configure 'show advanced options',0 "
                                    "reconfigure ");
    if(!query.exec(startcmdshell))
    {

    }

    //覆盖已存在信息，并将被覆盖文件存至历史备份文件夹
    QString filePath = QString("%1\\%2.bak").arg(buckupPath).arg(databaseName);
    QFile backupFile(filePath);
    if(backupFile.exists())
    {
        int result = QMessageBox::information(this,QStringLiteral("提示"),QStringLiteral("已存在%1，是否覆盖？").arg(filePath),QMessageBox::Ok|QMessageBox::Cancel);
        if(result == QMessageBox::Ok)
        {
            bool next = false;
            QDir historyBKDir;
            QDateTime currentDateTime;
            currentDateTime = QDateTime::currentDateTime();
            QDate currentDate = currentDateTime.date();
            QTime currentTime = currentDateTime.time();
            QString backupTime = QString("[%1_%2_%3 %4_%5_%6]%7")
                    .arg(currentDate.year())
                    .arg(QString("%1%2").arg((currentDate.month()<9)?"0":"").arg(currentDate.month()))
                    .arg(QString("%1%2").arg((currentDate.day()<9)?"0":"").arg(currentDate.day()))
                    .arg(QString("%1%2").arg((currentTime.hour()<9)?"0":"").arg(currentTime.hour()))
                    .arg(QString("%1%2").arg((currentTime.minute()<9)?"0":"").arg(currentTime.minute()))
                    .arg(QString("%1%2").arg((currentTime.second()<9)?"0":"").arg(currentTime.second()))
                    .arg(databaseName);
            historyBKDir.setPath(QStringLiteral("%1\\%2\\%3").arg(buckupPath).arg(QStringLiteral("历史备份")).arg(backupTime));

            //创建历史备份目录
            if(!historyBKDir.exists())
            {
                next = historyBKDir.mkpath(historyBKDir.path());
            }else
            {
                next = true;
            }

            //复制待覆盖历史备份文件
            if(true == next)
            {
                next = QFile::copy(filePath,QStringLiteral("%1\\%2.bak").arg(historyBKDir.path()).arg(databaseName));
            }else
            {
                qDebug()<<__FILE__<<__LINE__<<"can not create history backup path";
                return false;
            }

            //删除当前待覆盖备份文件
            if(true == next)
            {
                backupFile.remove();
            }else
            {
                qDebug()<<__FILE__<<__LINE__<<"can not copy .bak file";
                return false;
            }
        }else
        {
            return false;
        }
    }

    QString newFilePath = QString("exec xp_cmdshell 'mkdir %1'").arg(buckupPath);
    qDebug()<<__FILE__<<__LINE__<<"newFilePath = "<<newFilePath;
    if(!query.exec(newFilePath))
    {

    }
//        _BackupAndRecover.closeCmdshell(databaseName);
    QString sqlstr = QString("use %1 backup database %2 to disk = '%3\\%4.bak'").arg(databaseName).arg(databaseName).arg(buckupPath).arg(databaseName);

    qDebug()<<__FILE__<<__LINE__<<"sqlstr = "<<sqlstr;

    if(query.exec(sqlstr))
    {
        result = true;
        //QMessageBox::information(this,QStringLiteral("成功"), QStringLiteral("成功备份 [%1] 至 [%2]").arg(databaseName).arg(buckupPath));
    }else
    {
        //qDebug()<<__FILE__<<__LINE__<<query.lastError();
        //QMessageBox::information(this,QStringLiteral("失败"), QStringLiteral("备份失败，路径不存在"));
    }

    return result;
}

//还原数据库
bool BackupAndRecover::recoverDB(const QString& databaseName,QString recoverPath)
{
    bool result = false;
    QSqlDatabase database = getCurrentDB(databaseName);
    QSqlQuery query(database);

    //开启cmdShell
    QString startcmdshell = QString("exec sp_configure 'show advanced options',1 "
                                    "reconfigure "
                                    "exec sp_configure 'xp_cmdshell',1 "
                                    "reconfigure "
                                    "exec sp_configure 'show advanced options',0 "
                                    "reconfigure ");
    if(!query.exec(startcmdshell))
    {
    }

    QString sqlstr = QString("use master ALTER DATABASE %1 SET offline WITH ROLLBACK IMMEDIATE "
                             "RESTORE DATABASE %2 FROM DISK = '%3\\%4.bak' WITH REPLACE "
                             "ALTER DATABASE %5 SET online").arg(databaseName).arg(databaseName).arg(recoverPath).arg(databaseName).arg(databaseName);

    qDebug()<<__FILE__<<__LINE__<<"sqlstr = "<<sqlstr;

    if(query.exec(sqlstr))
    {
        result = true;
        //QMessageBox::information(this,QStringLiteral("成功"), QStringLiteral("成功还原 [%1] 从 [%2]").arg(databaseName).arg(recoverPath));
    }else
    {
        //qDebug()<<__FILE__<<__LINE__<<query.lastError();
        //QMessageBox::information(this,QStringLiteral("失败"), QStringLiteral("还原失败，未找到 [%1.bak]").arg(databaseName));
    }

    return result;
}

QSqlDatabase BackupAndRecover::getCurrentDB(const QString& databaseName)
{
    QSqlDatabase database ;
    //获取当前数据库

    return database;
}

头文件

#ifndef BUCKRECODB_H
#define BUCKRECODB_H
#include <QTreeWidgetItem>
#include <QTreeWidget>
#include <QStackedWidget>
#include <QProgressBar>
#include <QSettings>
#include <QSqlQuery>
class BackupAndRecover
{
public:
    BackupAndRecover();
    bool backupDB(const QString& databaseName, QString buckupPath);
    bool recoverDB(const QString& databaseName, QString recoverPath);
    QSqlDatabase getCurrentDB(const QString& databaseName);
};

#endif // BUCKRECODB_H