用命令备份数据

这种方式感觉实现起来也不错 我是使用这种方式来完成数据库备份还原的功能的 需要指出下面这些存储过程是在网上搜索到的 谢谢代码的提供者 在此转载 利用 T SQL 语句 实现数据库的备份与还原的功能 体现了 SQL Server 中的四个知识点 1 获取 SQL Server 服务器上的默认目录 2 备份 SQL 语句的使用 3 恢复 SQL 语句的使用 同时考虑了强制恢复时关闭其他用户进程的处理 4 作业创建 SQL 语句的使用 1 得到数据库的文件目录 dbname 指定要取得目录的数据库名 如果指定的数据不存在 返回安装 SQL 时设置的默认数据目录 如果指定 NULL 则返回默认的 SQL 备份目录名 调用示例 select 数据库文件目录 dbo f getdbpath tempdb 默认 SQL SERVER 数据目录 dbo f getdbpath 默认 SQL SERVER 备份目录 dbo f getdbpath null if exists select from dbo sysobjects where id object id N dbo f getdbpath and xtype in N FN N IF N TF drop function dbo f getdbpath GO create function f getdbpath dbname sysname returns nvarchar 260 as begin declare re nvarchar 260 if dbname is null or db id dbname is null select re rtrim reverse filename from master sysdatabases where name master else select re rtrim reverse filename from master sysdatabases where name dbname if dbname is null set re reverse substring re charindex re 5 260 BACKUP else set re reverse substring re charindex re 260 return re end go 2 备份数据库 调用示例 备份当前数据库 exec p backupdb bkpath c bkfname db DATE db bak 差异备份当前数据库 exec p backupdb bkpath c bkfname db DATE df bak bktype DF 备份当前数据库日志 exec p backupdb bkpath c bkfname db DATE log bak bktype LOG if exists select from dbo sysobjects where id object id N dbo p backupdb and OBJECTPROPERTY id N IsProcedure 1 drop procedure dbo p backupdb GO create proc p backupdb dbname sysname 要备份的数据库名称 不指定则备份当前数据库 bkpath nvarchar 260 备份文件的存放目录 不指定则使用 SQL 默认的备份目录 bkfname nvarchar 260 备份文件名 文件名中可以用 DBNAME 代表数据库名 DATE 代表日期 TIME 代表时间 bktype nvarchar 10 DB 备份类型 DB 备份数据库 DF 差异备份 LOG 日志备 份 appendfile bit 1 追加 覆盖备份文件 as declare sql varchar 8000 if isnull dbname set dbname db name if isnull bkpath set bkpath dbo f getdbpath null if isnull bkfname set bkfname DBNAME DATE TIME BAK set bkfname replace replace replace bkfname DBNAME dbname DATE convert varchar getdate 112 TIME replace convert varchar getdate 108 set sql backup case bktype when LOG then log else database end dbname to disk bkpath bkfname with case bktype when DF then DIFFERENTIAL else end case appendfile when 1 then NOINIT else INIT end print sql exec sql go 3 恢复数据库 调用示例 完整恢复数据库 exec p RestoreDb bkfile c db 20031015 db bak dbname db 差异备份恢复 exec p RestoreDb bkfile c db 20031015 db bak dbname db retype DBNOR exec p backupdb bkfile c db 20031015 df bak dbname db retype DF 日志备份恢复 exec p RestoreDb bkfile c db 20031015 db bak dbname db retype DBNOR exec p backupdb bkfile c db 20031015 log bak dbname db retype LOG if exists select from dbo sysobjects where id object id N dbo p RestoreDb and OBJECTPROPERTY id N IsProcedure 1 drop procedure dbo p RestoreDb GO create proc p RestoreDb bkfile nvarchar 1000 定义要恢复的备份文件名 dbname sysname 定义恢复后的数据库名 默认为备份的文件名 dbpath nvarchar 260 恢复后的数据库存放目录 不指定则为 SQL 的默认数据目录 retype nvarchar 10 DB 恢复类型 DB 完事恢复数据库 DBNOR 为差异恢复 日 志恢复进行完整恢复 DF 差异备份的恢复 LOG 日志恢复 filenumber int 1 恢复的文件号 overexist bit 1 是否覆盖已经存在的数据库 仅 retype 为 killuser bit 1 是否关闭用户使用进程 仅 overexist 1 时有效 as declare sql varchar 8000 得到恢复后的数据库名 if isnull dbname select sql reverse bkfile sql case when charindex sql 0 then sql else substring sql charindex sql 1 1000 end sql case when charindex sql 0 then sql else left sql charindex sql 1 end dbname reverse sql 得到恢复后的数据库存放目录 if isnull dbpath set dbpath dbo f getdbpath 生成数据库恢复语句 set sql restore case retype when LOG then log else database end dbname from disk bkfile with file cast filenumber as varchar case when overexist 1 and retype in DB DBNOR then replace else end case retype when DBNOR then NORECOVERY else RECOVERY end print sql 添加移动逻辑文件的处理 if retype DB or retype DBNOR begin 从备份文件中获取逻辑文件名 declare lfn nvarchar 128 tp char 1 i int 创建临时表 保存获取的信息 create table tb ln nvarchar 128 pn nvarchar 260 tp char 1 fgn nvarchar 128 sz numeric 20 0 Msz numeric 20 0 从备份文件中获取信息 insert into tb exec restore filelistonly from disk bkfile declare f cursor for select ln tp from tb open f fetch next from f into lfn tp set i 0 while fetch status 0 begin select sql sql move lfn to dbpath dbname cast i as varchar case tp when D then mdf else ldf end i i 1 fetch next from f into lfn tp end close f deallocate f end 关闭用户进程处理 if overexist 1 and killuser 1 begin declare spid varchar 20 declare spid cursor for select spid cast spid as varchar 20 from master sysprocesses where dbid db id dbname open spid fetch next from spid into spid while fetch status 0 begin exec kill spid fetch next from spid into spid end close spid deallocate spid end 恢复数据库 exec sql go 4 创建作业 调用示例 每月执行的作业 exec p createjob jobname mm sql select from syscolumns freqtype month 每周执行的作业 exec p createjob jobname ww sql select from syscolumns freqtype week 每日执行的作业 exec p createjob jobname a sql select from syscolumns 每日执行的作业 每天隔 4 小时重复的作业 exec p createjob jobname b sql select from syscolumns fsinterval 4 if exists select from dbo sysobjects where id object id N dbo p createjob and OBJECTPROPERTY id N IsProcedure 1 drop procedure dbo p createjob GO create proc p createjob jobname varchar 100 作业名称 sql varchar 8000 要执行的命令 dbname sysname 默认为当前的数据库名 freqtype varchar 6 day 时间周期 month 月 week 周 day 日 fsinterval int 1 相对于每日的重复次数 time int 170000 开始执行时间 对于重复执行的作业 将从 0 点到 23 59 分 as if isnull dbname set dbname db name 创建作业 exec msdb sp add job job name jobname 创建作业步骤 exec msdb sp add jobstep job name jobname step name 数据处理 subsystem TSQL database name dbname command sql retry attempts 5 重试次数 retry interval 5 重试间隔 创建调度 declare ftype int fstype int ffactor int select ftype case freqtype when day then 4 when week then 8 when month then 16 end fstype case fsinterval when 1 then 0 else 8 end if fsinterval1 set time 0 set ffactor case freqtype when day then 0 else 1 end EXEC msdb sp add jobschedule job name jobname name 时间安排 freq type ftype 每天 8 每周 16 每月 freq interval 1 重复执行次数 freq subday type fstype 是否重复执行 freq subday interval fsinterval 重复周期 freq recurrence factor ffactor active start time time 下午 17 00 00 分执行 go 应用案例 备份方案 完整备份 每个星期天一次 差异备份 每天备份一次 日志备份 每 2 小时备份一次 调用上面的存储过程来实现 declare sql varchar 8000 完整备份 每个星期天一次 set sql exec p backupdb dbname 要备份的数据库名 exec p createjob jobname 每周备份 sql freqtype week 差异备份 每天备份一次 set sql exec p backupdb dbname 要备份的数据库名 bktype DF exec p createjob jobname 每天差异备份 sql freqtype day 日志备份 每 2 小时备份一次 set sql exec p backupdb dbname 要备份的数据库名 bktype LOG exec p createjob jobname 每 2 小时日志备份 sql freqtype day fsinterval 2 应用案例 2 生产数据核心库 PRODUCE 备份方案如下 1 设置三个作业 分别对 PRODUCE 库进行每日备份 每周备份 每月备份 2 新建三个新库 分别命名为 每日备份 每周备份 每月备份 3 建立三个作业 分别把三个备份库还原到以上的三个新库 目的 当用户在 produce 库中有任何的数据丢失时 均可以从上面的三个备份库中导入相应 的 TABLE 数据 declare sql varchar 8000 1 建立每月备份和生成月备份数据库的作业 每月每 1 天下午 16 40 分进行 set sql declare path nvarchar 260 fname nvarchar 100 set fname PRODUCE convert varchar 10 getdate 112 m bak set path dbo f getdbpath null fname 备份 exec p backupdb dbname PRODUCE bkfname fname 根据备份生成每月新库 exec p RestoreDb bkfile path dbname PRODUCE 月 为周数据库恢复准备基础数据库 exec p RestoreDb bkfile path dbname PRODUCE 周 retype DBNOR 为日数据库恢复准备基础数据库 exec p RestoreDb bkfile path dbname PRODUCE 日 retype DBNOR exec p createjob jobnam