仓酷云

标题: 使用SQL对数据库举行备份 [打印本页]

---

作者: 逍遥一派    时间: 2015-1-16 14:09
标题: 使用SQL对数据库举行备份
有了rowbase的binlog后，我们来分析一下怎么实现闪回。平时的DML无非三种操作，增删改，先说三种操作的日志格式。使用T-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
mysqld进程在一个写入当中被杀死;计算机的意外关闭(例如，如果计算机掉电);一个硬件错误。

---

作者: 兰色精灵    时间: 2015-1-18 12:17
不过话说回来了，绝大多数的性能优化准则与对sqlserver存储的结构理解息息相关

---

作者: 爱飞    时间: 2015-1-26 16:53
无法深入到数据库系统层面去了解和探究

---

作者: 小女巫    时间: 2015-2-4 20:47
但是随着数据量的增大，这种成本差距会逐渐减小，趋于相等。（500万数量级只相差10%左右）

---

作者: 谁可相欹    时间: 2015-2-10 10:03
索引视图2k就有。但是2005对其效率作了一些改进但是schema.viewname的作用域真是太限制了它的应用面。还有一大堆的环境参数和种种限制都让人对它有点却步。

---

作者: 乐观    时间: 2015-3-1 10:02
varchar(max)\\\\nvarchar(max)类型的引入大大的提高了编程的效率，可以使用字符串函数对CLOB类型进行操作，这是一个亮点。

---

作者: 仓酷云    时间: 2015-3-10 17:01
如果我们从集合论（关系代数）的角度来看，一张数据库的表就是一组数据元的关系，而每个SQL语句会改变一种或数种关系，从而产生出新的数据元的关系（即产生新的表）。

---

作者: 老尸    时间: 2015-3-17 09:11
你觉得我的非分区索引无法对起子分区，你可以提醒我一下呀！没有任何的提醒，直接就变成了非分区表。不知道这算不算一个bug。大家也可以试试。

---

作者: 变相怪杰    时间: 2015-3-24 05:47
学习SQL语言的话如果要学会去做网站就不是很难!但是要做数据库管理的话就有难度了!

---

欢迎光临 仓酷云 (http://ckuyun.com/)

Powered by Discuz! X3.2