March 29, 2007 at 2:49 am
Yesterday, I made a small change to the backup on two databases. It was decided that now that they are being more heavily used we should be running differential log backups throughout the day in addition to the nightly ones.
As you can probably guess by now one database backup works fine and the other does not!
The SQL for both database backups is the same:
MAIN BACKUP - each weekday at 10pm
step 1:
BACKUP DATABASE [TheDatabase] TO [TheDatabaseBACKUP] WITH INIT ,
NOUNLOAD , NAME = N'TheDatabase backup', NOSKIP,
STATS = 10, NOFORMAT
step 2:
BACKUP log [TheDatabase] TO [TheDatabaseLOGBACKUP] WITH INIT
DIFFERENTIAL BACKUP - every hour between 7am and 9pm
step 1:
BACKUP log [TheDatabase] TO [TheDatabaseLOGBACKUP] WITH NOINIT
The error I get is:
Executed as user: SERVER\******. The media set for database 'TheDatabase' has 3 family members but only 1 are provided. All members must be provided. [SQLSTATE 42000] (Error 3132) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
March 29, 2007 at 9:37 am
I assume that you use backup device. Did you check if all 3 members are there?
March 29, 2007 at 10:00 am
You are correct - I am using a backup device.
How do I check which members there are?
March 29, 2007 at 10:33 am
The backup device is used with other 2 devices. Backup data are evenly split into these devices (members). To find the members, you can choose Properties by right-clicking device icons. Then, click Content to find your necessary info.
If you can include all members in your script, you should be able to run it. An example is:
BACKUP DATABASE [pubs] TO [myDevice], [yourDevice] WITH INIT
April 3, 2007 at 2:04 pm
Open Enterprise Manager. Expand until you see your database. Right click on it. Select Properties. Go to the tab for the Transaction Log. How many files are listed?
-SQLBill
April 10, 2007 at 4:00 am
November 21, 2008 at 12:38 pm
I get the exact same error. When I list the files in Enterprise Manager, I too see only one member. How can this be?
November 21, 2008 at 1:40 pm
This really started happening after I added another file to the media family, but I promptly removed it because I didn't need it. I think something is out of sync in SQL Server, but I all I did was use Enterprise Manager normally. Is this a known bug?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply