March 16, 2012 at 8:55 am
Hi
I have a SQL Server 2008R2 database set up to use database mirroing, log shipping and a backup schedule of full-backup and differential backup. There is no clustering. This configuration works with no problems.
As the full and differential backups are being stored on one server, but log backups are stored in multiple servers due to the log shipping I want to mirror the backups.
I have set up a script to do the mirrored backup and the first time I run the script, it works fine using the "Mirror To Disk" setting and the restore works from either server. The backups have been mirrored and not striped.
A snippet of the script is:
BACKUP DATABASE DatabaseName
TO DISK = '\\PrimaryBackupServer\DatabaseName.bak'
MIRROR TO DISK = '\\MirrorBackupServer\DatabaseName.bak'
WITH
RETAINDAYS = 30,
FORMAT,
MEDIANAME = 'MIRROR_BACKUP_SET',
COMPRESSION
However as soon as I replace the FORMAT switch with NOFORMAT to allow the next mirrored backup to be appended to the first mirrored backup set I get an error message that i must use WITH FORMAT for a mirrored backup.
How do i set up the mirrored backups to work with a full, differential and log files (log files mirrored by the log shipping routine) mirrored on multiple servers to create coherent backup sets?
BOL and other SQL Admin books seems to stop after explaining how to create a mirrored backup, but not how it can be used as part of a high availability strategy with full, differential and log backups
Regards
Justin
March 16, 2012 at 9:01 am
In general, I would not append backups together. If a file with multiple backups (full, differential, t-log) were to become corrupt, you lose all the backups in that file. I would put each backup to its own individual file.
Other than that, I really can't help. I setup mirroring at a previous employer to provide high availability for the HR and Finance systems, but I didn't setup database backup mirroring.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply