Invalid backup mirror specification. All mirrors must have the same number of members.

When we try to take MIRRORED BACKUP the Err occurred in some situation.

Ok. Let us try to take MIRRORED BACKUP...

BACKUP DATABASE
SQLServerBuddy
TO DISK='C:\Backup\SQLBuddy_20110123.Bak',
DISK = 'E:\Backup\SQLBuddy_20110123.Bak'
MIRROR TO DISK = 'F:\Backup\SQLBuddy_20110123.Bak' WITH INIT, FORMAT
GO

Error Occurred:
Msg 3010, Level 16, State 1, Line 1
Invalid backup mirror specification. All mirrors must have the same number of members.


Reason:
-"TO DISK" and "MIRROR TO DISK" should have same number of Backup Set.
-But, "TO DISK" have two member(C:\ and E:\) and "MIRROR TO DISK" has only one member(F:\), So the media members not matched..

Solution:
BACKUP DATABASE SQLServerBuddy
TO DISK='C:\Backup\SQLBuddy_20110123.Bak',
DISK = 'E:\Backup\SQLBuddy_20110123.Bak'
MIRROR TO DISK = 'F:\Backup\SQLBuddy_20110123.Bak',
DISK = 'G:\Backup\SQLBuddy_20110123.Bak' WITH INIT, FORMAT
GO

Both the media members TO DISK(C:\ and E:\) and MIRROR TO DISK(F:\ and G:\) can work independently.

Here we have used both Backup Stripping(TO DISK - C:\ and E:\, F:\ and G:\) and Mirrored Backup. So, when we try to restore it, the media should be combined (C:\, E:\) and (F:\, G:\)

Let us try to restore it
RESTORE DATABASE SQLServerBuddy FROM DISK='C:\Backup\SQLBuddy_20110123.Bak', DISK='E:\Backup\SQLBuddy_20110123.Bak' WITH REPLACE
GO

(or)

RESTORE DATABASE SQLServerBuddy FROM DISK='F:\Backup\SQLBuddy_20110123.Bak', DISK='G:\Backup\SQLBuddy_20110123.Bak' WITH REPLACE
GO

Note: This option is available only in SQL Server 2005 Enterprise Edition and later versions

2 comments:

  1. Hi Pandian,

    I am very much impress with ur blog, keep go on. You are realy SQL dude! i also bookmark and keep visiting ur site.

    From
    Khan
    Hyderabad.
    SQL-Dude!

    ReplyDelete