注意,下面备份还原都是用存储过程实现!
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
/**/ /*--备份数据库的通用存储过程
--邹建 2003.10--*/
/**/ /*--调用示例
--备份当前数据库
exec p_backupdb @bkpath='c:/',@bkfname='/DBNAME/_/DATE/_db.bak'
存储过程实现备份和还原数据库:
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
/*--备份数据库的通用存储过程
--邹建 2003.10--*/
/**/ /*--调用示例
--备份当前数据库
exec p_backupdb @bkpath='c:/',@bkfname='/DBNAME/_/DATE/_db.bak'
--差异备份当前数据库
exec p_backupdb @bkpath='c:/',@bkfname='db_/DATE/_df.bak',@bktype='DF'
--备份当前数据库日志
exec p_backupdb @bkpath='c:/',@bkfname='db_/DATE/_log.bak',@bktype='LOG'
--*/
create proc p_backupdb
@dbname sysname = '' , -- 要备份的数据库名称,不指定则备份当前数据库
@bkpath nvarchar ( 260 ) = '' , -- 备份文件的存放目录,不指定则使用SQL默认的备份目录
@bkfname nvarchar ( 260 ) = '' , -- 备份文件名,文件名中可以用/DBNAME/代表数据库名,/DATE/代表日期,/TIME/代表时间
@bktype nvarchar ( 10 ) = ' DB ' , -- 备份类型:'DB'备份数据库,'DF' 差异备份,'LOG' 日志备份
@appendfile bit = 1 , -- 追加/覆盖备份文件
@password nvarchar ( 20 ) = '' -- 为备份文件设置的密码(仅sql2000支持),设置后,恢复时必须提供此密码
as
declare @sql varchar ( 8000 )
if isnull ( @dbname , '' ) = '' set @dbname = db_name ()
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
if isnull ( @bkfname , '' ) = '' set @bkfname = ' /DBNAME/_/DATE/_/TIME/.BAK '
set @bkfname = replace ( replace ( replace ( @bkfname , ' /DBNAME/ ' , @dbname )
, ' /DATE/ ' , convert ( varchar , getdate (), 112 ))
, ' /TIME/ ' , replace ( convert ( varchar , getdate (), 108 ), ' : ' , '' ))
set @sql = ' backup ' + case @bktype when ' LOG ' then ' log ' else ' database ' end + @dbname
+ ' to disk= ''' + @bkpath + @bkfname
+ ''' with ' + case @bktype when ' DF ' then ' DIFFERENTIAL, ' else '' end
+ case @appendfile when 1 then ' NOINIT ' else ' INIT ' end
+ case isnull ( @password , '' ) when '' then '' else ' ,PASSWORD= ''' + @password + '''' end
exec ( @sql )
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
/**/ /*--恢复数据库的通用存储过程
--邹建 2003.10--*/
/**/ /*--调用示例
--完整恢复数据库
exec p_RestoreDb @bkfile='c:/db_20031015_db.bak',@dbname='db'
--差异备份恢复
exec p_RestoreDb @bkfile='c:/db_20031015_db.bak',@dbname='db',@retype='DBNOR'
exec p_RestoreDb @bkfile='c:/db_20031015_df.bak',@dbname='db',@retype='DF'
--日志备份恢复
exec p_RestoreDb @bkfile='c:/db_20031015_db.bak',@dbname='db',@retype='DBNOR'
exec p_RestoreDb @bkfile='c:/db_20031015_log.bak',@dbname='db',@retype='LOG'
--*/
create proc p_RestoreDb
@bkfile nvarchar ( 1000 ), -- 定义要恢复的备份文件名(带路径)
@dbname sysname = '' , -- 定义恢复后的数据库名,默认为备份的文件名
@dbpath nvarchar ( 260 ) = '' , -- 恢复后的数据库存放目录,不指定则为SQL的默认数据目录
@retype nvarchar ( 10 ) = ' DB ' , -- 恢复类型:'DB'完事恢复数据库,'DBNOR' 为差异恢复,日志恢复进行完整恢复,'DF' 差异备份的恢复,'LOG' 日志恢复
@filenumber int = 1 , -- 恢复的文件号
@overexist bit = 1 , -- 是否覆盖已经存在的数据库,仅@retype为'DB'/'DBNOR'是有效
@killuser bit = 1 , -- 是否关闭用户使用进程,仅@overexist=1时有效
@password nvarchar ( 20 ) = '' -- 备份文件的密码(仅sql2000支持),如果备份时设置了密码,必须提供此密码
as
declare @sql varchar ( 8000 )
-- 得到恢复后的数据库名
if isnull ( @dbname , '' ) = ''
select @sql = reverse ( @bkfile )
, @sql = case when charindex ( ' . ' , @sql ) = 0 then @sql
else substring ( @sql , charindex ( ' . ' , @sql ) + 1 , 1000 ) end
, @sql = case when charindex ( ' / ' , @sql ) = 0 then @sql
else left ( @sql , charindex ( ' / ' , @sql ) - 1 ) end
, @dbname = reverse ( @sql )
-- 得到恢复后的数据库存放目录
if isnull ( @dbpath , '' ) = ''
begin
select @dbpath = rtrim ( reverse (filename)) from master..sysfiles where name = ' master '
select @dbpath = reverse ( substring ( @dbpath , charindex ( ' / ' , @dbpath ), 4000 ))
end
-- 生成数据库恢复语句
set @sql = ' restore ' + case @retype when ' LOG ' then ' log ' else ' database ' end + @dbname
+ ' from disk= ''' + @bkfile + ''''
+ ' with file= ' + cast ( @filenumber as varchar )
+ case when @overexist = 1 and @retype in ( ' DB ' , ' DBNOR ' ) then ' ,replace ' else '' end
+ case @retype when ' DBNOR ' then ' ,NORECOVERY ' else ' ,RECOVERY ' end
+ case isnull ( @password , '' ) when '' then '' else ' ,PASSWORD= ''' + @password + '''' end
-- 添加移动逻辑文件的处理
if @retype = ' DB ' or @retype = ' DBNOR '
begin
-- 从备份文件中获取逻辑文件名
declare @lfn nvarchar ( 128 ), @tp char ( 1 ), @i int , @s varchar ( 1000 )
-- 创建临时表,保存获取的信息
create table #tb(ln nvarchar ( 128 ),pn nvarchar ( 260 ),tp char ( 1 ),fgn nvarchar ( 128 ),sz numeric( 20 , 0 ),Msz numeric( 20 , 0 ))
-- 从备份文件中获取信息
set @s = ' restore filelistonly from disk= ''' + @bkfile + ''''
++ case isnull ( @password , '' ) when '' then '' else ' with PASSWORD= ''' + @password + '''' end
insert into #tb exec ( @s )
declare #f cursor for select ln,tp from #tb
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
end
-- 关闭用户进程处理
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 )
Go
邹建说:
说白了,就是备份数据库和还原数据库的SQL语句的应用:
-- 备份
backup database 数据库 to disk = ' c:/你的备份文件名 '
-- 还原
restore database 数据库 from disk = ' c:/你的备份文件名 '
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
/**/ /*--备份数据库的通用存储过程
--邹建 2003.10--*/
/**/ /*--调用示例
--备份当前数据库
exec p_backupdb @bkpath='c:/',@bkfname='/DBNAME/_/DATE/_db.bak'
存储过程实现备份和还原数据库:
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
/*--备份数据库的通用存储过程
--邹建 2003.10--*/
/**/ /*--调用示例
--备份当前数据库
exec p_backupdb @bkpath='c:/',@bkfname='/DBNAME/_/DATE/_db.bak'
--差异备份当前数据库
exec p_backupdb @bkpath='c:/',@bkfname='db_/DATE/_df.bak',@bktype='DF'
--备份当前数据库日志
exec p_backupdb @bkpath='c:/',@bkfname='db_/DATE/_log.bak',@bktype='LOG'
--*/
create proc p_backupdb
@dbname sysname = '' , -- 要备份的数据库名称,不指定则备份当前数据库
@bkpath nvarchar ( 260 ) = '' , -- 备份文件的存放目录,不指定则使用SQL默认的备份目录
@bkfname nvarchar ( 260 ) = '' , -- 备份文件名,文件名中可以用/DBNAME/代表数据库名,/DATE/代表日期,/TIME/代表时间
@bktype nvarchar ( 10 ) = ' DB ' , -- 备份类型:'DB'备份数据库,'DF' 差异备份,'LOG' 日志备份
@appendfile bit = 1 , -- 追加/覆盖备份文件
@password nvarchar ( 20 ) = '' -- 为备份文件设置的密码(仅sql2000支持),设置后,恢复时必须提供此密码
as
declare @sql varchar ( 8000 )
if isnull ( @dbname , '' ) = '' set @dbname = db_name ()
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
if isnull ( @bkfname , '' ) = '' set @bkfname = ' /DBNAME/_/DATE/_/TIME/.BAK '
set @bkfname = replace ( replace ( replace ( @bkfname , ' /DBNAME/ ' , @dbname )
, ' /DATE/ ' , convert ( varchar , getdate (), 112 ))
, ' /TIME/ ' , replace ( convert ( varchar , getdate (), 108 ), ' : ' , '' ))
set @sql = ' backup ' + case @bktype when ' LOG ' then ' log ' else ' database ' end + @dbname
+ ' to disk= ''' + @bkpath + @bkfname
+ ''' with ' + case @bktype when ' DF ' then ' DIFFERENTIAL, ' else '' end
+ case @appendfile when 1 then ' NOINIT ' else ' INIT ' end
+ case isnull ( @password , '' ) when '' then '' else ' ,PASSWORD= ''' + @password + '''' end
exec ( @sql )
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
/**/ /*--恢复数据库的通用存储过程
--邹建 2003.10--*/
/**/ /*--调用示例
--完整恢复数据库
exec p_RestoreDb @bkfile='c:/db_20031015_db.bak',@dbname='db'
--差异备份恢复
exec p_RestoreDb @bkfile='c:/db_20031015_db.bak',@dbname='db',@retype='DBNOR'
exec p_RestoreDb @bkfile='c:/db_20031015_df.bak',@dbname='db',@retype='DF'
--日志备份恢复
exec p_RestoreDb @bkfile='c:/db_20031015_db.bak',@dbname='db',@retype='DBNOR'
exec p_RestoreDb @bkfile='c:/db_20031015_log.bak',@dbname='db',@retype='LOG'
--*/
create proc p_RestoreDb
@bkfile nvarchar ( 1000 ), -- 定义要恢复的备份文件名(带路径)
@dbname sysname = '' , -- 定义恢复后的数据库名,默认为备份的文件名
@dbpath nvarchar ( 260 ) = '' , -- 恢复后的数据库存放目录,不指定则为SQL的默认数据目录
@retype nvarchar ( 10 ) = ' DB ' , -- 恢复类型:'DB'完事恢复数据库,'DBNOR' 为差异恢复,日志恢复进行完整恢复,'DF' 差异备份的恢复,'LOG' 日志恢复
@filenumber int = 1 , -- 恢复的文件号
@overexist bit = 1 , -- 是否覆盖已经存在的数据库,仅@retype为'DB'/'DBNOR'是有效
@killuser bit = 1 , -- 是否关闭用户使用进程,仅@overexist=1时有效
@password nvarchar ( 20 ) = '' -- 备份文件的密码(仅sql2000支持),如果备份时设置了密码,必须提供此密码
as
declare @sql varchar ( 8000 )
-- 得到恢复后的数据库名
if isnull ( @dbname , '' ) = ''
select @sql = reverse ( @bkfile )
, @sql = case when charindex ( ' . ' , @sql ) = 0 then @sql
else substring ( @sql , charindex ( ' . ' , @sql ) + 1 , 1000 ) end
, @sql = case when charindex ( ' / ' , @sql ) = 0 then @sql
else left ( @sql , charindex ( ' / ' , @sql ) - 1 ) end
, @dbname = reverse ( @sql )
-- 得到恢复后的数据库存放目录
if isnull ( @dbpath , '' ) = ''
begin
select @dbpath = rtrim ( reverse (filename)) from master..sysfiles where name = ' master '
select @dbpath = reverse ( substring ( @dbpath , charindex ( ' / ' , @dbpath ), 4000 ))
end
-- 生成数据库恢复语句
set @sql = ' restore ' + case @retype when ' LOG ' then ' log ' else ' database ' end + @dbname
+ ' from disk= ''' + @bkfile + ''''
+ ' with file= ' + cast ( @filenumber as varchar )
+ case when @overexist = 1 and @retype in ( ' DB ' , ' DBNOR ' ) then ' ,replace ' else '' end
+ case @retype when ' DBNOR ' then ' ,NORECOVERY ' else ' ,RECOVERY ' end
+ case isnull ( @password , '' ) when '' then '' else ' ,PASSWORD= ''' + @password + '''' end
-- 添加移动逻辑文件的处理
if @retype = ' DB ' or @retype = ' DBNOR '
begin
-- 从备份文件中获取逻辑文件名
declare @lfn nvarchar ( 128 ), @tp char ( 1 ), @i int , @s varchar ( 1000 )
-- 创建临时表,保存获取的信息
create table #tb(ln nvarchar ( 128 ),pn nvarchar ( 260 ),tp char ( 1 ),fgn nvarchar ( 128 ),sz numeric( 20 , 0 ),Msz numeric( 20 , 0 ))
-- 从备份文件中获取信息
set @s = ' restore filelistonly from disk= ''' + @bkfile + ''''
++ case isnull ( @password , '' ) when '' then '' else ' with PASSWORD= ''' + @password + '''' end
insert into #tb exec ( @s )
declare #f cursor for select ln,tp from #tb
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
end
-- 关闭用户进程处理
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 )
Go
邹建说:
说白了,就是备份数据库和还原数据库的SQL语句的应用:
-- 备份
backup database 数据库 to disk = ' c:/你的备份文件名 '
-- 还原
restore database 数据库 from disk = ' c:/你的备份文件名 '