4

I have a database with two file groups:

1- PrimaryFileGroup
2- ArchiveFileGroup

now I have a backup from first filegroup: Primary.bak. and restore with below script :

USE [master]
GO
ALTER DATABASE MyDatabase
SET SINGLE_USER
    WITH

 ROLLBACK IMMEDIATE;

RESTORE DATABASE MyDatabase
    FILEGROUP = 'PRIMARY'
    FROM DISK = 'C:\Primary.bak'
    WITH PARTIAL, REPLACE 
GO

ALTER DATABASE SaleTabriz
SET MULTI_USER

It successfully restores. but my another tables which are in ArchiveFileGroup, goes into with below error while I'd like to select from them :

The query processor is unable to produce a plan for the table or view 'Customer' because the table resides in a filegroup which is not online

What is my mistake?

Nima Rostami
  • 2,652
  • 3
  • 15
  • 23
  • You need to post a lot more details, ideally a script that reproduces this. – usr Feb 06 '16 at 13:37
  • I don't have any script. all the scripts are refer to Backup and Restore filegroup Wizard. – Nima Rostami Feb 06 '16 at 13:49
  • Check [this](http://stackoverflow.com/q/2055021) and [this](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/56d24cc2-526a-4c60-8870-e2344cec8ef0), may be you will find something helpful. – i-one Feb 08 '16 at 07:22

2 Answers2

0

Sorry, I dont have enough reps yest to post as a comment hence the answer.

Have you tried bringing the secondary filegroups online:

alter database xxxDB modify file (name = 'xxxDB_File1', online)

Note: If the filegroup is in fact offline, i recall that if this doesn't work the only other way I know of to set a filegroup online is by restoring the file from backup.

hamish
  • 447
  • 1
  • 9
  • 19
  • When I Run this script this exception appears : ONLINE' is not a recognized CREATE/ALTER DATABASE option. – Nima Rostami Feb 13 '16 at 05:35
  • Apologies - I was working from memory but i just went back and checked where i got the info from when i did it and found the URL (http://www.sqlnotes.info/2013/05/09/bring-offline-file-online-modify-sql-server-metadata/ ). Please make sure you have a recovery plan in case something goes wrong as this is an "undocumented feature". Let me know how you go. – hamish Feb 13 '16 at 05:58
-1
RESTORE DATABASE MyDatabase
   FROM DISK = 'C:\Primary.bak',
   FILEGROUP = 'PRIMARY',
   FROM DISK = 'C:\Secondary.bak',
   FILEGROUP = 'SECONDARY'
   WITH PARTIAL, REPLACE ;
GO
isa.tzc
  • 1
  • 1
  • unfortunately Your Script is wrong (Exception : 'FILEGROUP' is not a recognized Device Type option.) Please test then answer. – Nima Rostami Feb 09 '16 at 05:47