還原指定文件夾下的備份文件

<!-- [if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery> <w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:SpaceForUL/> <w:BalanceSingleByteDoubleByteWidth/> <w:DoNotLeaveBackslashAlone/> <w:ULTrailSpace/> <w:DoNotExpandShiftReturn/> <w:AdjustLineHeightInTable/> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:UseFELayout/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!-- [if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!-- [if !mso]> <object classid="clsid:38481807-CA0E-42D2-BF39-B33AF135CC4D" id=ieooui> </object> <mce:style><!-- st1/:*{behavior:url(#ieooui) } --> <!-- [endif]--> <!-- [if gte mso 10]> <mce:style><!-- /* Style Definitions */ table.MsoNormalTable {mso-style-name:表格內文; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} --> <!-- [endif]-->

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 數據庫 , 指定還原路徑