--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