昨天有个同事在客户的服务器上面弄数据库,不小心执行了一条 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 @createBase14 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=014 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 end20 close test_Cursor21 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_xx15 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 @createBase21 exec (@createBase) --执行创建数据库22 set @errorCount=@@ERROR23 if(@errorCount=0)24 begin25 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=036 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 end42 close test_Cursor43 DEALLOCATE test_Cursor44 end待完善的问题
不知道同学们发现没有,如果想换需要备份的数据库,则需要改两个地方,注释说明那里的2,3点,这两个其实都是同一个数据库名,下面的那个 use testdb,不知道能不能像执行sql语句一样 exec ('use testdb'),如果可以的话那就换数据库的话改下set @fromDataBaseName='testdb' 这句就O了。
作业接下来就是放到作业里面去了,这个直接上图了。
1
2
3
4
最后面就O了。
新闻热点
疑难解答