Database Simplified Headline Animator

Database Simplified

Thursday, 10 November 2011

Database Backup At Multiple Location Using Mirror In SQL Server

SQL Server 2005+

At times we come across issue where we need multiple copies of database at different places. Many people end up taking single backup of database and making required copy of backup and moving them to required location using some cmd or script. But now this can be achieved using MIRROR with backup command. Mirror option allows you to create maximum 4 copies of database backup at a time.

Create Database with Following Script.

Create Database MakeMultipleCopies

Take Backup With Mirror Option.

Backup Database MakeMultipleCopies To Disk='D:\Copy1.Bak'
Mirror TO Disk ='E:\Copy2.Bak'
Mirror to disk='C:\Copy3.Bak'
Mirror to disk='D:\Copy4.Bak'
With Format

Output :


As I mentioned earlier we have max limit with mirror we cannot use more then 4 backup mirror. Let’s try it and see what happens when we use 5th mirror. See Following Screen.


So, You can see that the error message clearly says only 4 are allowed. So in all its really cool to have multiple backup with single backup statement without putting effort of copy and pasting it manually or using scipt or command.