sql 作业+游标自动备份数据库

2023-09-26 10:42:58

前言

昨天有个同事在客户的服务器上面弄数据库,不小心执行了一条 sql 语句

1 TRUNCATE TABLE xxx

碉堡了吧，数据全没了 - - ，然后就是在网上拼命的搜索关于数据恢复的软件，搞了一下午还是回不来了。

需求来了

后面老大就让我写一个作业每天备份一次数据库，且只需要备份一些重要的表。

如何实现

作业肯定不是难点，难点是怎么把需要的表的数据拿出来存放到新的数据库中。

我首先就想到了sql中的导出数据功能，结果是可行的，可问题来了那个是使用工具导出的，用sql语句咋写了，反正我是不会，有谁会的告诉一下哈。

后面在百度谷歌上找啊找啊，找到了个bcp

1 EXEC 数据库名..xp_cmdshell ‘bcp 表名 out 文件路径 -c -T -U‘‘账号‘‘ -P‘‘密码‘‘‘

结果发现这个只能导出文件，像txt,xls，csv等一些，不是我想要的mdf啊。（可能是我不会用落）

最终解决的方法

1 select * into [备份的数据库].dbo.表名  from [原数据库].dbo.表名

意思是往[备份的数据库]里增加一个表,表结构与数据都从[原数据库]的表中复制(反正我是这么理解的 - -)

总体思路及代码

1 新建数据库

数据库名我是根据原始数据库名加上当前日期命名的 testdb_bak_年_月_日

创建数据库的代码

 1 declare @fromDataBaseName nvarchar(50) --原数据库名称
 2 set @fromDataBaseName=‘testdb‘
 3 declare @errorCount int --错误数
 4 declare @yearMonthDay nvarchar(50) --年_月_日
 5 select @yearMonthDay=Datename(year,GetDate())+‘_‘+Datename(month,GetDate())+‘_‘+Datename(day,GetDate())
 6 declare @baseName nvarchar(50)--数据库名称
 7 set @baseName=@fromDataBaseName + ‘_bak_‘ + @yearMonthDay --数据库名称为 gpsdb_bak_xx_xx_xx
 8 
 9 declare @createBase nvarchar(500) --创建数据库用的sql语句
10 set @createBase=‘create database [‘ + @baseName + ‘]
11  on  primary(name=[‘+ @baseName + ‘_Data],filename=‘‘D:\GpsdbBak\‘ + @baseName + ‘_Data.mdf‘‘,size=5mb,maxsize=100mb, filegrowth=15%)
12 log on(name=[‘+ @baseName + ‘_log],filename=‘‘D:\GpsdbBak\‘ + @baseName + ‘_log.ldf‘‘,size=2mb,filegrowth=1mb)‘
13 print @createBase
14 exec (@createBase) --执行创建数据库
15 set @errorCount=@@ERROR

2 获取原数据库中经过刷选的表然后复制到上面新建的表中

获取原数据库中所有的表名,sql 语句如下

use [testdb]
select name from sysobjects where type = ‘U‘ and --获取所有表名
   (CHARINDEX(‘gps_position_‘,name)=0 and --带有这个的表不要
   CHARINDEX(‘buf‘,name)=0 and --临时表不要
   name <> ‘gps_log‘)  --日志表不要
   ORDER BY Name

3 游标

得到了所有表之后肯定就是最后的关键步骤，复制表到新数据库，这里我是用游标实现的。

 1 use [testdb]  
 2    declare test_Cursor  cursor local for --定义一个游标
 3    
 4    select name from sysobjects where type = ‘U‘ and --获取所有表名
 5    (CHARINDEX(‘gps_position_‘,name)=0 and --带有这个的表不要
 6    CHARINDEX(‘buf‘,name)=0 and --临时表不要
 7    name <> ‘gps_log‘)  --日志表不要
 8    ORDER BY Name 
 9   
10    open test_Cursor--打开游标
11    declare @table_name nvarchar(50)
12    fetch next from test_Cursor into @table_name 
13     while @@FETCH_STATUS=0
14      begin 
15       if(@table_name<>‘‘)
16        begin      
17         exec (‘select * into [‘ + @baseName +‘].dbo.‘ + @table_name +‘ from [‘+ @fromDataBaseName +‘].dbo.‘ + @table_name) --关键的一语句代码 
18       fetch next from test_Cursor into @table_name 
19      end
20 close test_Cursor
21 DEALLOCATE test_Cursor

完整代码

 1 /*
 2 备份数据脚本，将@fromDataBaseName(原数据库备份到@baseName数据库中)
 3 需注意的地方
 4 1:需要在D盘建立一个名为GpsdbBak的文件夹
 5 2:需要根据实际情况给fromDataBaseName变量赋值
 6 3:找到use 修改use后面的数据库名称为实际情况下的
 7 */
 8 declare @fromDataBaseName nvarchar(50) --原数据库名称
 9 set @fromDataBaseName=‘testdb‘
10 declare @errorCount int --错误数
11 declare @yearMonthDay nvarchar(50) --年_月_日
12 select @yearMonthDay=Datename(year,GetDate())+‘_‘+Datename(month,GetDate())+‘_‘+Datename(day,GetDate())
13 declare @baseName nvarchar(50)--数据库名称
14 set @baseName=@fromDataBaseName + ‘_bak_‘ + @yearMonthDay --数据库名称为 gpsdb_bak_xx_xx_xx
15 
16 declare @createBase nvarchar(500) --创建数据库用的sql语句
17 set @createBase=‘create database [‘ + @baseName + ‘]
18  on  primary(name=[‘+ @baseName + ‘_Data],filename=‘‘D:\GpsdbBak\‘ + @baseName + ‘_Data.mdf‘‘,size=5mb,maxsize=100mb, filegrowth=15%)
19 log on(name=[‘+ @baseName + ‘_log],filename=‘‘D:\GpsdbBak\‘ + @baseName + ‘_log.ldf‘‘,size=2mb,filegrowth=1mb)‘
20 print @createBase
21 exec (@createBase) --执行创建数据库
22 set @errorCount=@@ERROR
23 if(@errorCount=0)
24 begin
25 use [testdb]  
26    declare test_Cursor  cursor local for --定义一个游标   
27    select name from sysobjects where type = ‘U‘ and --获取所有表名
28    (CHARINDEX(‘gps_position_‘,name)=0 and --带有这个的表不要
29    CHARINDEX(‘buf‘,name)=0 and --临时表不要
30    name <> ‘gps_log‘)  --日志表不要
31    ORDER BY Name   
32    open test_Cursor--打开游标
33    declare @table_name nvarchar(50)
34    fetch next from test_Cursor into @table_name 
35     while @@FETCH_STATUS=0
36      begin 
37       if(@table_name<>‘‘)
38        begin      
39         exec (‘select * into [‘ + @baseName +‘].dbo.‘ + @table_name +‘ from [‘+ @fromDataBaseName +‘].dbo.‘ + @table_name) --关键的一语句代码 
40       fetch next from test_Cursor into @table_name 
41      end
42 close test_Cursor
43 DEALLOCATE test_Cursor
44 end

待完善的问题

不知道同学们发现没有,如果想换需要备份的数据库，则需要改两个地方，注释说明那里的2,3点，这两个其实都是同一个数据库名，下面的那个 use testdb,不知道能不能像执行sql语句一样 exec (‘use testdb‘),如果可以的话那就换数据库的话改下 set @fromDataBaseName=‘testdb‘ 这句就O了。