declare @switch tinyint

declare @ds table( [sid] int identity( 1 , 1 ), [size] int )

declare @df table( [sid] int, fid uniqueidentifier, is_ro bit ) -- много @df.[sid] к одному @ds.[sid]
------------- Создание тестовых наборов -----------
-- наборы с [is_ro] = 1
insert into @ds 
/* 1 */ select  100  union all 
/* 2 */ select  150  union all 
/* 3 */ select  150  union all 
/* 4 */ select  100            

insert into @df
select  1 , NewID(),  1  union all 
select  2 , NewID(),  1  union all 
select  3 , NewID(),  1  union all 
select  4 , NewID(),  1  

-- наборы с [is_ro] = 0
insert into @ds
/* 5 */ select  100  union all 
/* 6 */ select  150  union all 
/* 7 */ select  150  union all 
/* 8 */ select  100            
insert into @df
select  5 , NewID(),  0  union all 
select  6 , NewID(),  0  union all 
select  7 , NewID(),  0  union all 
select  8 , NewID(),  0  

-- Теперь в различных комбинациях 

/* 9 */ insert into @ds values( 0 )

insert into @df
select top  2   9 , fid, is_ro
from @df
where is_ro =  1  and [sid] between  1  and  4 
order by [sid] asc

insert into @df
select top  2   9 , fid, is_ro
from @df
where is_ro =  0  and [sid] between  5  and  8 
order by [sid] asc

update @ds set [size] = T.[size]
from @ds as s
cross apply(
  select SUM([size]) as [size]
  from @ds as ds
  inner join @df as df on ds.[sid] = df.[sid]
  inner join @df as app on app.[fid] = df.[fid]
  where (ds.[sid] between  1  and  8 ) and app.[sid]=s.[sid]
) as T
where [sid] =  9 

/* 10 */ insert into @ds values( 0 )

insert into @df
select top  1   10 , fid, is_ro
from @df
where is_ro =  1  and [sid] between  1  and  4 
order by [sid] desc

insert into @df
select top  2   10 , fid, is_ro
from @df
where is_ro =  0  and [sid] between  5  and  8 
order by [sid] desc

update @ds set [size] = T.[size]
from @ds as s
cross apply(
  select SUM([size]) as [size]
  from @ds as ds
  inner join @df as df on ds.[sid] = df.[sid]
  inner join @df as app on app.[fid] = df.[fid]
  where (ds.[sid] between  1  and  8 ) and app.[sid]=s.[sid]
) as T
where [sid] =  10 


/* 11 */ insert into @ds values( 0 )
insert into @df
select  11 , [fid], [is_ro]
from @df
where [sid] between  1  and  8 

update @ds set [size] = T.[size]
from @ds as s
cross apply(
  select SUM([size]) as [size]
  from @ds as ds
  inner join @df as df on ds.[sid] = df.[sid]
  inner join @df as app on app.[fid] = df.[fid]
  where (ds.[sid] between  1  and  8 ) and app.[sid]=s.[sid]
) as T
where [sid] =  11 

select *
from @ds as ds
inner join @df as df on ds.[sid] = df.[sid]

-- Уфффф

set @switch =  0 
set @switch =  1 
set @switch =  2 

-- Нужно получить ОДИН набор (выборка [sid]_ов) из @ds, в котором содержиться все @df.[is_ro] = 1
-- ГДЕ:
if @switch =  0 
begin
  print 'SUM([size]) минимальный. На Count(*) наплевать'
  -- должно получится
  select [sid]
  from @df 
  where ([is_ro] =  1 ) and ([sid] between  1  and  8 )

  select SUM(s.[size]) [sum], Count(*) as [cnt]
  from @ds as s
  inner join @df as f on s.[sid] = f.[sid]
  where ([is_ro] =  1 ) and (s.[sid] between  1  and  8 )
end
else if @switch =  1 
begin
  print 'Count(*) минимальный. На SUM([size]) наплевать'

  select  11  as [sid]

  select s.[size] as [sum],  1  as [cnt]
  from @ds as s
  where s.[sid] =  11 
end
else begin
  print 'Count(*) минимальный и SUM([size]) тоже минимальный'

  select [sid]
  from @ds
  where ([sid] between  9  and  10 )

  select SUM(s.[size]) [sum], Count(*) as [cnt]
  from @ds as s
  where (s.[sid] between  9  and  10 )
end

select @@version
---
Microsoft SQL Server  2005  -  9 . 00 . 3050 . 00  (Intel X86)   Mar   2   2007   20 : 01 : 28    Copyright (c)  1988 - 2005  Microsoft Corporation  Enterprise Edition on Windows NT  5 . 2  (Build  3790 : Service Pack  2 ) 

-- Нужно получить ОДИН набор (выборка [sid]_ов) из @ds, в котором содержиться все @df.[is_ro] = 1
-- ГДЕ:

set @switch =  0 
--set @switch = 1
--set @switch =  2 

SUM([size]) минимальный. На Count(*) наплевать
sid
-----------
 1 
 2 
 3 
 4 

( 4  row(s) affected)

sum         cnt
----------- -----------
 500           4 

Count(*) минимальный. На SUM([size]) наплевать
sid
-----------
 11 

( 1  row(s) affected)

sum         cnt
----------- -----------
 1000          1 

Count(*) минимальный и SUM([size]) тоже минимальный
sid
-----------
 9 
 10 

( 2  row(s) affected)

sum         cnt
----------- -----------
 850           2 

set @switch =  0 
--set @switch = 1
--set @switch =  2 

ALTER FUNCTION [dbo].[backup_set_id]( 
  @switch       tinyint,            -- "0" - все наборы Backup_ов
                                    -- "1" - минимальный по сумарному размеру набор актуальных Backup_ов.
                                    -- "2" - минимальный по количеству набор актуальных Backup_ов.
                                    -- "3" - минимальный по количеству и размеру набор актуальных Backup_ов.
  @DB           SQL_VARIANT = NULL, -- ID или Name базы, "NULL" - все существующие базы 
  @BoudaryDate  datetime = NULL     -- "Null" = GetDate(). Граница времени от которой двигаясь в прошлое, 
                                    -- собираем Backup_ы базы.
)
RETURNS @Res TABLE ( [database_id] int, [backup_set_id] int )
AS
BEGIN
  declare @id     int,
          @curID  int

  DECLARE @mf TABLE([database_id] int, [file_guid] uniqueidentifier, [is_read_only] bit);

  INSERT INTO @mf
  SELECT *
  FROM dbo.DbFilesOnDate( @BoudaryDate, @DB )

  if not exists(select top  1   1  from @mf)
    RETURN

  set @BoudaryDate = ISNULL(@BoudaryDate, GetDate());

  DECLARE C CURSOR LOCAL FAST_FORWARD
  FOR SELECT [database_id]
      FROM @mf
      GROUP BY [database_id]

  OPEN C
  FETCH NEXT FROM C INTO @curID

  WHILE @@FETCH_STATUS =  0 
  BEGIN
    if @switch =  0 
    begin
      -- Добавляем все Backup_ы c ReadWrite файлами, соответствующие по составу файлов указанной дате (@BoudaryDate)
      INSERT INTO @Res
      SELECT  @curID as [database_id], coalesce(bs.[backup_set_id], bf.[backup_set_id]) as [backup_set_id]
      FROM msdb.dbo.backupset as bs 
      CROSS JOIN (SELECT file_guid, [is_read_only] FROM @mf where (ISNULL([database_id],  1 ) = ISNULL(@curID,  1 ))) as f
      FULL JOIN msdb.dbo.backupfile as bf on bs.[backup_set_id] = bf.[backup_set_id]
        and (bf.file_guid = f.file_guid) and (bf.[is_readonly] = f.[is_read_only])
      WHERE (bs.[type] != 'F') and (bs.backup_finish_date <= @BoudaryDate) 
      GROUP BY coalesce(bs.[backup_set_id], bf.[backup_set_id])
      HAVING  SUM(case when bf.file_guid is null then  1  else  0  end) =  0 
        and   SUM(case when f.file_guid is null then  1  else  0  end) =  0  

      if @@ROWCOUNT !=  0 
      begin -- Такие Backup_ы существуют. Добавляем все RO Backup_ы (какие найдем до максимальной даты RW)
        SELECT @BoudaryDate = MAX(backup_finish_date)
        FROM msdb.dbo.backupset as bs
        INNER JOIN @Res as r ON bs.[backup_set_id] = r.[backup_set_id]

        INSERT INTO @Res
        SELECT DISTINCT @curID as [database_id], bf.[backup_set_id]
        FROM msdb.dbo.backupset as bs 
        INNER JOIN msdb.dbo.backupfile as bf ON bs.[backup_set_id] = bf.[backup_set_id]
        INNER JOIN @mf as f ON (bf.[file_guid] = f.file_guid) and (bf.[is_readonly] = f.[is_read_only])
        WHERE (bs.[backup_finish_date] <= @BoudaryDate) and (bs.[type] = 'F') and
              (bf.[is_present] =  1 ) and (f.[is_read_only] =  1 ) and
              (ISNULL(f.[database_id],  0 ) = ISNULL(@curID,  0 ))
        EXCEPT
        SELECT *
        FROM @Res

        -- А можем ли мы восстановить все файлы ?
        SELECT @id = Count(*)
        FROM (
          SELECT bf.file_guid
          FROM @Res as r
          INNER JOIN msdb.dbo.backupfile as bf ON bf.[backup_set_id] = r.[backup_set_id]
          INNER JOIN @mf as f ON (bf.file_guid = f.file_guid) and (f.[is_read_only] = bf.[is_readonly])
          WHERE (ISNULL(f.[database_id],  0 ) = ISNULL(@curID,  0 )) and (bf.[is_present] =  1 )
          GROUP BY bf.file_guid
        ) as T

        if @id != (SELECT Count(*) from @mf where (ISNULL([database_id],  0 ) = ISNULL(@curID,  0 )))
          DELETE FROM @Res -- на указанную дату нет набора из которого можно восстановить базу
      end;
    end
    else begin
      declare @t table( [backup_set_id] int )

      insert into @t
      select [backup_set_id]
      from dbo.backup_set_id( 0 , @curID, @BoudaryDate)

      if @@RowCount !=  0 
      begin -- Есть что поискать
        -- Ищем простую базу 
        select top  1  @id = n.[backup_set_id]
        from @t as n
        inner join msdb.dbo.backupset as b on b.backup_set_id = n.backup_set_id
        where b.type in ('D', 'P') -- FullBackup, ReadWrite Backup
              and  1  = case 
                        when b.type = 'D' then  1 
                        when exists(
                          select top  1   1 
                          from msdb.dbo.backupfile 
                          where (backup_set_id = n.backup_set_id) and ([is_readonly] =  0 ) and 
                                (is_present =  1 ) ) then  1 
                        else
                           0  end
        order by b.backup_start_date DESC

        if @id is NULL
        begin -- Многобазовый backup (код не проверялся)
          INSERT INTO @Res
          SELECT T.[database_id], T.[backup_set_id]
          FROM (
            SELECT @curID as [database_id], t.[backup_set_id]
            FROM @t as t
            INNER JOIN msdb.dbo.backupfile as bf on (t.[backup_set_id] = bf.[backup_set_id])
            INNER JOIN @mf as f on (f.file_guid = bf.file_guid)
            INNER JOIN msdb.dbo.backupfilegroup as g on (bf.[filegroup_guid] = g.[filegroup_guid])
            CROSS APPLY (
              SELECT top  1  [backup_set_id]
              FROM msdb.dbo.backupfile
              WHERE ([backup_set_id] = bf.[backup_set_id]) and ([file_guid] != f.file_guid) and
                    ([is_present] =  1 ) and (is_readonly= 0 )
            ) as c
            WHERE (f.[database_id] = @curID) and 
                  (g.[name] = 'PRIMARY') and (bf.[is_present] =  1 )
          ) as T
        end
        else begin -- Далее по списку из @t могут быть только актуальные ReadWrite файлы
          INSERT INTO @Res VALUES(@curID, @id)

          -- Ищем последний DIFF Backup
          SELECT @id = MAX(t.[backup_set_id]) 
          FROM @t as t
          INNER JOIN msdb.dbo.backupset as bs on t.[backup_set_id] = bs.[backup_set_id]
          WHERE (@id < t.[backup_set_id]) and (bs.[type] = 'Q') 

          if @@RowCount !=  0  -- Если нашли, то добавляем его
            INSERT INTO @Res VALUES( @curID, @id )
          -- else @id указывает на базу восстановления

          -- Добавляем все файловые backup_ы отдельных RW-файлов и всех логов
          INSERT INTO @Res
          SELECT @curID as [database_id], t.[backup_set_id]
          FROM @t as t
          INNER JOIN msdb.dbo.backupset as bs on t.[backup_set_id] = bs.[backup_set_id]
          INNER JOIN msdb.dbo.backupfile as bf on bs.[backup_set_id] = bf.[backup_set_id]
          OUTER APPLY (
            SELECT MAX(t1.[backup_set_id]) as [backup_set_id]
            FROM @t as t1
            INNER JOIN msdb.dbo.backupset as s on s.[backup_set_id] = t1.[backup_set_id]
            INNER JOIN msdb.dbo.backupfile as f on f.[backup_set_id] = s.[backup_set_id]
            WHERE (t.[backup_set_id] < t1.[backup_set_id]) and (s.type = bs.type) and 
                  (f.[file_guid] = bf.[file_guid]) and (f.[is_present] =  1 ) and (f.is_readonly= 0 )
          ) as app
          WHERE (@id < t.[backup_set_id]) and
                ( ((bs.type = 'G') and (bf.[is_present] =  1 ) and (bf.is_readonly= 0 ) and (app.[backup_set_id] is NULL))
                  or
                 (bs.type = 'L') )
          EXCEPT
          SELECT *
          FROM @Res
        end;

        -- Добавляем все ReadOnly файлы
        if @switch =  1 
        begin
          -- SUM([size]) минимальный. На Count(*) наплевать
          set @switch =  1 
        end
        else if @switch =  2 
        begin
          -- Count(*) минимальный. На SUM([backup_size]) наплевать
          set @switch =  2 
        end
        else begin
          -- Count(*) минимальный и SUM([backup_size]) тоже минимальный
          -- select ??? ... order by SUM([backup_size]) asc, Count(*) asc ???
          set @switch =  3 
        end

        -- Временная заглючка 
        INSERT INTO @Res
        SELECT DISTINCT @curID as [database_id], t.[backup_set_id]
        FROM @t as t
        INNER JOIN msdb.dbo.backupfile as bf on t.[backup_set_id] = bf.[backup_set_id]
        WHERE (bf.[is_present] =  1 ) and (is_readonly= 1 )
        EXCEPT
        SELECT [database_id], [backup_set_id]
        FROM @Res
      end; -- else Если нет максимального набора, то нет и минимального.
    end;

    FETCH NEXT FROM C INTO @curID
  end;

  CLOSE C;
  DEALLOCATE C;

	RETURN 
END

select bs.backup_set_id, bs.type, bs.name, bs.backup_start_date, bs.backup_finish_date, bs.backup_size, 
         bs.backup_set_uuid, bs.differential_base_guid,
         mf.physical_device_name 
  from master.dbo.backup_set_id( 1 , db_id(), '20081107') as a
  inner join msdb.dbo.backupset as bs on a.backup_set_id = bs.backup_set_id
  inner join msdb.dbo.backupmediafamily as mf on bs.media_set_id = mf.media_set_id

backup_set_id type name                            backup_start_date       backup_finish_date      backup_size    backup_set_uuid                      differential_base_guid               physical_device_name
------------- ---- ------------------------------- ----------------------- ----------------------- -------------- ------------------------------------ ------------------------------------ ----------------------------------------------------
2192          P    NULL                            2008-11-03 18:23:45.000 2008-11-03 19:00:57.000 374050857472   396284AB-74BC-41D4-BAB2-0D87D8F97535 NULL                                 \SQL2_RW\BLA_BLA_RW_081103.ARC
2240          Q    NULL                            2008-11-06 04:00:20.000 2008-11-06 04:03:03.000 16590432768    4F1D0628-C123-4D1C-A206-00D2D8A7E0DB 396284AB-74BC-41D4-BAB2-0D87D8F97535 \SQL2_DIFF\BLA_BLA_DIFF_081106.ARC
1948          F    NULL                            2008-01-21 15:18:00.000 2008-08-21 15:48:07.000 146864000512   F4D10F1A-069A-4892-8C37-0F1A621A5EDA NULL                                 \SQL2_RO\BLA_BLA_RO_200802.ARC
1950          F    NULL                            2008-02-21 16:47:30.000 2008-08-21 17:17:11.000 162867233280   7D713D09-9CCE-4D60-8F8C-3171778D8F98 NULL                                 \SQL2_RO\BLA_BLA_RO_200803.ARC
1953          F    NULL                            2008-03-21 17:45:34.000 2008-08-21 18:00:11.000 143686282752   C751FACE-2D0E-46BD-A6B0-3F10F4C1E6E8 NULL                                 \SQL2_RO\BLA_BLA_RO_200711.ARC
1954          F    NULL                            2008-04-21 18:00:17.000 2008-08-21 18:22:20.000 162120507904   19EF91F1-1E68-4406-916C-018C4D120A09 NULL                                 \SQL2_RO\BLA_BLA_RO_200805.ARC
1973          F    NULL                            2008-05-25 12:51:28.000 2008-08-25 13:07:11.000 134167844352   081A6606-7ADA-45C0-951D-9553D17DA156 NULL                                 \SQL2_RO\BLA_BLA_RO_200712.ARC
1974          F    NULL                            2008-06-25 13:07:15.000 2008-08-25 13:22:09.000 129942868480   531BFFEB-6266-4709-9E45-284D358FDF50 NULL                                 \SQL2_RO\BLA_BLA_RO_200801.ARC
1975          F    NULL                            2008-07-25 13:22:12.000 2008-08-25 13:38:47.000 164562461184   EF9B889E-5F50-4AC3-BBDE-C791E6CFCC04 NULL                                 \SQL2_RO\BLA_BLA_RO_200804.ARC
1992          F    NULL                            2008-08-29 13:11:22.000 2008-08-29 13:25:30.000 147641065984   3163F2B0-CF71-42C4-BE46-43D54A6DDE99 NULL                                 \SQL2_RO\BLA_BLA_RO_200806.ARC
1993          F    NULL                            2008-09-29 13:25:30.000 2008-08-29 13:40:01.000 133478998528   4A56A174-E19A-4246-BA52-50D0E07AE732 NULL                                 \SQL2_RO\BLA_BLA_RO_200712.ARC
2077          F    NULL                            2008-09-30 14:58:52.000 2008-09-30 15:14:49.000 154551512576   9CE7754F-62E5-4553-A8FB-F94EE13D1006 NULL                                 \SQL2_RO\BLA_BLA_RO_200807.ARC
2177          F    NULL                            2008-10-29 04:47:00.000 2008-10-29 05:06:20.000 148293220864   28014D6D-0E01-48FE-9167-AA61BD9F343B NULL                                 \SQL2_RO\BLA_BLA_RO_200808.ARC
2182          F    NULL                            2008-11-01 08:22:12.000 2008-11-01 08:22:14.000 73329152       580B88A9-1C7F-400C-B517-6436891E316C NULL                                 \SQL2_RO\BLA_BLA_adm_error_log_200810n4_weekday.ARC 
2191          D    Data Protector: 2008/11/01 0036 2008-11-02 16:39:59.000 2008-11-03 18:23:45.000 1875088187392  01A33A9B-2014-4FBD-9254-6237B7BCAD07 NULL                                 Data Protector_SQL2_BLA_BLA_16_37_41

(15 row(s) affected)