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

--建立job每天按時去執行就可以了[test] 你的庫
create proc test_backup
as
BACKUP 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 = 10
go

------解决方案--------------------

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.****.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 代理】的服务并双击-->【启动类型】选择“自动”-->【确定】

--至此，定时作业已创建完毕。



本文来自****博客，转载请标明出处：http://blog.****.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:perfectaction
date  :2009.04
desc  :适用于sql2005/2008备份，自动生成库文件夹，可以自定义备份类型和备份库名等
      可以自定义备份过期的天数
              删除过期备份功能不会删除最后一次备份，哪怕已经过期
              如果某库不再备份，那么也不会再删除之前过期的备份 
       如有错误请指正，谢谢.
*/

set nocount on
--开启xp_cmdshell支持
exec sp_configure 'show advanced options', 1
reconfigure with override
exec sp_configure 'xp_cmdshell', 1 
reconfigure with override
exec sp_configure 'show advanced options', 0
reconfigure with override
print char(13)+'------------------------'

--判断是否填写路径
if isnull(@bak_path,'')=''
    begin
        print('error:请指定备份路径')
        return 
    end

--判断是否指定需要备份的库
if isnull(ltrim(@baktype),'')=''
    begin
        print('error:请指定备份类型aa:0为全备,1为差异备,2为日志备份')
        return 
    end
else
    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 
    end
else
    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 int
declare @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)

--获取需要备份的库名--------------------start
declare @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' end
insert into @t exec(@sql)
--获取需要备份的库名---------------------end

--获取需要创建的文件夹------------------start
insert 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语句-------------start
if @overdueDay>0
begin
    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语句--------------end


begin try
    --print(@createfolder)  --创建备份所需文件夹
    exec(@createfolder)   --创建备份所需文件夹
end try
begin catch
    print 'err:'+ltrim(error_number())
    print 'err:'+error_message()
    return
end catch


select @i=1 ,@maxid=max(id) from @t
while @i<=@maxid
begin
    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 catch
end

begin try
    --print(@delbak)   --删除超期的备份
    exec(@delbak)    --删除超期的备份
end try
begin catch
    print 'err:'+ltrim(error_number())
    print 'err:'+error_message()
end catch



--关闭xp_cmdshell支持
exec sp_configure 'show advanced options', 1
reconfigure with override
exec sp_configure 'xp_cmdshell', 1 
reconfigure with override
exec sp_configure 'show advanced options', 0
reconfigure with override


---这是我修改某位 “大牛”后的代码。在SQL SERVER 2005 上可以直接使用