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