2

Background: SQL Server 2008 R2 mirrored and clustered.

We recently failed over the databases via mirror and everything went smoothly. When it was time to do a differential backup, I got an error so I went to take a look. The error message is:

The backup of the file or filegroup "DB_Filegroup_Archive" is not permitted because it is not online. Container state: "Restoring" (8). Restore status: 8. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. The backup of the file or filegroup "DB_Filegroup" is not permitted because it is not online. Container state: "Restoring" (8). Restore status: 8.

BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. BACKUP DATABASE is terminating abnormally. Cannot open backup device 'J:\SQL Backups\DB\Differential\DB_Differential_Backup_201508241903.bak'. Operating system error 2(The system cannot find the file specified.). VERIFY DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Then I went to take a look at sys.database_files and the state_desc is RESTORING. I thought perhaps it was stuck so I attempted to cluster failover the database instance which fixed another issue (SQL Agent schedule not working), but this issue didn't get fixed.

This is what the error log states:

Filegroup DB_Filegroup_Archive in database DB is unavailable because it is Restoring. Restore or alter the filegroup to be available. Filegroup DB_Filegroup in database DB is unavailable because it is Restoring. Restore or alter the filegroup to be available.

I failed it back to the primary site and the files were ONLINE and great, but when it's on the secondary site, it's set back to RESTORING. I don't understand what's going on...

Can someone please help me?

  • sounds like you may have either momentarily or permanently lost access to the location of your backup file. – swasheck Aug 25 '15 at 18:58
  • Does the sql server service account on the secondary server have full rights to J:\SQL Backups\DB\Differential ? – Kin Shah Aug 25 '15 at 19:18
  • That is not the case because I was able to execute a transaction log backup to that folder. The issue isn't about executing backups. The issue is that two data files/filegroups in my database are set to RESTORING instead of ONLINE. The reason the differential backup is failing is because SQL can't access those two data files to complete the backup. – dajonx Aug 25 '15 at 19:38
  • Have you verified file access to the data file? – SQL Hammer Aug 26 '15 at 14:23
  • Cannot access anything on the data file with the status RESTORING. When the database is on the primary site, however, it is accessible and has the ONLINE status. – dajonx Aug 26 '15 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.