June 20, 2011 at 2:28 am
Hi All,
I'm getting the following error message when I try to back up our SQL Server 2005 CRM database.
System.Data.SqlClient.SqlError: The backup of the file or filegroup "CCT_MSCRM" is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)
It seems this is a really common problem as there's plenty of resolutions on the Internet and have gone through almost every conceivable method of solving this based around issues with the full-text index and none of them resolve it. Whilst I'm convinced it probably *is* a full-text index issue, absolutely none of the methods (including the ones I found on this site) resolve it.
The database is working fine however and people are using the system on a daily basis.
Any help appreciated.
Chris
June 20, 2011 at 6:39 am
The database is working fine, but are you positive the file / filegroup is online?
EDIT: This article is for R2, but it almost mirrors the BOL 2005 entry on File States - http://msdn.microsoft.com/en-us/library/ms190241.aspx
June 20, 2011 at 6:58 am
They appear to be, if I query sys.master_files:
CCT_MSCRME:\DATA\CCT_MSCRM.mdf 0ONLINE
CCT_MSCRM_logF:\LOGS\CCT_MSCRM_log.LDF 0ONLINE
June 20, 2011 at 7:14 am
chris.caines (6/20/2011)
They appear to be, if I query sys.master_files:
CCT_MSCRME:\DATA\CCT_MSCRM.mdf 0ONLINE
CCT_MSCRM_logF:\LOGS\CCT_MSCRM_log.LDF 0ONLINE
FYI: Your .mdf file is not the same as a database file. They may be named the same, but they are actually two different creatures.
I take it CCT_MSCRM is in the Primary filegroup? How many files exist in that filegroup? How many filegroups do you have?
June 20, 2011 at 7:26 am
CCT_MSCRM is in the Primary (and only) filegroup, which has only a single file.
I'm not sure how you mean with the different references to a database file and the MDF. Are there any queries you can recommend I run to check the status of all the relevant files?
June 20, 2011 at 9:04 am
You can query on sys.Filegroups.
June 20, 2011 at 9:06 am
PRIMARY1FGROWS_FILEGROUP1NULLNULL0
June 22, 2011 at 8:03 am
Chris,
I haven't forgotten you. I'm just trying to figure out why your server thinks the filegroup or the file within it is offline. Because that does appear to be the problem.
Is this a production DB or a secondary server (for mirroring / log shipping) that happens to be used for reporting?
June 22, 2011 at 8:07 am
No problem Brandie, I appreciate this is a tricky one!
The server is production (It's our Microsoft Dynamics CRM 4.0 Server) and being used continually, quite happily.
June 22, 2011 at 8:09 am
I almost hate to suggest this, but you might need to do an ALTER DATABASE <MyDB> MODIFY FILEGROUP to see if you can trick the server into thinking the filegroup is back online.
June 22, 2011 at 8:42 am
Before you do anything oddball with the files, can you post the actual backup command being issued?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 22, 2011 at 9:00 am
GSquared (6/22/2011)
Before you do anything oddball with the files, can you post the actual backup command being issued?
In this particular instance, I'm running the backup through SQL Server Management Studio, however any permeation of backup commands through SQL also gives the same errors. Using a simple filegroup one:
BACKUP DATABASE CCT_MSCRM READ_WRITE_FILEGROUPS, FILEGROUP = 'PRIMARY' TO DISK = 'E:\Backup\CRM.bak'
GO
Msg 3007, Level 16, State 1, Line 1
The backup of the file or filegroup "CCT_MSCRM" is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.
----
Same issue with file level backup.
June 22, 2011 at 9:02 am
Also I realise that we're on the SIMPLE recovery model, hence using READ_WRITE_FILEGROUPS, however we had exactly the same issue under a FULL model and I'm led to believe that we should still be able to run the command, even to just test that any backup will work successfully.
June 22, 2011 at 9:29 am
Since you only have 1 filegroup, have you tried a backup without using the filegroup options?
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
June 22, 2011 at 9:32 am
SQLRNNR (6/22/2011)
Since you only have 1 filegroup, have you tried a backup without using the filegroup options?
Yes, and with the same error (or close enough)
Msg 3007, Level 16, State 1, Line 1
The backup of the file or filegroup "CCT_MSCRM" is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply