I think your db model is flawed (and kinda heteronormative). It's not bad, but not great either. It would be easier if you had following structure:

Persons : ID, First, LastName

PersonParents: PersonID, PersonParentID

So, most people would have two rows in PersonParents table. This way it's easy to iterate the trees up and down.

But if we stick to your model, your CTE looks wrong too, i think it tries to fetch same rows over and over. If you only wants children using your structure i think this works:

;with members as (
select *
from (
    VALUES  (1000,'Ranjith')
    ,   (1001,'Shilpa')
    ,   (1002,'Ramamkrishna')
    ,   (1003,'Jayasree')
    ,   (1004,'Sabarinadhan')
    ,   (1005,'Sushama')
    ,   (1006,'Shyamala')
    ,   (1007,'Mukundarao')
    ,   (1008,'Ramadevi')
    ,   (1009,'Gopinath')
    ,   (1010,'Reshmi')
    ,   (1011,'Raj')
    ,   (1012,'Pratham')
) members (id, firstname)
)
, Families as (
select *
from  (
    VALUES  (1,1002,1003)
    ,   (2,1000,1001)
    ,   (3,1004,1005)
    ,   (4,1006,1007)
    ,   (5,1008,1009)
    ,   (6,1010,1011)
) family(id, spouse1id, spouse2id)
)
, Families_Children as (
    select *
    from (
    VALUES  (1,1,1000)
    ,   (2,3,1001)
    ,   (3,4,1002)
    ,   (4,4,1008)
    ,   (5,5,1010)
    ,   (6,6,1012)
) t (id, familyid, childid)
)
, family_tree AS (
    SELECT 
        f.Id as FamilyId, 
        f.Spouse1Id as Spouse1Id,
        mfs1.FirstName as Spouse1,
        f.Spouse2Id as Spouse2Id,
        mfs2.FirstName as Spouse2,
        fc.ChildId as ChildId,
        mc.FirstName as Child
    FROM Families f
    INNER JOIN Families_Children fc
        ON fc.FamilyId = f.Id
    INNER JOIN Members mfs1
        ON mfs1.Id = f.Spouse1Id
    INNER JOIN Members mfs2
        ON mfs2.Id = f.Spouse2Id
    INNER JOIN Members mc
        ON mc.Id = fc.ChildId
    
    UNION ALL
        
        SELECT
            f.ID, 
            f.Spouse1Id,
            m.FirstName,
            f.Spouse2Id,
            m2.FirstName,
            fc.ChildId,
            m3.FirstName
        FROM  family_tree c
        INNER JOIN Families f
        ON  f.spouse1ID = c.ChildID
        OR  f.Spouse2ID = c.ChildID
    INNER JOIN Families_Children fc
        ON  fc.familyID = f.ID
    INNER JOIN Members m
        ON  m.ID = f.spouse1ID
    INNER JOIN Members m2
        ON  m2.ID = f.spouse2ID
    INNER JOIN Members m3
        ON  m3.ID = fc.ChildID
)
select *
from family_tree

As you see, it's kinda awkward, cause you have to do this double join and OR thing to get both parents. Also, this model doesn't allow adoptions, unmarried kids or unknown parents.

Finally, I don't think you can combine both up and down actions in one recursive CTE, so you need two to cover the both parents and children.

I took siggemannen 's advice and updated the DB model to have a single Member_Parents table

CREATE TABLE Member_Parents(
    Id INT NOT NULL IDENTITY PRIMARY KEY,
    MemberId INT NOT NULL FOREIGN KEY REFERENCES Members(Id),
    ParentId INT NOT NULL FOREIGN KEY REFERENCES Members(Id),
);

and was able to solve the problem pretty quickly as follows

WITH Family_Tree AS (
    SELECT 
        mp.MemberId,
        mp.ParentId
    FROM
        Member_Parents mp
    WHERE mp.ParentID = 1008

    UNION ALL

    SELECT
        mp.MemberId,
        mp.ParentId
    FROM
        Member_Parents mp,
        Family_Tree ft
    WHERE mp.ParentID = ft.MemberId
)

SELECT 
    ft.MemberId,
    mm.FirstName AS Member,
    ft.ParentId,
    mp.FirstName AS Parent
FROM Family_Tree ft
INNER JOIN Members mm
    ON mm.Id = ft.MemberId
INNER JOIN Members mp
    ON mp.Id = ft.ParentId

The query turned out to be pretty straight forward without too many joins. I also agree that my CTE was incorrect and I guess it was due to my understanding of the recursive CTE (learnt it just a couple of days back).

It would really be appreciated if you could review this query.

I am now trying to figure out a way to fetch the ancestors for a given member (traverse up the tree).

Thanks.

UPDATE Was able to quickly get the ancestor tree as well for a give member

WITH Ancestor_Tree AS (
    SELECT 
        mp.MemberId,
        mp.ParentId
    FROM
        Member_Parents mp
    WHERE mp.MemberId = 1012

    UNION ALL

    SELECT
        mp.MemberId,
        mp.ParentId
    FROM
        Member_Parents mp,
        Ancestor_Tree ft
    WHERE mp.MemberId = ft.ParentId
)

SELECT 
    ft.MemberId,
    mm.FirstName AS Member,
    ft.ParentId,
    mp.FirstName AS Parent
FROM Ancestor_Tree ft
INNER JOIN Members mm
    ON mm.Id = ft.MemberId
INNER JOIN Members mp
    ON mp.Id = ft.ParentId