MSSQL网页编程之数据库备份/恢复计划

Federated将不同的Mysql服务器联合起来，逻辑上组成一个完整的数据库。非常适合分布式应用备份|恢复|数据|数据库
使用SQL言语,完成数据库的备份/恢复的功效

表现了SQLServer中的四个常识点：

1．猎取SQLServer服务器上的默许目次

2．备份SQL语句的利用

3．恢复SQL语句的利用，同时思索了强迫恢复时封闭其他用户历程的处置

4．功课创立SQL语句的利用



/*1.--失掉数据库的文件目次

@dbname指定要获得目次的数据库名
假如指定的数据不存在,前往安装SQL时设置的默许数据目次
假如指定NULL,则前往默许的SQL备份目次名
----*/

/*--挪用示例
select数据库文件目次=dbo.f_getdbpath(tempdb)
,[默许SQLSERVER数据目次]=dbo.f_getdbpath()
,[默许SQLSERVER备份目次]=dbo.f_getdbpath(null)
--*/
ifexists(select*fromdbo.sysobjectswhereid=object_id(N[dbo].[f_getdbpath])andxtypein(NFN,NIF,NTF))
dropfunction[dbo].[f_getdbpath]
GO

createfunctionf_getdbpath(@dbnamesysname)
returnsnvarchar(260)
as
begin
declare@renvarchar(260)
if@dbnameisnullordb_id(@dbname)isnull
select@re=rtrim(reverse(filename))frommaster..sysdatabaseswherename=master
else
select@re=rtrim(reverse(filename))frommaster..sysdatabaseswherename=@dbname

if@dbnameisnull
set@re=reverse(substring(@re,charindex(,@re)+5,260))+BACKUP
else
set@re=reverse(substring(@re,charindex(,@re),260))
return(@re)
end
go



/*2.--备份数据库--*/

/*--挪用示例

--备份以后数据库
execp_backupdb@bkpath=c:,@bkfname=db_DATE\_db.bak

--差别备份以后数据库
execp_backupdb@bkpath=c:,@bkfname=db_DATE\_df.bak,@bktype=DF

--备份以后数据库日记
execp_backupdb@bkpath=c:,@bkfname=db_DATE\_log.bak,@bktype=LOG

--*/

ifexists(select*fromdbo.sysobjectswhereid=object_id(N[dbo].[p_backupdb])andOBJECTPROPERTY(id,NIsProcedure)=1)
dropprocedure[dbo].[p_backupdb]
GO

createprocp_backupdb
@dbnamesysname=,--要备份的数据库称号,不指定章备份以后数据库
@bkpathnvarchar(260)=,--备份文件的寄存目次,不指定章利用SQL默许的备份目次
@bkfnamenvarchar(260)=,--备份文件名,文件名中能够用DBNAME代表数据库名,DATE代表日期,TIME代表工夫
@bktypenvarchar(10)=DB,--备份范例:DB备份数据库,DF差别备份,LOG日记备份
@appendfilebit=1--追加/掩盖备份文件
as
declare@sqlvarchar(8000)
ifisnull(@dbname,)=set@dbname=db_name()
ifisnull(@bkpath,)=set@bkpath=dbo.f_getdbpath(null)
ifisnull(@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@bktypewhenLOGthenlogelsedatabaseend+@dbname
+todisk=+@bkpath+@bkfname
+with+case@bktypewhenDFthenDIFFERENTIAL,elseend
+case@appendfilewhen1thenNOINITelseINITend
print@sql
exec(@sql)
go



/*3.--恢单数据库--*/

/*--挪用示例
--完全恢单数据库
execp_RestoreDb@bkfile=c:db_20031015_db.bak,@dbname=db

--差别备份恢复
execp_RestoreDb@bkfile=c:db_20031015_db.bak,@dbname=db,@retype=DBNOR
execp_backupdb@bkfile=c:db_20031015_df.bak,@dbname=db,@retype=DF

--日记备份恢复
execp_RestoreDb@bkfile=c:db_20031015_db.bak,@dbname=db,@retype=DBNOR
execp_backupdb@bkfile=c:db_20031015_log.bak,@dbname=db,@retype=LOG

--*/

ifexists(select*fromdbo.sysobjectswhereid=object_id(N[dbo].[p_RestoreDb])andOBJECTPROPERTY(id,NIsProcedure)=1)
dropprocedure[dbo].[p_RestoreDb]
GO

createprocp_RestoreDb
@bkfilenvarchar(1000),--界说要恢复的备份文件名
@dbnamesysname=,--界说恢复后的数据库名,默许为备份的文件名
@dbpathnvarchar(260)=,--恢复后的数据库寄存目次,不指定章为SQL的默许数据目次
@retypenvarchar(10)=DB,--恢复范例:DB完事恢单数据库,DBNOR为差别恢复,日记恢复举行完全恢复,DF差别备份的恢复,LOG日记恢复
@filenumberint=1,--恢复的文件号
@overexistbit=1,--是不是掩盖已存在的数据库,仅@retype为
@killuserbit=1--是不是封闭用户利用历程,仅@overexist=1时无效
as
declare@sqlvarchar(8000)

--失掉恢复后的数据库名
ifisnull(@dbname,)=
select@sql=reverse(@bkfile)
,@sql=casewhencharindex(.,@sql)=0then@sql
elsesubstring(@sql,charindex(.,@sql)+1,1000)end
,@sql=casewhencharindex(,@sql)=0then@sql
elseleft(@sql,charindex(,@sql)-1)end
,@dbname=reverse(@sql)

--失掉恢复后的数据库寄存目次
ifisnull(@dbpath,)=set@dbpath=dbo.f_getdbpath()

--天生数据库恢复语句
set@sql=restore+case@retypewhenLOGthenlogelsedatabaseend+@dbname
+fromdisk=+@bkfile+
+withfile=+cast(@filenumberasvarchar)
+casewhen@overexist=1and@retypein(DB,DBNOR)then,replaceelseend
+case@retypewhenDBNORthen,NORECOVERYelse,RECOVERYend
print@sql
--增加挪动逻辑文件的处置
if@retype=DBor@retype=DBNOR
begin
--从备份文件中猎取逻辑文件名
declare@lfnnvarchar(128),@tpchar(1),@iint

--创立一时表,保留猎取的信息
createtable#tb(lnnvarchar(128),pnnvarchar(260),tpchar(1),fgnnvarchar(128),sznumeric(20,0),Msznumeric(20,0))
--从备份文件中猎取信息
insertinto#tbexec(restorefilelistonlyfromdisk=+@bkfile+)
declare#fcursorforselectln,tpfrom#tb
open#f
fetchnextfrom#finto@lfn,@tp
set@i=0
while@@fetch_status=0
begin
select@sql=@sql+,move+@lfn+to+@dbpath+@dbname+cast(@iasvarchar)
+case@tpwhenDthen.mdfelse.ldfend
,@i=@i+1
fetchnextfrom#finto@lfn,@tp
end
close#f
deallocate#f
end

--封闭用户历程处置
if@overexist=1and@killuser=1
begin
declare@spidvarchar(20)
declare#spidcursorfor
selectspid=cast(spidasvarchar(20))frommaster..sysprocesseswheredbid=db_id(@dbname)
open#spid
fetchnextfrom#spidinto@spid
while@@fetch_status=0
begin
exec(kill+@spid)
fetchnextfrom#spidinto@spid
end
close#spid
deallocate#spid
end

--恢单数据库
exec(@sql)

go

/*4.--创立功课--*/

/*--挪用示例

--每个月实行的功课
execp_createjob@jobname=mm,@sql=select*fromsyscolumns,@freqtype=month

--每周实行的功课
execp_createjob@jobname=ww,@sql=select*fromsyscolumns,@freqtype=week

--逐日实行的功课
execp_createjob@jobname=a,@sql=select*fromsyscolumns

--逐日实行的功课,天天隔4小时反复的功课
execp_createjob@jobname=b,@sql=select*fromsyscolumns,@fsinterval=4

--*/
ifexists(select*fromdbo.sysobjectswhereid=object_id(N[dbo].[p_createjob])andOBJECTPROPERTY(id,NIsProcedure)=1)
dropprocedure[dbo].[p_createjob]
GO

createprocp_createjob
@jobnamevarchar(100),--功课称号
@sqlvarchar(8000),--要实行的命令
@dbnamesysname=,--默许为以后的数据库名
@freqtypevarchar(6)=day,--工夫周期,month月,week周,day日
@fsintervalint=1,--相对逐日的反复次数
@timeint=170000--入手下手实行工夫,关于反复实行的功课,将从0点到23:59分
as
ifisnull(@dbname,)=set@dbname=db_name()

--创立功课
execmsdb..sp_add_job@job_name=@jobname

--创立功课步骤
execmsdb..sp_add_jobstep@job_name=@jobname,
@step_name=数据处置,
@subsystem=TSQL,
@database_name=@dbname,
@command=@sql,
@retry_attempts=5,--重试次数
@retry_interval=5--重试距离

--创立调剂
declare@ftypeint,@fstypeint,@ffactorint
select@ftype=case@freqtypewhendaythen4
whenweekthen8
whenmonththen16end
,@fstype=case@fsintervalwhen1then0else8end
if@fsinterval1set@time=0
set@ffactor=case@freqtypewhendaythen0else1end

EXECmsdb..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@sqlvarchar(8000)
--完全备份（每一个礼拜天一次）
set@sql=execp_backupdb@dbname=要备份的数据库名
execp_createjob@jobname=每周备份,@sql,@freqtype=week

--差别备份（天天备份一次）
set@sql=execp_backupdb@dbname=要备份的数据库名,@bktype=DF
execp_createjob@jobname=天天差别备份,@sql,@freqtype=day

--日记备份（每2小时备份一次）
set@sql=execp_backupdb@dbname=要备份的数据库名,@bktype=LOG
execp_createjob@jobname=每2小光阴志备份,@sql,@freqtype=day,@fsinterval=2

*--使用案例2

临盆数据中心库：PRODUCE

备份计划以下：
1.设置三个功课,分离对PRODUCE库举行逐日备份,每周备份,每个月备份
2.新建三个新库,分离定名为:逐日备份,每周备份,每个月备份
3.创建三个功课，分离把三个备份库复原到以上的三个新库。

目标:当用户在produce库中有任何的数据丧失时,都可以从下面的三个备份库中导进响应的TABLE数据。
--*/

declare@sqlvarchar(8000)

--1.创建每个月备份和天生月备份数据库的功课,每个月每1世界午16:40分举行:
set@sql=
declare@pathnvarchar(260),@fnamenvarchar(100)
set@fname=PRODUCE_+convert(varchar(10),getdate(),112)+_m.bak
set@path=dbo.f_getdbpath(null)+@fname

--备份
execp_backupdb@dbname=PRODUCE,@bkfname=@fname

--依据备份天生每个月新库
execp_RestoreDb@bkfile=@path,@dbname=PRODUCE_月

--为周数据库恢复筹办基本数据库
execp_RestoreDb@bkfile=@path,@dbname=PRODUCE_周,@retype=DBNOR

--为日数据库恢复筹办基本数据库
execp_RestoreDb@bkfile=@path,@dbname=PRODUCE_日,@retype=DBNOR

execp_createjob@jobname=每个月备份,@sql,@freqtype=month,@time=164000

--2.创建每周差别备份和天生周备份数据库的功课,每周日下战书17:00分举行:
set@sql=
declare@pathnvarchar(260),@fnamenvarchar(100)
set@fname=PRODUCE_+convert(varchar(10),getdate(),112)+_w.bak
set@path=dbo.f_getdbpath(null)+@fname

--差别备份
execp_backupdb@dbname=PRODUCE,@bkfname=@fname,@bktype=DF

--差别恢复周数据库
execp_backupdb@bkfile=@path,@dbname=PRODUCE_周,@retype=DF

execp_createjob@jobname=每周差别备份,@sql,@freqtype=week,@time=170000

--3.创建逐日日记备份和天生日备份数据库的功课,每周日下战书17:15分举行:
set@sql=
declare@pathnvarchar(260),@fnamenvarchar(100)
set@fname=PRODUCE_+convert(varchar(10),getdate(),112)+_l.bak
set@path=dbo.f_getdbpath(null)+@fname

--日记备份
execp_backupdb@dbname=PRODUCE,@bkfname=@fname,@bktype=LOG

--日记恢复日数据库
execp_backupdb@bkfile=@path,@dbname=PRODUCE_日,@retype=LOG

execp_createjob@jobname=每周差别备份,@sql,@freqtype=day,@time=171500




更多的信息参考我在CSDN上宣布的贴子:

http://expert.csdn.net/Expert/topic/2359/2359124.xml?temp=.7861292
每个Rows_log_event中包含event_type,可选值为WRITE_ROWS_EVENT、UPDATE_ROWS_EVENT、DELETE_ROWS_EVENT。从宏名字就能看出用途。

我们学到了什么？思考问题的时候从表的角度来思考问

如安全管理、备份恢复、性能监控和调优等，SQL只要熟悉基本操作就可以，只要程序设计部分只要稍加了解即可（如存储过程、触发器等）。

理解了存储结构，再阅读下性能优化的章节基本上会对sqlserver有个清晰地认识

不好!如果出了错;不好调试;不好处理!其实web开发将代码分为3层:web层;业务逻辑层和数据访问层;一般对数据库的操作都在数据访问层来做;这样便于调试和维护!而且将来如果是换了数据库的话;你只需要改数据层的代码;其他层的基本可以不变!要是你在jsp中直接调用sql数据库;那么如果换了数据库呢?岂不都要改?如果报了异常呢?怎么做异常处理?

对一张百万级别的表建游标，同时又没有什么过滤条件，取得游标效率是如果直接SQL查询百万条数据；如果再对每条记录做处理，耗时将更长。

所以你总能得到相应的升级版本，来满足你的需求。

也可谈一下你是怎么优化存储过程的?