August 25, 2015 at 12:04 pm
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?
August 28, 2015 at 7:06 am
BACKUP DATABASE is terminating abnormally. Cannot open backup device 'J:\SQL Backups\DB\Differential\DB_Differential_Backup_201508241903.bak'.
Have ypou check if the file exists and the service account has access to the path.
Also You mentioned fail over on mirroring , so I guess you are referring to the Primary. Have you taken a full backup on a new backup set ?
August 28, 2015 at 7:11 am
The differential and full backups are failing because two data files are stuck in RESTORING state. I have manually taken a transaction log backup to the differential backup folder and it works.
I did [mirror] fail over to the secondary site and that is when the two data files are in RESTORING state. However, when I fail it back to the primary site, those two data files are in ONLINE state. I have checked that I have enough disk space so that's not an issue.
August 28, 2015 at 7:15 am
Files are not in restoring state is the database that is in restoring state.
Could you try this:-
On the Server were the database is in ONLINE state ( PrimarY), take a full backup , followed by Differential backup and then t log backup and check if they all go through.
Try taking a database backup instead of a File and FileGroup backup
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply