--下面的存储过程可以帮助你一次性备份所有的用户数据库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 +'.bak'' with init' exec(@sql) fetch next from tb into @dbname end close tb deallocate tb go
--最后还原用户数据库,下面的存储过程可以帮助你还原指定目录下的所有用户数据库: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/*--恢复指定目录下的所有数据库 恢复的数据库名为备份文件名(不含扩展名) 备份文件的扩展名固定为.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 sysnameif 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,1if 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 @killuser=1 begin declare hCForEach cursor for select s='kill '+cast(spid as varchar) from master..sysprocesses where dbid=db_id(@dbname) exec sp_msforeach_worker '?' end --恢复数据库 exec(@sql) fetch next from tb into @dbname end close tb deallocate tb go
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
+'.bak'' with init'
exec(@sql)
fetch next from tb into @dbname
end
close tb
deallocate tb
go
关于系统数据库的恢复总结如下: 在SQL Server数据库中,系统信息存储在系统数据库中,主要的系统数据库包括:
master-从整体上控制用户数据库和SQL Server操作,在创建了任何用户定义的对象后,都要备份它
model-为新数据库提供模版和原型
msdb-包含了有关作业、报警及操作员等信息如果包含系统数据库的介质变了,那么必须重建系统数据库,如果你仍然可以启动SQL Server服务,则可以通过RESTORE语句从系统数据库的备份中恢复数据库。
如果master坏了,不能启动系统,可以按照下面步骤进行恢复
1.重建系统数据库 运行c:\mssql7\binn\rebuildm.exe,按照提示进行即可,
过程中需要系统数据库样本的路径,可在安装光盘中找到; 2 重建系统数据库后,启动SQL Server服务,用系统数据库的备份恢复数据库
就行了通常恢复顺序为master->msdb->model
在恢复master的备份时要注意:必须在单用户(single user)模式下进行a.进入单用户模式的方法:
1.在命令行模式下输入sqlservr -c -f -m或者输入sqlservr -m
其中:-c 可以缩短启动时间,SQL Server 不作为Windows NT的服务启动
-f 用最小配置启动SQL Server
-m 单用户模式启动SQL Server 2.可以在控制面板-服务-MSSQLServer的启动参数中输入-c -f -m或者输入-m,点击开始
3.进行master数据库的恢复
a.直接进入查询分析器,有个提示不要理会它
输入恢复语句进行数据库恢复:
RESTORE DATABASE master from disk='c:\具体的备份文件名'b.或者用这个,在命令提示符下输入,注意大小写
使用"windows身份验证"的,输入:isql /E
使用"sql server和windows身份验证"的,输入:isql /U"用户名" /P"密码"
然后在出现的提示符下输入(注意1>,2>是提示符):
1>RESTORE DATABASE master from disk='c:\具体的备份文件名'
2>go
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 sysnameif 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,1if 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 @killuser=1
begin
declare hCForEach cursor for
select s='kill '+cast(spid as varchar) from master..sysprocesses
where dbid=db_id(@dbname)
exec sp_msforeach_worker '?'
end --恢复数据库
exec(@sql)
fetch next from tb into @dbname
end
close tb
deallocate tb
go
我用原数据库BACKUP后的.bak文件在新的数据库上面直接还原数据库解决了。
虽然麻烦了点,但还算是安全。
最主要的是原数据库一直在运行,我不可以停下。