数据库备份及还原.doc

一、数据库备份第一种方法：利用SQL Server的数据库维护计划，创建好之后在作业列表中,只要SQL代理启动着，它就会按照你的设置定时备份数据库，当然如果你有时在数据库变动很大时想先备份，则可以直接启动作业就达到备份的效果了，这个方法最简单企业管理器 -管理 -数据库维护计划 -右键 -新建维护计划 -下一步 -选“全部用户数据库” -下一步 -下一步 -勾上“作为维护计划的一部分来备份数据库”，选择磁盘来存放备份文件，点调度的“更改”来设置每天24：00执行 -下一步 -指定存放备份文件的系统目录-这里还可以设置文件保存的时间 -下一步 -下一步 -下一步 -完成sql server2005中建议使用SQL server 维护计划向导，用户尽可能选择Windows 身份验证。第二种方法：自己创建存储过程，创建作业，设置相应参数-以下资源从网上获取的（1）新建存储过程-我的第一种处理中用到的备份处理的存储过程 if exists (select * from dbo.sysobjects where id = object_id(Ndbo.p_backupdb) and OBJECTPROPERTY(id, NIsProcedure) = 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 + todisk=+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(Ndbo.p_RestoreDb) and OBJECTPROPERTY(id, NIsProcedure) = 1) drop procedure dbo.p_RestoreDb GO /*-恢复指定目录下的所有数据库 恢复的数据库名为备份文件名(不含扩展名) 备份文件的扩展名固定为.bak -邹建 2003.10(引用请保留此信息)-*/ /*-调用示例 -恢复指定目录下的所有数据库 exec p_RestoreDb bkpath=c: -恢复指定目录下的指定数据库 exec p_RestoreDb bkpath=c:,bkfile=客户资料,xzkh_new -*/ 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 + from disk=+bkpath+dbname+.bak + with RECOVERY +case when overexist=1 then ,replace else end -添加移动逻辑文件的处理 -从备份文件中获取逻辑文件名 declare lfn nvarchar(128),tp char(1),i int -创建临时表,保存获取的信息 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 from disk=+bkpath+dbname+.bak) declare #f cursor local for select ln,tp from #tb order by tp open #f fetch next from #f into lfn,tp set i=0 while fetch_status=0 begin select sql=sql+,move +lfn+ to +dbpath+dbname+cast(i as varchar) +case tp when D then .mdf else .ldf end ,i=i+1 fetch next from #f into lfn,tp end close #f deallocate #f drop table #tb -关闭用户进程处理 if overexist=1 and killu