备份数据库存储过程

简介: 原文: 备份数据库存储过程 由于使用SSMS创建的维护计划中,完整备份的话,会打断别的备份的顺序链,而由于管理原因,往往需要有几套备份计划,所以经过时间,本人编写了一个存储过程实现我的想法。
原文: 备份数据库存储过程

由于使用SSMS创建的维护计划中,完整备份的话,会打断别的备份的顺序链,而由于管理原因,往往需要有几套备份计划,所以经过时间,本人编写了一个存储过程实现我的想法。不管你用不用,反正我用了。偷笑


首先,创建一个表,用于记录备份信息。因为用DMV和系统视图的话往往要编写很多东西,对于一般人很难记住那么多表。所以使用一个表来记录更加方便。以前之前已经创建了一个库 AuditDB,用于监控数据库的DDL操作,所以这里没有再编写建库脚本。只是在这个库里面创建一个表。

USE AuditDB
GO
IF OBJECT_ID('BackupHistory') IS NOT NULL
DROP TABLE BackupHistory
GO
CREATE TABLE BackupHistory
(
	DatabaseName		NVARCHAR(128),--数据库名
	CreateDate			DATETIME,--创建时间
	Compatibilitylevel	TINYINT,--数据库兼容级别
	RecoveryModel		CHAR(10),--恢复模式
	BackupStartData		DATETIME, --备份开始时间
	BackupEndData		DATETIME, --备份结束时间
	BackupSpace			NVARCHAR(128), --备份文件大小
	Operator			NVARCHAR(128),--执行操作者
	Servername			NVARCHAR(128),	--服务器名称
	[FILENAME]			NVARCHAR(256) --备份文件名
);

建了表之后,在msdb或者AuditDB中创建这个存储过程。一般不要在客户数据中创建。


/*
注意:由于用GUI备份会导致备份链中断,而作业无法实现“仅复制备份”,所以使用脚本备份
create by huangzj 20120510
EXEC Backup_By_DBA 'auditdb','e:\新建文件夹\'
*/

ALTER PROC Backup_By_DBA
(
@dbname NVARCHAR(128),
@bakpath NVARCHAR(128)='E:\'
)
AS
--不备份系统表
IF @dbname IN ( 'master', 'msdb', 'model', 'tempdb' ) 
    BEGIN
        RETURN
    END 
ELSE 
    BEGIN
--定义备份时间,精确到秒
        DECLARE @date NVARCHAR(64)
        SELECT  @date = SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 121), 1, 4)
                + '_' + SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 121), 6, 2)
                + '_' + SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 121), 9, 2)
                + '_' + CONVERT(CHAR(2), DATEPART(hh, GETDATE()))
                + CONVERT(CHAR(2), DATEPART(mi, GETDATE()))
                + CONVERT(CHAR(2), DATEPART(ss, GETDATE()))
--定义要备份的数据库名
        DECLARE @db NVARCHAR(20)
        SET @db = '' + '' + @dbname + '' + ''
--定义备份文件的全名
        DECLARE @bakname NVARCHAR(128)
        SELECT  @bakname = @db + '_' + @date
--定义备份存放路径
        DECLARE @disk NVARCHAR(256)
        SELECT  @disk = @bakpath + @bakname + '.bak'
--定义备份描述
        DECLARE @name NVARCHAR(128)
        SELECT  @name = @db + '-完整 数据库 备份'
--定义错误信息
        DECLARE @error NVARCHAR(128)
        SELECT  @error = '验证失败。找不到数据库“' + @db + '”的备份信息。'

        BACKUP DATABASE @db TO  DISK = @disk WITH  COPY_ONLY, NOFORMAT, NOINIT,  
NAME =@name, SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM ;
        DECLARE @backupSetId AS INT
        SELECT  @backupSetId = position
        FROM    msdb..backupset
        WHERE   database_name = @db
                AND backup_set_id = ( SELECT    MAX(backup_set_id)
                                      FROM      msdb..backupset
                                      WHERE     database_name = @db
                                    )
        IF @backupSetId IS NULL 
            BEGIN
                RAISERROR(@error, 16, 1)
            END
        RESTORE VERIFYONLY FROM  DISK = @disk WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
        
        INSERT INTO AuditDB.dbo.BackupHistory(DatabaseName,CreateDate,Compatibilitylevel,RecoveryModel,BackupStartData,BackupEndData,BackupSpace,Operator,Servername,[FILENAME])
        SELECT a.[name] ,create_date,a.[compatibility_level] ,recovery_model_desc,S.backup_start_date,S.backup_finish_date,CONVERT(VARCHAR(20),CONVERT(DECIMAL(10,2),S.compressed_backup_size/(1024*1024)))+'MB',
        S.[user_name],S.[server_name],physical_device_name
        FROM sys.databases a INNER JOIN msdb.dbo.backupset S ON a.name=S.database_name Inner Join
            msdb.dbo.backupmediafamily M ON S.media_set_id =M.media_set_id
        WHERE a.name=db_name() AND physical_device_name=@disk
    END
 备注:此处只是完整备份的例子,如果需要别的备份,那么要修改脚本。

目录
相关文章
|
5月前
|
存储 关系型数据库 分布式数据库
PolarDB开源数据库进阶课5 在线备份
本文介绍了如何在PolarDB RAC一写多读集群中进行在线备份,特别针对共享存储模式。通过使用`polar_basebackup`工具,可以将实例的本地数据和共享数据备份到本地盘中。实验环境依赖于Docker容器中用loop设备模拟的共享存储。
115 1
|
3月前
|
存储 关系型数据库 MySQL
利用Cron表达式实现MySQL数据库的定时备份
以上就是如何使用Cron表达式和mysqldump命令实现MySQL数据库的定时备份。这种方法的优点是简单易用,而且可以根据需要定制备份的时间和频率。但是,它也有一些限制,例如,它不能备份MySQL服务器的配置文件和用户账户信息,也不能实现增量备份。如果需要更复杂的备份策略,可能需要使用专门的备份工具或服务。
88 15
|
4月前
|
关系型数据库 Shell 网络安全
定期备份数据库:基于 Shell 脚本的自动化方案
本篇文章分享一个简单的 Shell 脚本,用于定期备份 MySQL 数据库,并自动将备份传输到远程服务器,帮助防止数据丢失。
|
8月前
|
关系型数据库 MySQL Linux
Linux环境下MySQL数据库自动定时备份实践
数据库备份是确保数据安全的重要措施。在Linux环境下,实现MySQL数据库的自动定时备份可以通过多种方式完成。本文将介绍如何使用`cron`定时任务和`mysqldump`工具来实现MySQL数据库的每日自动备份。
531 3
|
8月前
|
监控 关系型数据库 MySQL
Linux环境下MySQL数据库自动定时备份策略
在Linux环境下,MySQL数据库的自动定时备份是确保数据安全和可靠性的重要措施。通过设置定时任务,我们可以每天自动执行数据库备份,从而减少人为错误和提高数据恢复的效率。本文将详细介绍如何在Linux下实现MySQL数据库的自动定时备份。
268 3
|
9月前
|
存储 定位技术 数据库
介绍一下数据库的备份和恢复策略
【10月更文挑战第21】介绍一下数据库的备份和恢复策略
|
8月前
|
数据库
【赵渝强老师】数据库的备份方式
备份数据库是指将数据库中的数据及相关信息保存起来,以便在系统故障时恢复。备份对象不仅限于数据本身,还包括数据库对象、用户权限等。根据备份策略、类型和模式的不同,可分为整体/部分备份、完全/增量备份、一致/非一致备份。文中还附有相关视频讲解。
110 0
|
27天前
|
人工智能 运维 关系型数据库
数据库运维:mysql 数据库迁移方法-mysqldump
本文介绍了MySQL数据库迁移的方法与技巧,重点探讨了数据量大小对迁移方式的影响。对于10GB以下的小型数据库,推荐使用mysqldump进行逻辑导出和source导入;10GB以上可考虑mydumper与myloader工具;100GB以上则建议物理迁移。文中还提供了统计数据库及表空间大小的SQL语句,并讲解了如何使用mysqldump导出存储过程、函数和数据结构。通过结合实际应用场景选择合适的工具与方法,可实现高效的数据迁移。
212 1
|
2月前
|
负载均衡 算法 关系型数据库
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
本文聚焦 MySQL 集群架构中的负载均衡算法,阐述其重要性。详细介绍轮询、加权轮询、最少连接、加权最少连接、随机、源地址哈希等常用算法,分析各自优缺点及适用场景。并提供 Java 语言代码实现示例,助力直观理解。文章结构清晰,语言通俗易懂,对理解和应用负载均衡算法具有实用价值和参考价值。
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
|
4月前
|
关系型数据库 MySQL Java
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
【YashanDB知识库】原生mysql驱动配置连接崖山数据库