Restored Database But FileGroup is Not Available

• dbaforever

  Hall of Fame

  Points: 3740

  More actions

  May 12, 2011 at 2:08 pm

  #389517

  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

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  May 12, 2011 at 2:30 pm

  #1323970

  It sounds as if the backup that you are using to restore the filegroup is corrupt.

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• dbaforever

  Hall of Fame

  Points: 3740

  More actions

  May 12, 2011 at 4:20 pm

  #1324010

  Thanks Welsh Corgi.

  I agree that it is possible that the backup file is corrupt.

  It did not have a .bak extension so I renamed it as .... .bak, then restored.

  The restored database "looks" ok. The db Properties shows that all the expected File Groups are there.

  Is there a step that "forces" a file group to come online?

  Thanks, John