Friday, December 2, 2005

FIX: SQL Server Error 2132

Note update: this is a one-time fix to resynchronize backup devices. Once you do this, you can resume doing normal backups on the backup devices. Thanks to the Data Iguana for pointing this out.



Problem: I have a SQL Server 2000 maintenance job that backs up my transaction logs every ten minutes by running this step:




BACKUP LOG [Tryout] TO [Tx01], [Tx02]
WITH NOINIT,
NOUNLOAD,
NAME = N'Tryout Tx 10 Mins',
NOSKIP,
STATS = 10,
DESCRIPTION = N'Tryout Tx 10 Mins',
NOFORMAT


This returned this error:




Executed as user: Tim. The media set for database 'Tryout' has 2 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.


What was happening is that I'm trying to backup to two different backup devices on two different systems, but one of the devices was not correctly formatted.



Fix:I fixed this with the following statement:




BACKUP LOG [Tryout] TO [Tx01], [Tx02]
WITH INIT,
NOUNLOAD,
NAME = N'Tryout Tx 10 Mins',
STATS = 10,
DESCRIPTION = N'Tryout Tx 10 Mins',
FORMAT


You run this statement once to resync your two backup devices. After that, the normal backup statement will work correctly.



Note: This solution reformats both backup devices, so you will want to do a complete backup to a different backup device prior to doing this.

No comments:

Post a Comment

I moderate comments blog posts over 14 days old. This keeps a lot of spam away. I generally am all right about moderating. Thanks for understanding.