:::: MENU ::::

WITH Format option for mirrored backup devices

  • Dec 27 / 2011
  • 0
Backup and Restore, DBA Interview questions, dbDigger, Disaster Recovery

WITH Format option for mirrored backup devices

WITH clause when used in backup statement has various associated options. I have discussed effect of couple of these associated options

  • INIT/NOINIT
  • FORMAT/NOFORMAT

In most of the cases both of above options do not make major difference when used while creating backups on disk rather than tape drive. However i got error when using these options for mirror backups. For mirror backups INIT was not producing same results as that of FORMAT. Consider following scenarios
Try to create full backup of AdventureWorks along with a mirror backup. In following script INIT option is provided

 
BACKUP DATABASE AdventureWorks  
 TO DISK = 'C:AdventureWorks.bak'  
 MIRROR TO DISK = 'C:AdventureWorks1.bak'  
 WITH INIT  
 GO  

Previously backup device is not present but mirror backup script would produce following error even with or without INIT option.

Msg 3215, Level 16, State 1, Line 1
Use WITH FORMAT to create a new mirrored backup set.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

By providing WITH FORMAT argument as suggested in error message, backup along with mirror backup would be created successfully

WITH Format option for mirrored backup devices

Once mirrored media set is created you may append later backups to same mirrored media set without using WITH FORMAT. In case of adding new mirror to existing media WITH FORMAT would be required again.

Consult us to explore the Databases. Contact us