SQLServer备份还原数据库功能封装分享

简介

SQL Server 是一款颇为流行的关系型数据库，支持备份还原功能。备份是指将数据库的全部或部分数据复制到磁盘或磁带上，还原则是将这些备份重新到数据库引擎中。

封装备份还原数据库功能意义在于：将复杂的备份还原过程简化，提高代码复用性和可维护性。

功能封装

备份数据库

-- 备份数据库
CREATE PROCEDURE sp_BackupDB
    @dbName nvarchar(50),
    @backupPath nvarchar(200)
AS
BEGIN
    DECLARE @backupFile nvarchar(200)
    SET @backupFile = @backupPath + '\' + @dbName + '_' + CONVERT(nvarchar(20), GETDATE(), 120) + '.bak'
    BACKUP DATABASE @dbName TO DISK = @backupFile
END

备份数据库存储过程 sp_BackupDB 接收两个参数：@dbName 数据库名，@backupPath 备份保存路径。该存储过程通过 BACKUP DATABASE 命令备份数据库，并将备份文件存储在指定路径下。

还原数据库

-- 还原数据库
CREATE PROCEDURE sp_RestoreDB
    @dbName nvarchar(50),
    @backupFile nvarchar(200)
AS
BEGIN
    DECLARE @mdfFile nvarchar(200), @ldfFile nvarchar(200)
    SET @mdfFile = @dbName + '.mdf'
    SET @ldfFile = @dbName + '_log.ldf'

    -- 杀掉占用的连接
    DECLARE @killSql nvarchar(1000)
    SET @killSql = ''
    SELECT @killSql = @killSql + 'kill ' + CONVERT(nvarchar(5), spid) + ';'
    FROM sys.sysprocesses
    WHERE dbid = db_id(@dbName)
    EXEC(@killSql)

    -- 恢复数据库
    RESTORE DATABASE @dbName FROM DISK = @backupFile
    WITH REPLACE,
    MOVE @mdfFile TO 
        (SELECT physical_name FROM sys.master_files WHERE database_id = db_id(@dbName) AND file_id = 1),
    MOVE @ldfFile TO 
        (SELECT physical_name FROM sys.master_files WHERE database_id = db_id(@dbName) AND file_id = 2)
END

还原数据库存储过程 sp_RestoreDB 接收两个参数：@dbName 数据库名，@backupFile 数据库备份文件路径。该存储过程通过 RESTORE DATABASE 命令还原数据库，并杀掉占用连接，保证还原成功。

示例说明

示例1：备份数据库

EXEC sp_BackupDB 'testDB', 'D:\backup'

该语句表示备份名为 testDB 的数据库，并将备份文件保存在 D:\backup 目录下。

示例2：还原数据库

EXEC sp_RestoreDB 'testDB', 'D:\backup\testDB_20211018091500.bak'

该语句表示还原名为 testDB 的数据库，并使用备份文件 D:\backup\testDB_20211018091500.bak 进行还原。