SQL2000時用insert ..exec..用字符串拼起來執行
只指定完整備份的數據庫文件，如果處理差異有限制，在這裡不作針對性寫法。
---------------------------------------------------------
use Master
go
if object_ID('sp_RestoreDB') is not null
Drop Procedure sp_RestoreDB
go
/**********************************************************
%%存儲過程名：sp_RestoreDB
%%輸入參數：@Path,@DBs,@DefaultPath
%%輸出參數：
%%功能：還原活頁夾路徑下的備份,如：活頁夾下同一個數據庫有多個備份或多個備份文件，如會還原最後一次
****************************************************************************************************************************************************************
%%編寫：Roy 2009-09-24
****************************************************************************************************************************************************************/
Create Procedure sp_RestoreDB
(
@Path nvarchar(1000) --路徑如：G:\
,@DBs nvarchar(2000)=null --指定要還原的數據庫如：HR,SalesOrder;用逗號分隔,不指定時按備份文件中的數據庫還原
,@DefaultPath nvarchar(2000)=null --通過還原文件生成數據時，指定數據庫文件存放路徑,不指定時取數據最大的一個作為路徑
)
as
set nocount on ;
declare @Sql nvarchar(max),@Path2 nvarchar(1000)
declare @FileExist table(Col1 int,Col2 int,Col3 int)
insert @FileExist exec xp_fileexist @Path
if @DefaultPath is not null
insert @FileExist exec xp_fileexist @DefaultPath
if exists(select 1 from @FileExist where Col2=0)
begin
raiserror 50001 N'指定文件路徑不正確,請確認!'
return
end
select
top 1 @DefaultPath=isnull(@DefaultPath,left(Physical_name,len(Physical_name)-charindex('\',reverse(Physical_name))+1))
from sys.master_files order by Database_id desc
if object_id('Tempdb..#BackFile') is not null
drop table #BackFile
create table #BackFile( FName nvarchar(1000))
if object_id('Tempdb..#BackDB') is not null
drop table #BackDB
create table #BackDB
(
ID int identity(1,1)
,BackupName nvarchar(128)
,BackupDescription nvarchar(255)
,BackupType smallint
,ExpirationDate datetime
,Compressed tinyint
,Position smallint
,DeviceType tinyint
,UserName nvarchar(128)
,ServerName nvarchar(128)
,DatabaseName nvarchar(128)
,DatabaseVersion int
,DatabaseCreationDate datetime
,BackupSize numeric(20,0)
,FirstLSN numeric(25,0)
,LastLSN numeric(25,0)
,CheckpointLSN numeric(25,0)
,DatabaseBackupLSN numeric(25,0)
,BackupStartDate datetime
,BackupFinishDate datetime
,SortOrder smallint
,CodePage smallint
,UnicodeLocaleId int
,UnicodeComparisonStyle int
,CompatibilityLevel tinyint
,SoftwareVendorId int
,SoftwareVersionMajor int
,SoftwareVersionMinor int
,SoftwareVersionBuild int
,MachineName nvarchar(128)
,Flags int
,BindingID uniqueidentifIEr
,RecoveryForkID uniqueidentifIEr
,Collation nvarchar(128)
,FamilyGUID uniqueidentifIEr
,HasBulkLoggedData bit
,IsSnapshot bit
,IsReadOnly bit
,IsSingleUser bit
,HasBackupChecksums bit
,IsDamaged bit
,BeginsLogChain bit
,HasIncompleteMetaData bit
,IsForceOffline bit
,IsCopyOnly bit
,FirstRecoveryForkID uniqueidentifIEr
,ForkPointLSN numeric(25,0) NULL
,RecoveryModel nvarchar(60)
,DifferentialBaseLSN numeric(25,0) NULL
,DifferentialBaseGUID uniqueidentifIEr
,BackupTypeDescription nvarchar(60)
,BackupSetGUID uniqueidentifIEr NULL
,PathName nvarchar(2000)
)
if object_id('Tempdb..#TmpBackDB') is not null
drop table #TmpBackDB
create table #TmpBackDB
(
BackupName nvarchar(128)
,BackupDescription nvarchar(255)
,BackupType smallint
,ExpirationDate datetime
,Compressed tinyint
,Position smallint
,DeviceType tinyint
,UserName nvarchar(128)
,ServerName nvarchar(128)
,DatabaseName nvarchar(128)
,DatabaseVersion int
,DatabaseCreationDate datetime
,BackupSize numeric(20,0)
,FirstLSN numeric(25,0)
,LastLSN numeric(25,0)
,CheckpointLSN numeric(25,0)
,DatabaseBackupLSN numeric(25,0)
,BackupStartDate datetime
,BackupFinishDate datetime
,SortOrder smallint
,CodePage smallint
,UnicodeLocaleId int
,UnicodeComparisonStyle int
,CompatibilityLevel tinyint
,SoftwareVendorId int
,SoftwareVersionMajor int
,SoftwareVersionMinor int
,SoftwareVersionBuild int
,MachineName nvarchar(128)
,Flags int
,BindingID uniqueidentifIEr
,RecoveryForkID uniqueidentifIEr
,Collation nvarchar(128)
,FamilyGUID uniqueidentifIEr
,HasBulkLoggedData bit
,IsSnapshot bit
,IsReadOnly bit
,IsSingleUser bit
,HasBackupChecksums bit
,IsDamaged bit
,BeginsLogChain bit
,HasIncompleteMetaData bit
,IsForceOffline bit
,IsCopyOnly bit
,FirstRecoveryForkID uniqueidentifIEr
,ForkPointLSN numeric(25,0) NULL
,RecoveryModel nvarchar(60)
,DifferentialBaseLSN numeric(25,0) NULL
,DifferentialBaseGUID uniqueidentifIEr
,BackupTypeDescription nvarchar(60)
,BackupSetGUID uniqueidentifIEr NULL
)
if object_id('Tempdb..#BackDB2') is not null
drop table #BackDB2
create table #BackDB2
(
ID int identity(1,1)
,LogicalName nvarchar(128)
,PhysicalName nvarchar(260)
,Type char(1)
,FileGroupName nvarchar(128)
,Size numeric(20,0)
,MaxSize numeric(20,0)
,FileID bigint
,CreateLSN numeric(25,0)
,DropLSN numeric(25,0) NULL
,UniqueID uniqueidentifIEr
,ReadOnlyLSN numeric(25,0) NULL
,ReadWriteLSN numeric(25,0) NULL
,BackupSizeInBytes bigint
,SourceBlockSize int
,FileGroupID int
,LogGroupGUID uniqueidentifIEr NULL
,DifferentialBaseLSN numeric(25,0) NULL
,DifferentialBaseGUID uniqueidentifIEr
,IsReadOnly bit
,IsPresent bit
,DatabaseName nvarchar(128)
,Position smallint
,PathName nvarchar(2000)
)
if object_id('Tempdb..#TmpBackDB2') is not null
drop table #TmpBackDB2
create table #TmpBackDB2
(
LogicalName nvarchar(128)
,PhysicalName nvarchar(260)
,Type char(1)
,FileGroupName nvarchar(128)
,Size numeric(20,0)
,MaxSize numeric(20,0)
,FileID bigint
,CreateLSN numeric(25,0)
,DropLSN numeric(25,0) NULL
,UniqueID uniqueidentifIEr
,ReadOnlyLSN numeric(25,0) NULL
,ReadWriteLSN numeric(25,0) NULL
,BackupSizeInBytes bigint
,SourceBlockSize int
,FileGroupID int
,LogGroupGUID uniqueidentifIEr NULL
,DifferentialBaseLSN numeric(25,0) NULL
,DifferentialBaseGUID uniqueidentifIEr
,IsReadOnly bit
,IsPresent bit
)
set @Path2=N'dir /B/o:d '+@Path+'*.bak'
insert #BackFile exec master..xp_cmdshell @Path2
delete #BackFile where FName is null or right(FName,4)<>'.bak'
if not exists(select 1 from #BackFile)
begin
raiserror 50001 N'備份文件不存在'
return
end
set @Sql=char(13)+char(10)
select
@Sql=@Sql+char(13)+char(10)+'insert into #TmpBackDB exec(''RESTORE HEADERONLY FROM DISK ='''+quotename(@Path+FName,'''')
+''''') insert into #BackDB select *,'''+@Path+FName+''' from #TmpBackDB delete #TmpBackDB '
from #BackFile
exec(@Sql)
delete #BackDB where BackupType>1 or ','+isnull(@DBs,DatabaseName)+',' not like '%,'+DatabaseName+',%' --刪除非完整備份和非指定還原數據庫
delete a from #BackDB as a where exists(select 1 from #BackDB where DatabaseName=a.DatabaseName and ID>a.ID)
delete a
from #BackDB a
left join (select PathName,DatabaseName,max(Position) as Position from #BackDB group by PathName,DatabaseName)b
on a.PathName=b.PathName and a.DatabaseName=b.DatabaseName and a.Position=b.Position
where b.PathName is null
set @Sql=char(13)+char(10)
select
@Sql=@Sql+char(13)+char(10)+' Kill '+rtrim(spid)
from sysprocesses where dbid in(select db_id(DatabaseName) from #BackDB )
exec (@Sql)
set @Sql=char(13)+char(10)
select
@Sql=@Sql+char(13)+char(10)+'RESTORE DATABASE '+quotename(DatabaseName)+' From Disk=N'''+PathName+''' WITH FILE = '+rtrim(Position)+', NOUNLOAD, REPLACE' +char(13)+char(10)+'print '''+DatabaseName+''''
from #BackDB
where db_id(DatabaseName) is not null
--print @Sql
exec(@Sql)
if exists(select 1 from #BackDB where db_id(DatabaseName) is null)
begin
set @Sql=char(13)+char(10)
select
@Sql=@Sql+char(13)+char(10)+'insert into #TmpBackDB2 exec(''RESTORE FILELISTONLY FROM Disk=N'''''+PathName+''''''') insert into #BackDB2 select *,'''+DatabaseName+''','+rtrim(Position)+','''+PathName+''' from #TmpBackDB2 delete #TmpBackDB2 '
from #BackDB
where db_id(DatabaseName) is null
exec(@Sql)
set @Sql=char(13)+char(10)
select
@Sql=@Sql+char(13)+char(10)+N'RESTORE DATABASE '+quotename(a.DatabaseName)+N' FROM DISK = N'''+a.PathName+''' with File='+rtrim(a.Position)+', MOVE N'+quotename(a.LogicalName,'''')+' TO N'''+@DefaultPath+right(a.PhysicalName,charindex('\',reverse(a.PhysicalName))-1)+''', MOVE N'+quotename(b.LogicalName,'''')+' TO N'''+@DefaultPath+right(b.PhysicalName,charindex('\',reverse(b.PhysicalName))-1)+''', NOUNLOAD, REPLACE'+char(13)+char(10)+' print '''+a.DatabaseName+''''
from #BackDB2 a
inner join #BackDB2 b on a.DatabaseName=b.DatabaseName
where a.Type='D' and b.Type='L'
--print @Sql
exec(@Sql)
end
drop table #BackDB2,#TmpBackDB,#BackDB,#TmpBackDB2,#BackFile
go
--調用方法
--use Master
--go
--exec sp_RestoreDB @Path='G:\' --還原路徑下的所有備份
--exec sp_RestoreDB @Path='G:\',@DBs='HR,OChart' --還原HR和OChart數據庫
--exec sp_RestoreDB @Path='G:\',@DBs='HR,OChart',@DefaultPath='C:\' --還原HR和OChart數據庫,指定還原路徑
