• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    通过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)
    --
    */
    ifexists (select*from dbo.sysobjects where id =object_id(N'[dbo].[f_getdbpath]') and xtype in (N'FN', N'IF', N'TF'))
    dropfunction[dbo].[f_getdbpath]
    GO

    createfunction f_getdbpath(@dbname sysname)
    returnsnvarchar(260)
    as
    begin
    declare@renvarchar(260)
    if@dbnameisnullordb_id(@dbname) isnull
    select@re=rtrim(reverse(filename)) from master..sysdatabases where name='master'
    else
    select@re=rtrim(reverse(filename)) from master..sysdatabases where name=@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.--备份数据库

    */

    /*--调用示例

    --备份当前数据库
    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'

    --
    */

    ifexists (select*from dbo.sysobjects where id =object_id(N'[dbo].[p_backupdb]') andOBJECTPROPERTY(id, N'IsProcedure') =1)
    dropprocedure[dbo].[p_backupdb]
    GO

    createproc p_backupdb
    @dbname sysname='', --要备份的数据库名称,不指定则备份当前数据库
    @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@bktypewhen'LOG'then'log 'else'database 'end+@dbname
    +' to disk='''+@bkpath+@bkfname
    +''' with '+case@bktypewhen'DF'then'DIFFERENTIAL,'else''end
    +case@appendfilewhen1then'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'

    --
    */

    ifexists (select*from dbo.sysobjects where id =object_id(N'[dbo].[p_RestoreDb]') andOBJECTPROPERTY(id, N'IsProcedure') =1)
    dropprocedure[dbo].[p_RestoreDb]
    GO

    createproc p_RestoreDb
    @bkfilenvarchar(1000), --定义要恢复的备份文件名
    @dbname sysname='', --定义恢复后的数据库名,默认为备份的文件名
    @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@retypewhen'LOG'then'log 'else'database 'end+@dbname
    +' from disk='''+@bkfile+''''
    +' with file='+cast(@filenumberasvarchar)
    +casewhen@overexist=1and@retypein('DB','DBNOR') then',replace'else''end
    +case@retypewhen'DBNOR'then',NORECOVERY'else',RECOVERY'end
    print@sql
    --添加移动逻辑文件的处理
    if@retype='DB'or@retype='DBNOR'
    begin
    --从备份文件中获取逻辑文件名
    declare@lfnnvarchar(128),@tpchar(1),@iint

    --创建临时表,保存获取的信息
    createtable #tb(ln nvarchar(128),pn nvarchar(260),tp char(1),fgn nvarchar(128),sz numeric(20,0),Msz numeric(20,0))
    --从备份文件中获取信息
    insertinto #tb exec('restore filelistonly from disk='''+@bkfile+'''')
    declare #f cursorforselect ln,tp from #tb
    open #f
    fetchnextfrom #f into@lfn,@tp
    set@i=0
    while@@fetch_status=0
    begin
    select@sql=@sql+',move '''+@lfn+''' to '''+@dbpath+@dbname+cast(@iasvarchar)
    +case@tpwhen'D'then'.mdf'''else'.ldf'''end
    ,@i=@i+1
    fetchnextfrom #f into@lfn,@tp
    end
    close #f
    deallocate #f
    end

    --关闭用户进程处理
    if@overexist=1and@killuser=1
    begin
    declare@spidvarchar(20)
    declare #spid cursorfor
    select spid=cast(spid asvarchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
    open #spid
    fetchnextfrom #spid into@spid
    while@@fetch_status=0
    begin
    exec('kill '+@spid)
    fetchnextfrom #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

    --
    */
    ifexists (select*from dbo.sysobjects where id =object_id(N'[dbo].[p_createjob]') andOBJECTPROPERTY(id, N'IsProcedure') =1)
    dropprocedure[dbo].[p_createjob]
    GO

    createproc p_createjob
    @jobnamevarchar(100), --作业名称
    @sqlvarchar(8000), --要执行的命令
    @dbname sysname='', --默认为当前的数据库名
    @freqtypevarchar(6)='day', --时间周期,month 月,week 周,day 日
    @fsintervalint=1, --相对于每日的重复次数
    @timeint=170000--开始执行时间,对于重复执行的作业,将从0点到23:59分
    as
    ifisnull(@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@ftypeint,@fstypeint,@ffactorint
    select@ftype=case@freqtypewhen'day'then4
    when'week'then8
    when'month'then16end
    ,@fstype=case@fsintervalwhen1then0else8end
    if@fsinterval>1set@time=0
    set@ffactor=case@freqtypewhen'day'then0else1end

    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@sqlvarchar(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@sqlvarchar(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 @jobname='每月备份',@sql,@freqtype='month',@time=164000

    --2.建立每周差异备份和生成周备份数据库的作业,每周日下午17:00分进行:
    set@sql='
    declare @path nvarchar(260),@fname nvarchar(100)
    set @fname=
    ''PRODUCE_''+convert(varchar(10),getdate(),112)+''_w.bak''
    set @path=dbo.f_getdbpath(null)+@fname

    --差异备份
    exec p_backupdb @dbname=
    ''PRODUCE'',@bkfname=@fname,@bktype=''DF''

    --差异恢复周数据库
    exec p_backupdb @bkfile=@path,@dbname=
    ''PRODUCE_周'',@retype=''DF''
    '
    exec p_createjob @jobname='每周差异备份',@sql,@freqtype='week',@time=170000

    --3.建立每日日志备份和生成日备份数据库的作业,每周日下午17:15分进行:
    set@sql='
    declare @path nvarchar(260),@fname nvarchar(100)
    set @fname=
    ''PRODUCE_''+convert(varchar(10),getdate(),112)+''_l.bak''
    set @path=dbo.f_getdbpath(null)+@fname

    --日志备份
    exec p_backupdb @dbname=
    ''PRODUCE'',@bkfname=@fname,@bktype=''LOG''

    --日志恢复日数据库
    exec p_backupdb @bkfile=@path,@dbname=
    ''PRODUCE_日'',@retype=''LOG''
    '
    exec p_createjob @jobname='每周差异备份',@sql,@freqtype='day',@time=171500
    您可能感兴趣的文章:
    • sqlserver2005 TSql新功能学习总结(数据类型篇)
    • 如何在SQL Server 2008下轻松调试T-SQL语句和存储过程
    • SQLServer 2008 新增T-SQL 简写语法
    • SQL Server 数据库管理常用的SQL和T-SQL语句
    • SQL Server 数据库管理常用的SQL和T-SQL语句
    • T-SQL中使用正则表达式函数
    • T-SQL篇如何防止SQL注入的解决方法
    • T-SQL 查询语句的执行顺序解析
    • 一些 T-SQL 技巧
    • SQL Server中T-SQL 数据类型转换详解
    上一篇:卸载VS2011 Developer Preview后Sql Server2008 R2建立数据库关系图报“找不到指定的模块”错误的解决方法
    下一篇:mssql存储过程表名和字段名为变量的实现方法
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    通过T-SQL语句实现数据库备份与还原的代码 通过,T-SQL,语句,实现,数据库,