Take mirror backup of database in SQL Server 2005/2008/Denali

Saturday, July 9, 2011 |

Backup is one of the most important tasks of DBA. Without having proper backup, you are running in danger mode always so it is good to keep more than backup file with you always so in any emergency if one backup gets corrupt or missing, you can use other one.

In early days of SQL Server, especially before SQL Server 2005, DBA used to take full backup of database and copy it somewhere else via DOS command XCOPY to make another copy of the same to other disk drive or somewhere in network but SQL Server 2005+ provides the facility to take backup with mirror copy so you don’t need to put additional efforts to make copy of your database backup and move it somewhere else.

Suppose my database name “SQLHub” so I would use following command to take copy of that database in D drive and mirror copy in E drive.

--database back up of SQLHub
--full back in D drive SQLHubBackup folder
--same backup copy will be copied at E drive SQLHubBackup folder
      TO DISK = 'D:\SQLHubBackup\SQLHub.bak'
      TO DISK = 'E:\SQLHubBackup\SQLHub.bak'

“With Format” option in backup is not mandatory. If we take backup of database with backup file name SQLHub.bak on 8th July 2011, when we again run the same database backup with same backup file name “SQLHub.bak” on 9th July 2011, SQL Server keeps previous backup of database in SQLHub.bak file and copy the next so your both backup taken on 8th and 9th july 2011 will now be there in SQLHub.bak file, if you have NOT used “WITH FORMAT” option. 

If you want to take backup on 9th July 2011 and want to remove database back taken on 8th July 2011, which is there with the same backup file name “SQLHub.bak”, you have to use “WITH FORMAT” clause.

Reference: Ritesh Shah

Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of

Ask me any SQL Server related question at my “ASK Profile