Post reply

Restored from backup but Filegroups are Offline ?

  • May 23, 2011 at 4:50 pm

    #389573

    hi experts,

    I'm trying to restore a 2008 backup to a 2008 server where it has never existed. This db uses separate filegroups for the indexes. Also, the original db was in "2005 compatibility" mode but was on SQL Server 2008.

    This is how I restored onto a different 2008 instance:

    RESTORE DATABASE WFCDBMesaba FILEGROUP='PRIMARY'

    FROM DISK = 'E:\Mesaba\MESABA KRONOS BACKUP.BAK'

    WITH PARTIAL, NORECOVERY, MOVE N'tkcsdb_Data' TO N'E:\Microsoft SQL Server\Data\WFCDBMesaba_data.mdf',

    MOVE N'tkcsdb_log' TO 'E:\Microsoft SQL Server\Data\WFCDBMesaba_log.ldf';

    RESTORE DATABASE WFCDBMesaba FILEGROUP='tkcs1'

    FROM DISK = 'E:\Mesaba\MESABA KRONOS BACKUP.BAK'

    WITH PARTIAL, NORECOVERY, MOVE N'tkcsdb_data_tkcs1' TO N'E:\Microsoft SQL Server\Data\WFCDBMesaba_data1.ndf',

    MOVE N'tkcsdb_log' TO 'E:\Microsoft SQL Server\Data\WFCDBMesaba_log.ldf';

    RESTORE DATABASE WFCDBMesaba FILEGROUP='tkcs2'

    FROM DISK = 'E:\Mesaba\MESABA KRONOS BACKUP.BAK'

    WITH RECOVERY, MOVE N'tkcsdb_data_tkcs2' TO N'E:\Microsoft SQL Server\Data\WFCDBMesaba_data2.ndf',

    MOVE N'tkcsdb_log' TO 'E:\Microsoft SQL Server\Data\WFCDBMesaba_log.ldf';

    From what I see in Management Studio, the db looks to be OK.

    *** But when I try to query a table, I get this error:

    "Filegroup tkcs2 is unavailable because it is offline. Restore or alter the filegroup to be available" ***

    Do I need to run an Alter Filegroup statement or ??

    Thanks for any ideas. John

  • May 23, 2011 at 5:34 pm

    #1328790

    You may want to change it up.

    Try the restore this way

    RESTORE DATABASE WFCDBMesaba FILEGROUP='PRIMARY'

    FROM DISK = 'E:\Mesaba\MESABA KRONOS BACKUP.BAK'

    WITH PARTIAL, NORECOVERY, MOVE N'tkcsdb_Data' TO N'E:\Microsoft SQL Server\Data\WFCDBMesaba_data.mdf'

    ;

    RESTORE DATABASE WFCDBMesaba FILEGROUP='tkcs1'

    FROM DISK = 'E:\Mesaba\MESABA KRONOS BACKUP.BAK'

    WITH PARTIAL, NORECOVERY, MOVE N'tkcsdb_data_tkcs1' TO N'E:\Microsoft SQL Server\Data\WFCDBMesaba_data1.ndf';

    RESTORE DATABASE WFCDBMesaba FILEGROUP='tkcs2'

    FROM DISK = 'E:\Mesaba\MESABA KRONOS BACKUP.BAK'

    WITH RECOVERY, MOVE N'tkcsdb_data_tkcs2' TO N'E:\Microsoft SQL Server\Data\WFCDBMesaba_data2.ndf',

    MOVE N'tkcsdb_log' TO 'E:\Microsoft SQL Server\Data\WFCDBMesaba_log.ldf';

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply