一、数据库备份
第一种方法:利用SQL Server的—数据库维护计划,创建好之后在"作业"列表中,只要SQL代理启动着,它就会按照你的设置定时备份数据库,当然如果你有时在数据库变动很大时想先备份,则可以直接启动作业就达到备份的效果了,这个方法最简单
企业管理器
--管理
--数据库维护计划
--右键
--新建维护计划
--下一步
--选“全部用户数据库”
--下一步
--下一步
--勾上“作为维护计划的一部分来备份数据库”,选择磁盘来存放备份文件,点调度的“更改”来设置每天24:00执行
--下一步
--指定存放备份文件的系统目录--这里还可以设置文件保存的时间
--下一步
--下一步
--下一步
--完成
sql server2005中建议使用SQL server 维护计划向导,用户尽可能选择Windows 身份验证(比如GISTAR\Administrator)。
第二种方法:自己创建存储过程,创建作业,设置相应参数
--以下资源从网上获取的
(1)新建存储过程
--我的第一种处理中用到的备份处理的存储过程
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_backupdb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_backupdb]
GO
/*--备份所有数据库
备份的文件名为数据库名+.bak
将所有的用户数据库(或指定的数据库列表)
备分到指定的目录下.
--邹建 2003.10(引用请保留此信息)--*/
/*--调用示例
--备份所有用户数据库
exec p_backupdb @bkpath='c:\',@dbname=''
--备份指定数据库
exec p_backupdb @bkpath='c:\',@dbname='客户资料,xzkh_new'
--*/
create proc p_backupdb
@bkpath nvarchar(260)='', --备份文件的存放目录,不指定则使用SQL默认的备份目录
@dbname nvarchar(4000)='' --要备份的数据库名称列表,不指定则备份所有用户数据库
as
declare @sql varchar(8000)
--检查参数
if isnull(@bkpath,'')=''
begin
select @bkpath=rtrim(reverse(filename)) from master..sysfiles where name='master'
select @bkpath=substring(@bkpath,charindex('\',@bkpath)+1,4000)
,@bkpath=reverse(substring(@bkpath,charindex('\',@bkpath),4000))+'BACKUP\'
end
else if right(@bkpath,1)<>'\' set @bkpath=@bkpath+'\'
--得到要备份的数据库列表
if isnull(@dbname,'')=''
declare tb cursor local for
select name from master..sysdatabases where name not in('master','tempdb','model','msdb')
else
declare tb cursor local for
select name from master..sysdatabases
where name not in('master','tempdb','model','msdb')
and(@dbname like '%,'+name+',%' or @dbname like name+',%' or @dbname like '%,'+name)
--备份处理
open tb
fetch next from tb into @dbname
while @@fetch_status=0
begin
set @sql='backup database '+@dbname
+' to disk='''+@bkpath+@dbname
+rtrim(convert(char,getdate(),112))+'.bak'' with format'
exec(@sql)
fetch next from tb into @dbname
end
close tb
deallocate tb
go
(2) SQL Server中创建作业的方法
企业管理器
--管理
--SQL Server代理
--右键作业
--新建作业
--"常规"项中输入作业名称
--"步骤"项
--新建
--"步骤名"中输入步骤名
--"类型"中选择"Transact-SQL 脚本(TSQL)" --这里执行上面那个备份的存储过程
--"数据库"选择执行命令的数据库
--"命令"中输入要执行的语句
--确定
--"调度"项
--新建调度
--"名称"中输入调度名称
--"调度类型"中选择你的作业执行安排
--如果选择"反复出现"
--点"更改"来设置你的时间安排
二。数据库还原
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_RestoreDb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_RestoreDb]
GO
create proc p_RestoreDb
@bkpath nvarchar(1000)='', --定义备份文件的存放目录,默认为SQL的备份目录
@bkfile nvarchar(4000)='', --定义要恢复的备份文件名,不含扩展名
@dbpath nvarchar(260)='', --恢复后的数据库存放目录,不指定则为SQL的默认数据目录
@overexist bit=1, --是否覆盖已经存在的数据库,仅@retype为'DB'/'DBNOR'是有效
@killuser bit=1 --是否关闭用户使用进程,仅@overexist=1时有效
as
declare @sql varchar(8000),@dbname sysname
if isnull(@bkpath,'')=''
begin
select @bkpath=rtrim(reverse(filename)) from master..sysfiles where name='master'
select @bkpath=substring(@bkpath,charindex('\',@bkpath)+1,4000)
,@bkpath=reverse(substring(@bkpath,charindex('\',@bkpath),4000))+'BACKUP\'
end
else if right(@bkpath,1)<>'\' set @bkpath=@bkpath+'\'
--得到恢复后的数据库存放目录
if isnull(@dbpath,'')=''
begin
select @dbpath=rtrim(reverse(filename)) from master..sysfiles where name='master'
select @dbpath=reverse(substring(@dbpath,charindex('\',@dbpath),4000))
end
else if right(@dbpath,1)<>'\' set @dbpath=@dbpath+'\'
--得到指定目录下的所有备份文件
create table #t(fname varchar(260),depth int,isf bit)
insert into #t exec master..xp_dirtree @bkpath,1,1
if isnull(@bkfile,'')=''
declare tb cursor local for select fn=left(fname,patindex('%.bak',fname)-1) from #t
where isf=1 and fname like '%.bak' --取.bak文件
else
begin
set @bkfile=','+replace(@bkfile,',','.bak,')+'.bak,'
declare tb cursor local for select fn=left(fname,patindex('%.bak',fname)-1) from #t
where isf=1 and fname like '%.bak' and @bkfile like '%,'+fname+',%'
end
--恢复数据库处理
open tb
fetch next from tb into @dbname
while @@fetch_status=0
begin
--生成数据库恢复语句
set @sql='restore database ['+@dbname
+'] fromdisk='''+@bkpath+@dbname+'.bak'''
+' with RECOVERY'
+case when @overexist=1 then ',replace' else '' end
--添加移动逻辑文件的处理
--从备份文件中获取逻辑文件名
declare @lfn nvarchar(128),@tp char(1),@i int,@pn nvarchar(128)
--创建临时表,保存获取的信息
create table #tb(ln nvarchar(128),pn nvarchar(260),tp char(1),fgn nvarchar(128),sz numeric(20,0),Msz numeric(20,0))
--从备份文件中获取信息
insert into #tb exec('restore filelistonly fromdisk='''+@bkpath+@dbname+'.bak''')
declare #f cursor local for select ln,tp,pn from #tb order by tp
open #f
fetch next from #f into @lfn,@tp,@pn
set @i=0
while @@fetch_status=0
begin
select @sql=@sql+',move'''+@lfn+'''to'''+@dbpath+@dbname+cast(@ias varchar)
+right(@pn,4)+''''
,@i=@i+1
fetch next from #f into @lfn,@tp,@pn
end
close #f
deallocate #f
drop table #tb
--关闭用户进程处理
if @overexist=1 and @killuser=1
begin
declare hCForEach cursor for