DUPLICATE BACKUPs ? 2

[mirceapop14]

I am new to SQL 2000. I have a job who backup a database in a location. I need to backup same database in another location in network at different hour, and if location is not available to retry until backup is done. How can i do that ?

 

[mrdenny]

You can create a seperate backup job, with a second step in it. Have the backup command be in the first step. If the first step fails have to go to the second step. The second step should contain a wait command to have the SQL server pause for a few minutes.

waitfor '00:01:00' /*This will wait for 1 minute*/

Something like the above, but with what ever duration you want.
When the second step completes have it return to the first step.

Any reason that you need to backup the database a second time instead of just copying the first backup to the second location?

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[SQLBill]

I agree with MrDenny's last comment...

WHY???

Do you ONLY do FULL backups? If not, this will really mess up your backups and restores.

A Differential Backup is of all the changes since the LAST Full backup. A transaction log backup is of everything since the last backup of any kind. Sooooo

At 1 you do a full backup to location A
At 2 you do a transaction log (TL) backup to location A
At 3 you do a full backup to location B
At 4 you do a TL backup to location A
etc...

Once you do a second Full backup (location B), all of the following backups (DIFF or TL) belong to that backup and not to the first Full backup (location A).

So, if you want to do a restore and use LocationA full backup, you will only be able to restore up to the time you did a full backup to LocationB.

So, what's the point? Instead, do what MrDenny suggests...do the backups to one location and COPY the backup files to a new location.

-SQLBill