March 25, 2011 at 8:13 am
Hello ninjas,
Yesterday we set some file groups to read only that we are obviously no longer writing to. This database is set as simple recovery.
To begin my backups I first backed up the readwrite:
BACKUP DATABASE [dbname]
READ_WRITE_FILEGROUPS
TO DISK= 'X:\...\...\dbname.bak'
WITH INIT
Then I ran the statements to backup the read only:
BACKUP DATABASE [dbname]
FILEGROUP = 'FG_Nov'
TO DISK = 'X:\...\...\Archive\FG_Nov_20110324.bak'
All of these backups worked just fine. However, when my differential ran last night it failed with the following error:
"The differential backup is not allowed
because it would be based on more than one base backup. Multi-based
differential backups are not allowed in the simple recovery model, and are
never allowed for partial differential backups.
BACKUP 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.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 11:36:37 PM
Finished: 11:36:38 PM
Elapsed: 0.781 seconds
Is this a matter of simply running my backup for the read write one more time to recreate just 1 base?
Thanks,
Jay
Jared
CE - Microsoft
March 25, 2011 at 8:54 am
Is the name of your database read_write_filegroups? Or is this a filegroup backup (typo)?
I think this might help you: http://msdn.microsoft.com/en-us/library/ms190218.aspx
The best solution would be, If I understand your situation correctly, would be to make sure you to do a file backup of your read only groups, then a file backup of the read/write groups, then do a partial differential.
Your backup above looks like a full backup, then a partial, missing a partial of the read/write filegroups. I think the way you did it, you created a multiple base backup. Perhaps a single filegroup backup of your read/write filegroups will help. I'll try to test here shortly.
March 25, 2011 at 9:04 am
I'm sorry if that was confusing... [dbname] is what I am typing here to illustrate the name would be there.
I am running the partial on read_write now to see if that will reset the base.
Thanks,
Jay
Jared
CE - Microsoft
March 25, 2011 at 9:32 am
It seems that rerunning the backup on read_write only did not allow me to do a normal differential.
I then tried this script with success:
declare @backupfile nvarchar(2000)
set @backupfile = 'X:\...\...\dbname_' + convert(nvarchar(50), getdate(), 112) + N'.bak'
BACKUP DATABASE dbname
READ_WRITE_FILEGROUPS
TO DISK = @backupfile
WITH DIFFERENTIAL
The reason I was avoiding this is that on another server with the exact same database (the one mentioned above is replicated) a normal differential backup is working...
Strange...
Jay
Jared
CE - Microsoft
March 25, 2011 at 9:56 am
That is strange. I wouldn't think it would matter, but I'm glad it's working. I'll dig in later and see if I can come up with something.
March 25, 2011 at 10:05 am
Thanks for your help!
Jay
Jared
CE - Microsoft
March 25, 2011 at 10:26 am
Are you saying you ran a full database differential backup after running two individual filegroup backups and it gave this error?
This is expected functionality in the Simple recovery model, you cannot have multiple base backups for a single differential backup, even if one of your filegroups is read only:
http://msdn.microsoft.com/en-us/library/ms178046.aspx
You'd need to change your recovery model to do this - bear in mind that BOL cautions against having multiple bases anyway as it can make recovery difficult in some scenarios.
March 25, 2011 at 10:52 am
On the machine experiencing the issue, I ran a partial on read_write_filegroups and then 8 individual read only filegroup backups. Then, that night I ran the differential without specifying read_write_filegroups and it failed. My differential now runs fine today when using the read_write_filegroups within the backup script.
What we found on the other server was that the differential was working fine without read_write_filegroups because a service set up by a different department was making a FULL backup every night, and then our job was doing a differential in the morning. So, even though we thought that both servers were the same (running a partial backup on Sunday and then daily differentials) they were actually not the same.
So even though I thought that the differential should work without the read_write_filegroups because it was working on another server, I found it was not working because the other server had an extra condition (nightly FULL backup) that was creating only 1 base.
Have I understood my scenario correctly?
Thanks,
Jay
Jared
CE - Microsoft
March 25, 2011 at 11:03 am
Yep, that makes sense.
Basically, in the simple recovery model, if you're doing a differential backup, all the data you're backing up must have had the full backup it's based upon performed by a single backup operation.
In your other environment this was being satisfied by the full backup (e.g. your differentials were not differentials of your filegroup backups, but of the FULL backup performed later).
This is the sort of thing that makes recovery of differentials awkward - if you hadn't found out about those FULL backups and needed to restore your differentials, you'd be out of luck.
You are able to perform a FULL backup of a particular set of files and then do a differential of the exact same list of files only if no other full backup operation has been performed on a subset of the same files in the meantime.
I'd generally advise knowing what you're doing and to think hard about the recovery situations with partial backups (especially when roaming into partial differentials) as you can easily find yourself in trouble.
March 25, 2011 at 11:07 am
Great! Thanks!
Jay
Jared
CE - Microsoft
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply