首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

关于sql server每天自动备份数据库到指导目录的有关问题?(100分)

2012-06-03 
关于sql server每天自动备份数据库到指导目录的问题?(100分)看了不少文章,但总是看不懂,想问一问,有没有直

关于sql server每天自动备份数据库到指导目录的问题?(100分)
看了不少文章,但总是看不懂,想问一问,有没有直接一段代码,可以让sql server 2005 服务器每天定时自动备份到指导目录。
看在我结贴率100%的份上,希望高手爽快一点,给的代码或方法直接能用。谢谢了!

[解决办法]

SQL code
--建立job每天按時去執行就可以了[test] 你的庫create proc test_backupasBACKUP DATABASE [test] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\test.bak' WITH NOFORMAT, NOINIT,  NAME = N'test-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10go
[解决办法]
SQL code
---2000企业管理器--管理--SQL Server代理--右键作业--新建作业--"常规"项中输入作业名称--"步骤"项--新建--"步骤名"中输入步骤名--"类型"中选择"Transact-SQL 脚本(TSQL)"--"数据库"选择执行命令的数据库--"命令"中输入要执行的语句:                      --确定--"调度"项--新建调度--"名称"中输入调度名称--"调度类型"中选择你的作业执行安排--如果选择"反复出现"--点"更改"来设置你的时间安排为一天一次然后将SQL Agent服务启动,并设置为自动启动,否则你的作业不会被执行设置方法:我的电脑--控制面板--管理工具--服务--右键 SQLSERVERAGENT--属性--启动类型--选择"自动启动"--确定.----------------------------------------  Author : htl258(Tony)--  Date   : 2010-04-29 19:07:45--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) --          Jul  9 2008 14:43:38 --          Copyright (c) 1988-2008 Microsoft Corporation--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)--  Blog   : http://blog.csdn.net/htl258--  Subject: SQL Server 2008 定时作业的制定(SQL2005参考此方法)----------------------------------------SQL Server 2008 定时作业的制定--1.打开【SQL Server Management Studio】,在【对象资源管理器】列表中选择【SQL Server 代理】;--2.鼠标右击【SQL Server 代理】,选择【启动(S)】,如已启动,可以省略此步骤;--3.展开【SQL Server 代理】列表,右击【作业】-->【新建作业】;--3.1  在【常规】选项卡中:--       输入作业名称,如"My Job";--3.2  在【步骤】选项卡中:--3.2.1 点击【新建】,输入【步骤名称】,如“步骤1”,类型默认T-SQL脚本,也可以选择SSIS包等;--3.2.2 在【数据库】一栏选择要作业处理的数据库,在【命令】的右边空白编辑栏输入要执行的SQL代码,     EXEC p_Name  --如:执行一个P_Name的存储过程--      也可以点击命令下面的【打开】,打开.sql脚本;--3.2.3 输入运行脚本后,建议点击【分析】,确保脚本语法正确,然后点击下面的【确定】按钮;--3.3  在【计划】选项卡中:--3.3.1 点击【新建】,输入【计划名称】,如“计划1”,计划类型默认是”重复执行”,也可以选择执行一次等;--3.3.2 在【频率】-->【执行】处选择“每天”、“每周”或“每月”,以“每天”为例,间隔时间输入间隔几天执行一次,--      下面还可以选择每天一次性执行或间隔一定的时间重复执行--3.3.3 在【持续时间】中选择计划开始执行的【起始日期】和【截止日期】,然后点击【确定】按钮;--注意要将服务设置为自动启动,否则在重启服务器后作业就不运行了。--启动代理服务的方法:--开始-->运行-->输入services.msc-->找到【SQL Server 代理】的服务并双击-->【启动类型】选择“自动”-->【确定】--至此,定时作业已创建完毕。本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/htl258/archive/2010/04/29/5543694.aspx
[解决办法]
SQL code
declare @bak_path nvarchar(4000)set @bak_path ='D:\DB_BAK\'       --备份路径;declare @baktype int set @baktype = 0               --备份类型0为全备,1为差异备,2为日志备份declare @type int set @type = 2                  --设置需要备份的库,0为全部库,1为系统库,2为全部用户库,3为指定库,4为排除指定库;declare @dbnames nvarchar(4000)set @dbnames =''        --需要备份或排除的数据库,用,隔开,当@type=3或4时生效declare @overdueDay int set @overdueDay = 1            --设置过期天数,默认天;declare @compression int set @compression =0               --是否采用sql2008的压缩备份,0为否,1为采用压缩--sql server 2005/2008备份/删除过期备份T-sql 版本v1.0/*author:perfectactiondate  :2009.04desc  :适用于sql2005/2008备份,自动生成库文件夹,可以自定义备份类型和备份库名等      可以自定义备份过期的天数              删除过期备份功能不会删除最后一次备份,哪怕已经过期              如果某库不再备份,那么也不会再删除之前过期的备份        如有错误请指正,谢谢.*/set nocount on--开启xp_cmdshell支持exec sp_configure 'show advanced options', 1reconfigure with overrideexec sp_configure 'xp_cmdshell', 1 reconfigure with overrideexec sp_configure 'show advanced options', 0reconfigure with overrideprint char(13)+'------------------------'--判断是否填写路径if isnull(@bak_path,'')=''    begin        print('error:请指定备份路径')        return     end--判断是否指定需要备份的库if isnull(ltrim(@baktype),'')=''    begin        print('error:请指定备份类型aa:0为全备,1为差异备,2为日志备份')        return     endelse    begin        if @baktype not between 0 and 2        begin            print('error:指定备份类型只能为,1,2:  0为全备,1为差异备,2为日志备份')            return         end    end--判断是否指定需要备份的库if isnull(ltrim(@type),'')=''    begin        print('error:请指定需要备份的库,0为全部库,1为系统库,2为全部用户库,3为指定库,4为排除指定库')        return     endelse    begin        if @type not between 0 and 4        begin            print('error:请指定需要备份的库,0为全部库,1为系统库,2为全部用户库,3为指定库,4为排除指定库')            return         end    end--判断指定库或排除库时,是否填写库名if @type>2    if @dbnames=''    begin        print('error:备份类型为'+ltrim(@type)+'时,需要指定@dbnames参数')        return     end--判断指定指定过期时间if isnull(ltrim(@overdueDay),'')=''begin    print('error:必须指定备份过期时间,单位为天,0为永不过期')    return end--判断是否支持压缩if @compression=1     if charindex('2008',@@version)=0 or charindex('Enterprise',@@version)=0    begin        print('error:压缩备份只支持sql2008企业版')        return     end--判断是否存在该磁盘declare @drives table(drive varchar(1),[size] varchar(20))insert into @drives exec('master.dbo.xp_fixeddrives')if not exists(select 1 from @drives where drive=left(@bak_path,1))    begin        print('error:不存在该磁盘:'+left(@bak_path,1))        return     end--格式化参数select @bak_path=rtrim(ltrim(@bak_path)),@dbnames=rtrim(ltrim(@dbnames))if right(isnull(@bak_path,''),1)!='' set @bak_path=@bak_path+''if isnull(@dbnames,'')!='' set @dbnames = ','+@dbnames+','set @dbnames=replace(@dbnames,' ','')--定义变量declare @bak_sql nvarchar(max),@del_sql nvarchar(max),@i int,@maxid intdeclare @dirtree_1 table (id int identity(1,1) primary key,subdirectory nvarchar(600),depth int,files int)declare @dirtree_2 table (id int identity(1,1) primary key,subdirectory nvarchar(600),depth int,files int,dbname varchar(300),baktime datetime,isLastbak int)declare @createfolder nvarchar(max),@delbackupfile nvarchar(max),@delbak nvarchar(max)--获取需要备份的库名--------------------startdeclare @t table(id int identity(1,1) primary key,name nvarchar(max))declare @sql nvarchar(max)set @sql = 'select name from sys.databases where state=0 and name!=''ReportServer'' and name!=''ReportServerTempDB'' and name!=''LiteSpeedLocal'' and name!=''tempdb''  '    + case when @baktype=2 then ' and recovery_model!=3 ' else '' end    + case @type when 0 then 'and 1=1'        when 1 then 'and database_id<=4'        when 2 then 'and database_id>4'        when 3 then 'and charindex('',''+Name+'','','''+@dbnames+''')>0'        when 4 then 'and charindex('',''+Name+'','','''+@dbnames+''')=0 and database_id>4'        else '1>2' endinsert into @t exec(@sql)--获取需要备份的库名---------------------end--获取需要创建的文件夹------------------startinsert into @dirtree_1 exec('master.dbo.xp_dirtree '''+@bak_path+''',0,1')select @createfolder=isnull(@createfolder,'')+'exec master.dbo.xp_cmdshell ''md '+@bak_path+''+name+''',no_output '+char(13)from @t as a left join @dirtree_1 as b on a.name=b.subdirectory and b.files=0 and depth=1 where  b.id is null--获取需要创建的文件夹-------------------end--生成处理过期备份的sql语句-------------startif @overdueDay>0begin    insert into @dirtree_2(subdirectory,depth,files) exec('master.dbo.xp_dirtree '''+@bak_path+''',0,1')    if @baktype =0     delete from @dirtree_2 where depth=1 or files=0 or charindex('_Full_bak_',subdirectory)=0     if @baktype =1     delete from @dirtree_2 where depth=1 or files=0 or charindex('_Diff_bak_',subdirectory)=0     if @baktype=2    delete from @dirtree_2 where depth=1 or files=0 or charindex('_Log_bak_',subdirectory)=0     if exists(select 1 from @dirtree_2)    delete from @dirtree_2 where isdate(            left(right(subdirectory,19),8)+' '+ substring(right(subdirectory,20),11,2) + ':' +              substring(right(subdirectory,20),13,2) +':'+substring(right(subdirectory,20),15,2)             )=0    if exists(select 1 from @dirtree_2)    update @dirtree_2 set dbname = case when @baktype=0 then left(subdirectory,charindex('_Full_bak_',subdirectory)-1)        when @baktype=1 then left(subdirectory,charindex('_Diff_bak_',subdirectory)-1)         when @baktype=2 then left(subdirectory,charindex('_Log_bak_',subdirectory)-1)         else '' end            ,baktime=left(right(subdirectory,19),8)+' '+ substring(right(subdirectory,20),11,2) + ':' +              substring(right(subdirectory,20),13,2) +':'+substring(right(subdirectory,20),15,2)     from @dirtree_2 as a    delete @dirtree_2 from @dirtree_2 as a left join @t as b on b.name=a.dbname where b.id is null    update @dirtree_2 set isLastbak= case when (select max(baktime) from @dirtree_2 where dbname=a.dbname)=baktime     then 1 else 0 end from @dirtree_2 as a    select @delbak=isnull(@delbak,'')+'exec master.dbo.xp_cmdshell ''del '+@bak_path+''+dbname+''+'\'+    +subdirectory+''',no_output '+char(13) from @dirtree_2 where isLastbak=0 and datediff(day,baktime,getdate())>@overdueDay         begin try        --print(@delbak)   --删除超期的备份        exec(@delbak)    --删除超期的备份        end try        begin catch            print 'err:'+ltrim(error_number())            print 'err:'+error_message()        end catch        end--生成处理过期备份的sql语句--------------endbegin try    --print(@createfolder)  --创建备份所需文件夹    exec(@createfolder)   --创建备份所需文件夹end trybegin catch    print 'err:'+ltrim(error_number())    print 'err:'+error_message()    returnend catchselect @i=1 ,@maxid=max(id) from @twhile @i<=@maxidbegin    select @bak_sql=''+char(13)+'backup '+ case when @baktype=2 then 'log ' else 'database ' end            +quotename(Name)+' to disk='''+@bak_path + Name+'\'+            Name+ case when @baktype=0 then '_Full_bak_' when @baktype=1 then '_Diff_bak_'             when @baktype=2 then '_Log_bak_' else null end + case when @compression=1 then 'compression_' else '' end+            replace(replace(replace(convert(varchar(20),getdate(),120),'-',''),' ','_'),':','')+            case when @baktype=2 then '.trn' when @baktype=1 then '.dif' else '.bak' end +''''             + case when @compression=1 or @baktype=1 then ' with ' else '' end            + case when @compression=1 then 'compression,' else '' end            + case when @baktype=1 then 'differential,' else '' end            + case when @compression=1 or @baktype=1 then ' noformat' else '' end     from @t where id=@i    set @i=@i+1    begin try        --print(@bak_sql)--循环执行备份        exec(@bak_sql) --循环执行备份    end try    begin catch        print 'err:'+ltrim(error_number())        print 'err:'+error_message()    end catchendbegin try    --print(@delbak)   --删除超期的备份    exec(@delbak)    --删除超期的备份end trybegin catch    print 'err:'+ltrim(error_number())    print 'err:'+error_message()end catch--关闭xp_cmdshell支持exec sp_configure 'show advanced options', 1reconfigure with overrideexec sp_configure 'xp_cmdshell', 1 reconfigure with overrideexec sp_configure 'show advanced options', 0reconfigure with override---这是我修改某位 “大牛”后的代码。在SQL SERVER 2005 上可以直接使用 


[解决办法]
楼上 好样的!!!
[解决办法]

SQL code
--呵呵,你没建过job?例子:@database_name=N'test', 换成你的数据库,此job每天早上1点执行IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'备份测试')EXEC msdb.dbo.sp_delete_job @job_name=N'备份测试', @delete_unused_schedule=1goBEGIN TRANSACTIONDECLARE @ReturnCode INTSELECT @ReturnCode = 0/****** 对象:  JobCategory [Database Engine Tuning Advisor]    脚本日期: 06/01/2012 10:39:32 ******/IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Engine Tuning Advisor' AND category_class=1)BEGINEXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Engine Tuning Advisor'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackENDDECLARE @jobId BINARY(16)EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'备份测试',         @enabled=1,         @notify_level_eventlog=0,         @notify_level_email=0,         @notify_level_netsend=0,         @notify_level_page=0,         @delete_level=0,         @description=N'无描述。',         @category_name=N'Database Engine Tuning Advisor',         @owner_login_name=N'sa', @job_id = @jobId OUTPUTIF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback/****** 对象:  Step [备份数据]    脚本日期: 06/01/2012 10:39:33 ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'备份数据',         @step_id=1,         @cmdexec_success_code=0,         @on_success_action=1,         @on_success_step_id=0,         @on_fail_action=2,         @on_fail_step_id=0,         @retry_attempts=0,         @retry_interval=0,         @os_run_priority=0, @subsystem=N'TSQL',         @command=N'exec [test_backup]',         @database_name=N'test',         @flags=0IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'早上1:00',         @enabled=1,         @freq_type=4,         @freq_interval=1,         @freq_subday_type=1,         @freq_subday_interval=0,         @freq_relative_interval=0,         @freq_recurrence_factor=0,         @active_start_date=20120601,         @active_end_date=99991231,         @active_start_time=0,         @active_end_time=235959IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback:    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave:go 

热点排行