Simultaneous SQL databases backups to an alternate location on Disk using Backup Exec
Many times if we want to make two copies of same backup set then we have to fully depend on the Duplicate backups which backs up the data from the first media to second. The main problem in this is that Duplicate job will start only after the Full is completed. Also we cannot select just specific data to be duplicated if the duplicate job is linked to the source job.
So if we have a scenario in hand where we just need to have another copy of backup of our SQL databases then there is an option which we can use in Backup Exec. This option is called “Create on-disk copies of SQL backups to be placed on the SQL server where the database is located“. You can find this option in Backup job properties under Settings >> Microsoft SQL as shown in below figure (1):
Here you need to specify the path where you need to keep the backup file of SQL databases which will be in .BAK format. What this option does is simultaneously back up a SQL database to storage media destination that you have selected (Tapes, B2D, Removable B2D, etc) while also writing a copy of the database to a disk path you specify in the “Save to path“after selecting the option “Create on-disk copies of SQL backups to be placed on the SQL server where the database is located“
The default path to save this files is C:\TEMP folder but this option is not enabled by Default and needs to be selected to use it. You can also change the location where the files needs to be saved.
For every database .BAK file is saved in a separate folder with the name of that Database. As you can see on Figure (2), for SQL instance called BKUPEXEC, the backup files for three databases (Master, Model and BEDB) are stored in different folders.
Also it creates .BAK file for Full, Differential as well as Log backups of SQL databases as you can see in Figure (3) shown for Model database
This option gives IT administrators/Users the ability to back up SQL databases while also providing database administrators with copies of the database on disk, which can be used for such things as tests and restores.
Following are the advantages of using the option “Create on-disk copies of SQL backups to be placed on the SQL server where the database is located“ :
1) Administrator does not have to fully depend on Duplicate backup jobs for SQL databases.
2) As On-Disk copy is created simultaneously it saves time then duplicating the same amount of Data.
3) The .Bak files of SQL can be used for emergency restore of SQL Database using the SQL Management Studio. This will be very handy when the tapes are off-site and SQL needs to be restore ASAP
4) With the .BAK file and Administrator can restore it to another instance of SQL on the same server or to another SQL server and will not have to go through all the Backup Exec process of installing RAWS and all
5) By testing the restore Administrator can make sure that the backups are valid and will not have any problem during the restore process in major disaster situation.