SQL Server中使用数据库快照的方式来完成测试环境中数据库的轻量级备份还原操作

简介: 原文:SQL Server中使用数据库快照的方式来完成测试环境中数据库的轻量级备份还原操作  在开发或者测试环境的数据库中,经常会发现有开发或者测试人员误删除表或者数据的情况,对于开发或者测试库,一般都没有安排定时的备份任务去备份数据库,一方面是由于存储资源有限,不太可能给开发或者测试环境准备大量的存储空间,二是必要性不是很强,开发或者测试库的数据库对象变化太多,通过还原备份的方式又有可能冲掉其最近新建的数据库对象。
原文: SQL Server中使用数据库快照的方式来完成测试环境中数据库的轻量级备份还原操作

 

在开发或者测试环境的数据库中,经常会发现有开发或者测试人员误删除表或者数据的情况,
对于开发或者测试库,一般都没有安排定时的备份任务去备份数据库,
一方面是由于存储资源有限,不太可能给开发或者测试环境准备大量的存储空间,
二是必要性不是很强,开发或者测试库的数据库对象变化太多,通过还原备份的方式又有可能冲掉其最近新建的数据库对象。
但是不得不面对的问题就是个别人在执行update或者delete操作的时候“忘了加where条件”这种事情的发生。

这是开发或者测试环境的一个痛点,相信多数人都经历过,
当面对开发人员过来说“我刚才delete表的时候忘了加where条件,结果全删了,能不能还原一下?”这个问题的时候,
实则有点尴尬,只能告知无法还原,sqlserver不支持闪回功能,又因为测试数据库没有备份,确实无能为力。
此时,开发人员往往会报以鄙视+无奈的眼神,作为冒牌DBA,确实难以相助。

为了切实地解决这个问题,同时又能够以最小的代价来实现,可以定时给测试库打快照的这种轻量级的方式来解决误删除数据的问题。
一是快照占用的空间并不大,
二是可以通过轻量级的方式去还原单个表的数据
花了点时间,写了个全实例下用户数据库的生成快照操作,可以安排一个定时任务来完成定时对数据库生成快照的方式来做一个轻量级的备份。
因此就可以方便地解决执行update或者delete操作的时候“忘了加where条件”误删数据或者表的情况。

 

通过定时对开发环境的数据库打快照,一旦出现误删数据的情况,可以根据最近的快照中的数据还原误删的数据,同时可以根据实际情况,删除创建的已过期的快照。

 

简单测试了一下,应该可以达到目的,没有做完整的测试,原理很简单,双层while循环,就是通过循环每一个数据库对应的物理文件,动态生成一个创建数据库快照的语句
此种方式仅适应于开发或者测试环境数据库的轻量级备份,不适应于生产环境。

--生成全实例下的数据库快照
create proc [dbo].[CreateSnapshotForBackUp]
(
    --数据库快照文件位置
    @p_FilePath varchar(500),
    --保留最近N个小时之内创建的快照,单位为小时
    @p_RetainTime int
)
as
begin
    set nocount on;

    declare @strSql            varchar(2000)
    declare @strDatetime    varchar(20)
    declare @strDBFileName    varchar(200)

    set @strDatetime =  replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
    

    if object_id('tempdb..#databaseName') is not null
    begin 
        drop table #databaseName
    end

    select name 
    into #databaseName  
    from  sys.databases where database_id>6 and source_database_id is null 
    

    declare @databaseName varchar(200);
    declare @databaseCnt int;
    select @databaseCnt = count(1) from #databaseName;

    while @databaseCnt>0
    begin

        select top 1 @databaseName = name from #databaseName

        if object_id('tempdb..#dbFiles') is not null
        begin 
            drop table #dbFiles
        end

        select concat('(','name = ' , name , ',FileName = ''',@p_FilePath,name,'_',@strDatetime,'.ss'' )' ) as strFileName
        into #dbFiles
        from sys.sysaltfiles 
        where  dbid=db_id(@databaseName) and status = 2

        DECLARE @dbFileCnt int = 0;
        SELECT @dbFileCnt = COUNT(1) FROM  #dbFiles
        while @dbFileCnt>0
        begin
            select top 1 @strDBFileName = strFileName from #dbFiles;
            set @strSql=CONCAT(@strSql,',',@strDBFileName,char(10));
            delete top (1) from #dbFiles;
            select @dbFileCnt = count(1) from #dbFiles;
        end

        set @strSql=stuff(@strSql,1,1,'')

        set @strSql = CONCAT('create database ' ,@databaseName,'_',@strDatetime, char(10),
                             ' on ' , char(10), @strSql
                             ,'as snapshot of ' , @databaseName)


        begin try
            print @strSql
            exec(@strSql)
            select @databaseName+'snapshot create successful:'+@databaseName+@strDatetime
            set @strSql = ''
        end try
        begin catch
            select 'snapshot create fail'
            throw
        end catch

        delete top (1) from #databaseName
        select @databaseCnt = count(1)  from #databaseName
    end


    --删除过期的数据库快照
    begin try
        if object_id('tempdb..#snapshotname') is not null
            drop table #snapshotname

        select name 
        into #snapshotname 
        from sys.databases 
        where source_database_id is not null and create_date<dateadd(hh,-@p_RetainTime,getdate());

        declare @cnt int = 0;
        declare @strDBName varchar(200) = ''
        declare @strDropDatabase varchar(max) =  ''
        select @cnt = count(1) from #snapshotname
        while @cnt>0
        begin
            select top 1 @strDBName = name from #snapshotname;
            set @strDropDatabase = 'drop database ' +@strDBName;
            print @strDropDatabase
            exec (@strDropDatabase);
            delete top (1) from #snapshotname;
            select @cnt = count(1) from #snapshotname
        end

    end try
    begin catch
        select N'snapshot delete fail'
        throw
    end catch

end

 

 

 

实话讲,已经完全忘记了T-SQL中游标的语法了,通过while循环临时表的方式,也可以达到游标循环的效果,并且这种语法逻辑结构上更清晰简便,根本不会忘记,呵呵。

 

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
3天前
|
SQL 关系型数据库 MySQL
【MySQL 数据库】7、SQL 优化
【MySQL 数据库】7、SQL 优化
23 0
|
5天前
|
SQL 关系型数据库 MySQL
数据库管理新星:Navicat、HeidiSQL和SQL Workbench的竞争格局
随着数据管理需求的不断增长,数据库管理工具的选择变得至关重要。在众多选择中,Navicat、HeidiSQL和SQL Workbench作为备受关注的数据库管理工具,各自展现出独特的特性和功能,竞争格局日益激烈。
17 0
|
1月前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之数据查询
【数据库SQL server】关系数据库标准语言SQL之数据查询
58 0
|
15天前
|
SQL 存储 关系型数据库
sql数据库的相关概念与底层介绍
sql数据库的相关概念与底层介绍
32 0
|
3天前
|
SQL druid Java
【MyBatis】2、MyBatis 的动态 SQL 和增删改操作
【MyBatis】2、MyBatis 的动态 SQL 和增删改操作
18 0
|
4天前
|
SQL Oracle Java
怎样写出可在各种数据库间移植的SQL
国际标准没有对这些函数做规定,这种 SQL 语句就会和数据库相关而无法移植了,而这些函数在应用开发中还特别常见。 Hibernate 能将包括这些函数的标准 HQL 语句转换成不同数据库的 SQL,但 HQL 支持的函数太少,碰到不认识的函数还是要分别注册,这就丧失了可移植性;而且 HQL 能生成的 SQL 本身也比较简单,覆盖面太窄了。
|
10天前
|
SQL 存储 数据安全/隐私保护
|
12天前
|
SQL 存储
SQL Server基本函数
SQL Server基本函数
|
26天前
|
存储 SQL 数据库连接
连接并操作数据库:Python 数据库案例
数据库是一种用于存储和管理数据的工具,它以一种有组织的方式将数据存储在文件或内存中,以便于检索和处理。数据库系统通常使用 SQL(Structured Query Language)语言来进行数据的操作,包括数据的插入、查询、更新和删除等。
|
26天前
|
前端开发 数据库连接 数据库
ASP.NETMVC数据库完整CRUD操作示例
ASP.NETMVC数据库完整CRUD操作示例
20 0

热门文章

最新文章