You might have heard of mirror backups, but you don’t know what they are exactly?

Well, in this article we will try to explain in the best way possible what they are, if you should use them and when you should use them. 

What are mirror backups?

A mirror backup is, as the name might suggest, a mirror (exact copy) of the source database. A mirror backup is identical to a FULL backup, but its files are not compressed in an archive (.zip file) and these types of backups cannot be protected through passwords.

Once you make a change in your mirrored database, the change is perpetuated to the mirror database. This is why you should use mirrored backups with caution.

Database mirroring is available only for databases that use the full recovery model. Also, this feature is available only since SQL Server 2005 Enterprise edition.

Also, during the mirroring process, there are a few restrictions to the operations that can be done:

• Doing a backup or a restore of the mirror database is not possible
• Doing a backup of the principal database is allowed, but access to the BACKUP LOG WITH NORECOVERY is denied.
• Restoring the principal database is not allowed.

How to create a mirror backup?

Using T-SQL is the simplest way to do a mirror backup of your database. The general query for this is:

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\MainBackup\AdventureWorks.bak'
MIRROR TO DISK = 'C:\Backup\MirrorBackup\AdventureWorks.bak'
WITH FORMAT

Advantages

• Backup files are always clean and up to date, they don’t contain old and possibly obsolete data.
• Backup files occupy less space on the media in case data is deleted.
• It’s the fastest backup types

Disadvantages

• The main disadvantage, as might be obvious is that if data is accidentally deleted from the main database, then the data is also lost in the mirror.