Options
MIRRORFILE and FILEOPTIONS Parameters
Gladiatr
Posts: 28
Is there any way that we can use MIRRORFILE parameter along with the FILEOPTONS parameter when we do the database backup using BACKUP command in SQL Backup 4.5?
What I know from SQL Backup help is FILEOPTION works only with TO DISK and COPYTO parameter.
Please help.
What I know from SQL Backup help is FILEOPTION works only with TO DISK and COPYTO parameter.
Please help.
Comments
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
Let me try to explain it to you what I am trying to achieve.
We have two servers (Production and Backup) running currently in our organization.
An SQL Server instance is running on the production server which has got some critical databases running.
There are also a couple of daily nightly full backup jobs scheduled on the Production server which are responsible for the daily nightly backup of our critical databases.
Every night these jobs does the database full backup on one of the local hard drives on the production server and later the same job copies that database full backup to our Backup server and then all the databases from the Backup server transferred to the Tape drive. This way we have the last nights latest full backup of all our databases on the production server as well as on the backup server and also on the tapes.
I am using the TO DISK command to created the full backup on the local Production server and later using the COPYTO command to replicate the backup to the Backup server. With the help of FILEOPTIONS=4 I am able to overwrite the full backup files on the Backup server.
Using the senerio I mentioned above I can have the full backups of all our database at three different locations i.e. Production, Backup servers and Tape drive.
Now my problem is if lets say last nights job on the production server creates a backup on the local drive which is on the production server and if in any case the backup gets corrupted and later the same job copies the files across the network to the backup server and then later copies to the Tape drive then we end up having a corrupted backup overall on three different locations which is no use to us. Here instead of using the COPYTO command I would like to use the MIRRORFILE command which create the full backup on the production server and later we have the mirror file as well on the backup server. In this case if the backup corrupts on the production server I will have the working mirror file on the Backup server and on Tape.
Here I would like to keep the mirror files for 7 days of each database we have and once the 8 day backup is started we would like the SQL Backup to delete the files only keep the latest 7 days files. I was reading the SQL Backup help and found out that FILEOPTIONS parameter can only work with the DISK and COPYTO command but it does not work with the MIRRORFILE command. Am I right? and also the ERASEFILES and ERASEFILES_ATSTART also works with the FILEOPTIONS and COPYTO command but not the MIRRORFILE command. Am I correct?
Is there any way that I can use the DISK, MIRRORFILE and FILEOPTIONS=1?
Please help.
Thanks.
EXEC master..sqlbackup '-sql "BACKUP DATABASE pubs TO DISK = [e:\backups\pubs.sqb] WITH MIRRORFILE = [\\backup1\share\pubs.sqb], MIRRORFILE = [\\backup2\share\pubs.sqb], ERASEFILES = 7" '
will cause SQL Backup to delete all backup files for the pubs database older than 7 days in the following folders, if you have adequate rights:
- e:\backups
- \\backup1\share
- \\backup2\share
FILEOPTION 2 may be useful for you, if the backup file has the archive flag set by default, and you do not want to delete any files that has not been backed up to tape yet.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
This is exactly what I was trying to achieve. I tried your example on one of my test servers and it worked perfect.
This way I can now keep full 1 weeks backup of all my databases on my Production server and mirror them to my backup server as well. Later every night I can get all my full backups transferred to tapes
Once again Thank you very much.
-Gladiatr