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?
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
BACKUP DATABASE is terminating abnormally.
Backup failed for Server ‘Test\kj’. (Microsoft.SqlServer.SmoExtended)
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)
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
create database Test
ALTER DATABASE [Test] ADD FILEGROUP [Secondary]
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]
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
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N’Test’
ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [Test]
RESTORE DATABASE [Test] FILEGROUP=‘Primary’ FROM DISK = N’D:\Backupstore\PrimaryFilegroupbackup’ WITH PARTIAL, RECOVERY
–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
–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
–Now we can take normal database backups