The backup of the file or filegroup "" 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.
Posted by Karthick P.K on January 25, 2012
Database backup fails with below error after piecemeal restore?
I do not have backup for some of file groups and I do not want the data in those file group.
How to drop offline file and file groups after piecemeal restore? I don’t have the backup for some of my file groups.
I came across some of the customers with large databases schedule file group backup for some of file groups in database and ignore some file groups which do not have critical information. They do piece meal restore for file groups for which they have backup and bring the database online during disaster.
They end with below error message when they take backup of database which is piece meal restored. How do we Fix?
Error1
Msg 3007, Level 16, State 1, Line 1
The backup of the file or filegroup "Test2" 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.
Msg 3013, Level 16, State 1, Line 1
Error2
BACKUP DATABASE is terminating abnormally.
Backup failed for Server ‘Test\kj’. (Microsoft.SqlServer.SmoExtended)
——————————
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The backup of the file or filegroup "Test2" 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)
Error3
We also get below error when we try to drop the file which is no more required.
Msg 5056, Level 16, State 2, Line 1
Cannot add, remove, or modify a file in filegroup ‘Secondary’ because the filegroup is not online.
If the unrestored file group is no longer required they can be removed using below queries. Many people get stuck when they
get “Cannot add, remove, or modify a file in filegroup ‘ ’ because the filegroup is not online.“ while they remove file.
This error can be ignored and we can proceed to removing the file group as mentioned in below query.
ALTER DATABASE database_name REMOVE FILE file_name – If this query fails proceed to next file or Remove file group
ALTER DATABASE database_name REMOVE FILEGROUP filegroup_name
Once we issue the REMOVE FILEGROUP query to offline file group they will get in to defunct state and we can proceed taking the database backups like any other normal database.
Sample query to reproduce the above error is below
USE [master]
go
create database Test
GO
ALTER DATABASE [Test] ADD FILEGROUP [Secondary]
GO
ALTER DATABASE [Test] ADD FILE ( NAME = N’Test2′, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.KJ\MSSQL\DATA\Test2.ndf’ , SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Secondary]
GO
BACKUP DATABASE [Test] FILEGROUP = N’PRIMARY’ TO DISK = N’D:\Backupstore\PrimaryFilegroupbackup’ WITH NOFORMAT, NOINIT, NAME = N’Test-Full Filegroup Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N’Test’
GO
USE [master]
GO
ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [Test]
GO
RESTORE DATABASE [Test] FILEGROUP=‘Primary’ FROM DISK = N’D:\Backupstore\PrimaryFilegroupbackup’ WITH PARTIAL, RECOVERY
Go
–Now the database is online and full database backup would fail with error mentioned above. To resolve issues below queries.
ALTER DATABASE [Test] REMOVE FILE Test2
go
–{
–Error
–=====
–Msg 5056, Level 16, State 2, Line 1
–Cannot add, remove, or modify a file in filegroup ‘Secondary’ because the filegroup is not online.
–}
–Above error can be ignored and we can proceed to remove filegroup as shown below.
ALTER DATABASE [Test] REMOVE FILEGROUP Secondary
go
–Now we can take normal database backups
Karthick P.K
MSSQLWIKI 
best backup software said
Very good article! We will be linking to this particularly great content on our site.
Keep up the great writing.
fantast-fabrikken.blogspot.dk said
For most recent information you have to pay a visit web and on internet I found this web page as a finest
site for newest updates.
www said
I recently just like the helpful information anyone present on your articles. I’ll take a note of your current weblog along with test out again below consistently. Now i’m realistically selected We are advised numerous new products suitable right here! All the best . for the!
Top SQL Server blogs from MSSQLWIKI « MSSQLWIKI said
[…] The backup of the file or filegroup "" is not permitted because it is not online. BACKUP c… […]
Referencement Lausanne said
Howdy! This blog post couldn’t be written much better! Looking
at this article reminds me of my previous roommate!
He continually kept preaching about this. I most certainly will forward this post to
him. Fairly certain he’ll have a good read. Thanks for sharing!
air said
Everyone loves it when individuals get together and share opinions.
Great website, keep it up!
friv0 said
Great post. I was checking continuously this blog and I am impressed!
Very helpful information specifically the last part 🙂 I
care for such info much. I was looking for this certain info for a long
time. Thank you and good luck.
الموقع الرسمي لشركة زين الكويت said
This excellent website certainly has all of the information I needed concerning this subject and didn’t know
who to ask.
Xavier said
What’s up, yup his piece of writing is actuually nice and I have learned llot
of things from itt on the topic of blogging. thanks.
Movie said
On Fb, DStv is trying to appease indignant subscribers who complain about just
one film being out there.
คลินิกทันตกรรม ชลบุรี said
Its not my first time to visit this web page, i am visiting this web site dailly and get pleasant facts from
here all the time.