Sql Backup with Mirrored Server
cfidoe
Posts: 10
We have 2 servers set up to be the primary and mirror server..
Is there any way for Sql backup to know which one is which and backup accordingly?
We have a solution set up for the primary which works fine, but obviously the backup fails when that server switches to Mirror..
Any suggestions are appreciated.
Thanks!
Is there any way for Sql backup to know which one is which and backup accordingly?
We have a solution set up for the primary which works fine, but obviously the backup fails when that server switches to Mirror..
Any suggestions are appreciated.
Thanks!
Comments
Unlike a SQL Cluster, unfortunately SQL Backup does not support SQL Server Database Mirroring.
On a SQL Cluster both or all nodes of the cluster have an installation of SQL Backup Server components. The server components is cluster aware so all nodes of the cluster are installed simultaneously and a resource is created within the Cluster Administrator.
A cluster applies to the whole SQL Instance not individual databases. A database mirror is configured per database.
Both the Principal Server and the Mirror Server have there own independent installations of SQL Backup Server Components. The server components are not aware of the database mirroring. The Witness server will initiate fail over if a problem is detected, so that the mirror database becomes the principal. SQL Backup does not know if fail over has occurred, has no resource is created on the witness server.
The same caveats apply to both native SQL Server Backup and Red Gate SQL Backup:
1. While database mirroring is active, you cannot backup or restore the mirror database. The Mirror database is in a NORECOVERY state, so SQL Server prevents backup's of this database.
2. Although you can backup the principal database, you cannot us BACKUP LOG WITH NORECOVERY
3. You cannot restore the principal database. The mirror will correct itself after failover.
The only possible workaround for the problem is to create a backup job that perform a backup all databases on each server. Databases in a restoring state will be ignored. Possible SQL Backup syntax maybe as follows:
EXECUTE master..sqlbackup '-SQL "BACKUP DATABASES [*] TO DISK = [<path to backup file location>] WITH COMPRESSION = 2, ERASEFILES = 2, COPYTO = [<unc path a network share>], VERIFY" '
Alternatively, you can use the EXCLUDE argument, which will backup all databases except those listed in the EXCLUDE argument. For example, you may just want to backup all user databases:
EXECUTE master..sqlbackup '-SQL "BACKUP DATABASES EXCLUDE [master, model, msdb] TO DISK = [<path to backup file location>] WITH COMPRESSION = 2, ERASEFILES = 2, COPYTO = [<unc path a network share>], VERIFY" '
Once fail over occurs, the mirror database becomes the principal and therefore no longer in a restoring state and the database will then be backed up using SQL Backup.
Many Thanks
Eddie
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com