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

Backup is one of the most important tasks of DBA. Withouthaving proper backup, you are running in danger mode always so it is good tokeep more than backup file with you always so in any emergency if one backupgets corrupt or missing, you can use other one.
In early days of SQL Server, especially before SQL Server2005, DBA used to take full backup of database and copy it somewhere else viaDOS command XCOPY to make another copy of the same to other disk drive orsomewhere in network but SQL Server 2005+ provides the facility to take backupwith mirror copy so you don’t need to put additional efforts to make copy ofyour database backup and move it somewhere else.
Suppose my database name “SQLHub” so I would use following commandto take copy of that database in D drive and mirror copy in E drive.
–database backup of SQLHub
–full back in Ddrive SQLHubBackup folder
–same backupcopy 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 takebackup 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 inSQLHub.bak file and copy the next so your both backup taken on 8thand 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 wantto remove database back taken on 8th July 2011, which is there withthe 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 butexamples and explanations prepared by Ritesh Shah, founder of
Ask me any SQL Server related question at my “ASK Profile

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: