ms sql2008 数据库备份(收集)

--完整备份

backup database northwindcs
to disk='g:\backup\northwindcs_full_20070908.bak'

--差异备份
backup database northwindcs
to disk='g:\backup\northwindcs_diff_20070908.bak'
with differential

--日志备份，默认截断日志
backup log northwindcs
to disk='g:\backup\northwindcs_log_20070908.bak'

--日志备份，不截断日志
backup log northwindcs
to disk='g:\backup\northwindcs_log_20070908.bak'
with no_truncate

--截断日志不保留
backup log northwindcs
with no_log

--或者
backup log northwindcs
with truncate_only
--截断之后日志文件不会变小
--有必要可以进行收缩

--文件备份
exec sp_helpdb northwindcs --查看数据文件
backup database northwindcs
file='northwindcs'   --数据文件的逻辑名
to disk='g:\backup\northwindcs_file_20070908.bak'

--文件组备份
exec sp_helpdb northwindcs --查看数据文件
backup database northwindcs
filegroup='primary'   --数据文件的逻辑名
to disk='g:\backup\northwindcs_filegroup_20070908.bak'
with init

--分割备份到多个目标
--恢复的时候不允许丢失任何一个目标
backup database northwindcs
to disk='g:\backup\northwindcs_full_1.bak'
     ,disk='g:\backup\northwindcs_full_2.bak'

--镜像备份
--每个目标都是相同的
backup database northwindcs
to disk='g:\backup\northwindcs_mirror_1.bak'
mirror 
to disk='g:\backup\northwindcs_mirror_2.bak'
with format --第一次做镜像备份的时候格式化目标

--镜像备份到本地和远程
backup database northwindcs
to disk='g:\backup\northwindcs_mirror_1.bak'
mirror 
to disk='\\192.168.1.200\backup\northwindcs_mirror_2.bak'
with format

--每天生成一个备份文件
declare @path nvarchar(2000)
set @path ='g:\backup\northwindcs_full_'
+convert(nvarchar,getdate(),112)+'.bak'

backup database northwindcs
to disk=@path

--从norecovery或者
--standby模式恢复数据库为可用
restore database northwindcs_bak
with recovery

--查看目标备份中的备份集
restore headeronly
from disk ='g:\backup\northwindcs_full_20070908.bak'

--查看目标备份的第一个备份集的信息
restore filelistonly
from disk ='g:\backup\northwindcs_full_20070908_2.bak'
with file=1

--查看目标备份的卷标
restore labelonly
from disk ='g:\backup\northwindcs_full_20070908_2.bak'

--备份设置密码保护备份
backup database northwindcs
to disk='g:\backup\northwindcs_full_20070908.bak'
with password = '123',init

restore database northwindcs
from disk='g:\backup\northwindcs_full_20070908.bak'
with password = '123'

在sql server 2005数据库中实现自动备份的具体步骤: 
1、打开sql server management studio

2、启动sql server代理

3、点击作业->新建作业

4、"常规"中输入作业的名称

5、新建步骤，类型选t-sql，在下面的命令中输入下面语句

declare @strpath nvarchar(200)
set @strpath = convert(nvarchar(19),getdate(),120)
set @strpath = replace(@strpath, ':' , '.')
set @strpath = 'd:\bak\' + mailto:%20databasename%20+@strpath + '.bak'
backup database [databasename] to disk = @strpath with noinit , nounload , noskip , stats = 10, noformat

（d:\bak\改为自己的备份路径，databasename修改为想备份的数据库的名称）

6、添加计划，设置频率，时间等。

确定，完成。

函数部分：
create function [dbo].[fun_gettime] (@taskphaseid int)
returns float as
begin
declare @taskid int,
          @hour float,
          @percent float,
          @return float
if @taskphaseid is null
begin
    return(0.0)
end

select @taskid=taskid,@percent=isnull(workpercent,0)/100
from tabletaskphase
where id=@taskphaseid

select @hour=isnull(tasktime,0) from tabletask
where id=@taskid

set @return=@hour*@percent
return (@return)
end

调用函数的存储过程部分
create procedure [dbo].[proc_calcca]
@roid int
as
begin
declare @ca float

update tablefmeca
set
cvalue_m=    isnull(moderate,0)*isnull(fmerate,0)*isnull(b.basfailurerate,0)*[dbo].[fun_gettime](c.id)
from tablefmeca ,tablerelation b,tabletaskphase c
where roid=@roid and taskphaseid=c.id and b.id=@roid

select @ca=sum(isnull(cvalue_m,0)) from tablefmeca where roid=@roid

update tablerelation
set criticality=@ca
where id=@roid
end
go

2． 我们要根据某表的某些记录，先计算后求和，因为无法存储中间值，平时我们也用游标的方法进行计算。但sqlserver2000里支持
sum ( [ all distinct ] expression )

expression

是常量、列或函数，或者是算术、按位与字符串等运算符的任意组合。因此我们可以利用这一功能。

函数部分：

create function [dbo].[fun_rate] (@partid int,@enid int,@sourceid int, @qualityid int,@count int)

returns float as
begin
declare @qxs float, @g float, @rate float

if (@enid=null) or (@partid=null) or (@sourceid=null) or (@qualityid=null)
begin
    return(0.0)
end

select @qxs= isnull(xs,0) from tablequality where id=@qualityid
select @g=isnull(frate_g,0) from tablefailurerate
where (subkindid=@partid) and( enid=@enid) and ( datasourceid=@sourceid) and( ( (isnull(mincount,0)<=isnull(@count,0)) and ( isnull(maxcount,0)>=isnull(@count,0)))
or(isnull(@count,0)>isnull(maxcount,0)))

set @rate=isnull(@qxs*@g,0)
return (@rate)
end

调用函数的存储过程部分：

create proc proc_faultrate

@partid integer, @qualityid integer, @sourceid integer, @count integer, @roid int, @grade int,@rate float=0 outputas
begin
declare
    @taskid int
    set @rate=0.0

select @taskid=isnull(taskproid,-1) from tablerelation where id=(select pid from tablerelation where id=@roid)

    if (@taskid=-1) or(@grade=1) begin
       set @rate=0
    return
end

    select @rate=sum([dbo].[fun_rate] (@partid,enid,@sourceid, @qualityid,@count) *isnull(workpercent,0)/100.0)

    from tabletaskphase
    where taskid=@taskid
end
go