c#实现sql备份.docx

c实现sql备份 非常简单 利用T-SQL完成你所要做的就是控制流程 保存变量 分析字符串这种方式感觉实现起来也不错，我是使用这种方式来完成数据库备份还原的功能的。 需要指出下面这些存储过程是在上 学 吧网上搜索到的。谢谢代码的提供者。在此转载 利用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(Ndbo.f_getdbpath) and xtype in (NFN, NIF, NTF) 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(Ndbo.p_backupdb) and OBJECTPROPERTY(id, NIsProcedure) = 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(Ndbo.p_RestoreDb) and OBJECTPROPERTY(id, NIsProcedure) = 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(Ndbo.p_createjob) and OBJECTPROPERTY(id, NIsProcedure) = 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=job