--USE [master]
GO
/****** Object: StoredProcedure [dbo].[zsp_RestoreHeaderOnly] Script Date: 2014/1/18 13:31:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter proc [dbo].[zsp_RestoreHeaderOnly]
--@sql varchar(max)
@Text nvarchar(MAX), --待分拆的字符串
@Separator nvarchar(8) = ',' --数据分隔符
as
begin
/*
declare @Text nvarchar(max)
set @text = N'
d:\iFundGlobalCenter.log.1.bak,
d:\iFundGlobalCenter.log.2.bak,
d:\iFundGlobalCenter.log.3.bak,
d:\iFundGlobalCenter.log.4.bak,
d:\iFundGlobalCenter.ful.5.bak
'
exec zsp_RestoreHeaderOnly @text
*/
declare @sql nvarchar(max) = N''
declare @Table table
(
id int
,F nvarchar(256)
)
set @Text = replace(@Text,N' ',N'')
set @Text = replace(@Text,nchar(13),N'')
set @Text = replace(@Text,nchar(10),N'')
set @Text = replace(@Text,nchar(9),N'')
set @Separator = N','
DECLARE @SeparatorLen int
SET @SeparatorLen=LEN(@Separator + N'$') - 2
set @Text = replace(@Text,N' ',N'')
declare @i int
set @i = 1
WHILE CHARINDEX(@Separator,@Text )>0
BEGIN
declare @v nvarchar(max)
set @v = (LEFT(@Text ,CHARINDEX(@Separator,@Text )-1))
INSERT @Table (id,F)
select @i,@v
where rtrim(ltrim(@v)) != ''
and not exists (select 1 from @Table where F = @v)
if @@rowcount > 0
begin
set @i = @i + 1
end
SET @Text = STUFF(@Text ,1,CHARINDEX(@Separator,@Text )+@SeparatorLen,'')
END
INSERT @Table (id,F)
select @i,@Text
where rtrim(ltrim(@Text)) != ''
and not exists (select 1 from @Table where F = @Text)
select
@sql +=
N'RESTORE HEADERONLY FROM disk=''' + F + '''' + nchar(13) + nchar(10)
from
@table
select @sql
declare @ table
(
--CREATE TABLE #T(
BackupName nvarchar(256) ,
BackupDescription nvarchar(256) ,
BackupType int ,
ExpirationDate datetime ,
Compressed tinyint ,
Position int ,
DeviceType int ,
UserName nvarchar(256) ,
ServerName nvarchar(256) ,
DatabaseName nvarchar(256) ,
DatabaseVersion int ,
DatabaseCreationDate datetime ,
BackupSize numeric(38,0) ,
FirstLSN numeric(38,0) ,
LastLSN numeric(38,0) ,
CheckpointLSN numeric(38,0) ,
DatabaseBackupLSN numeric(38,0) ,
BackupStartDate datetime ,
BackupFinishDate datetime ,
SortOrder int ,
[CodePage] int ,
UnicodeLocaleId int ,
UnicodeComparisonStyle int ,
CompatibilityLevel int ,
SoftwareVendorId int ,
SoftwareVersionMajor int ,
SoftwareVersionMinor int ,
SoftwareVersionBuild int ,
MachineName nvarchar(256) ,
Flags int ,
BindingID uniqueidentifier ,
RecoveryForkID uniqueidentifier ,
Collation nvarchar(256) ,
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(38,0) NULL ,
RecoveryModel nvarchar(256) ,
DifferentialBaseLSN numeric(38,0) NULL ,
DifferentialBaseGUID uniqueidentifier ,
BackupTypeDescription nvarchar(256) ,
BackupSetGUID uniqueidentifier NULL
, [CompressedBackupSize] numeric(38,0)
, [Containment] numeric(38,0)
)
INSERT
--#1
@
EXEC
(@sql)
;with T
as
(
select
NewBackupType = iif([BackupType] in (1, 5) , 1 ,[BackupType])
, NewDifferentialBaseLSN = iif(backuptype=1, FirstLSN, DifferentialBaseLSN)
, *
from
@
--order by
-- databaseName
-- ,[FirstLSN]
)
, TT
as
(
select
MachineName_0 = MachineName
, DatabaseName_0 = DatabaseName
, NewBackupType_0 = NewBackupType
--, IsDamaged_0 = IsDamaged
--, BeginsLogChain_0 = BeginsLogChain
, LagNewBackupType = Lag(NewBackupType)
over
(
order by
MachineName
, DatabaseName
, FirstLSN
)
, LagLastLSN =
lag([LastLSN])
OVER
(
ORDER BY
NewBackupType
, [FirstLSN]
)
, FirstLSN_0 = FirstLSN
, LastLSN_0 = LastLSN
, FirstValue_FirstLSN = iif
(
backupType in (1,5)
, --FIRST_VALUE(FirstLSN)
min(FirstLSN)
OVER
(
partition by
MachineName
, databaseName
, NewDifferentialBaseLSN
order by
--MachineName
--, DatabaseName
--,
FirstLSN
)
, null
)
, DifferentialBaseLSN_0 = DifferentialBaseLSN
, BackupTypeDescription_0 = BackupTypeDescription
, BackupFinishDate_0 = BackupFinishDate
, *
from
T
)
select
ok = iif(FirstLSN = LagLastLSN, 'Y', 'N')
, *
from
TT
order by
MachineName
, databaseName
--, NewBackupType
, FirstLSN
end
|
