MSSQLWIKI

Karthick P.K on SQL Server

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 

 

 

 

Thank You and Best Regards,

Karthick P.K

 

About these ads

6 Responses to “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.”

  1. Very good article! We will be linking to this particularly great content on our site.

    Keep up the great writing.

  2. 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.

  3. 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!

  4. […] The backup of the file or filegroup "" is not permitted because it is not online. BACKUP c… […]

  5. 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!

  6. air said

    Everyone loves it when individuals get together and share opinions.
    Great website, keep it up!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 2,114 other followers

%d bloggers like this: