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), @Path3 nvarchar ( 1000)
set @Path3= replace ( @Path, '"' , '' )
declare @FileExist table ( Col1 int , Col2 int , Col3 int )
insert @FileExist exec xp_fileexist @Path3
if @DefaultPath is not null
begin
set @Path3= replace ( @DefaultPath, '"' , '' )
insert @FileExist exec xp_fileexist @Path3
end
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)
set @Path= Replace ( @Path, '"' , '' )
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 數據庫 , 指定還原路徑
-- 有空格時加引號
--exec sp_RestoreDB @Path='G:/"HR 2009"/',@DBs='HR,OChart',@DefaultPath='C:/' -- 還原 HR 和 OChart 數據庫 , 指定還原路徑