I know that I can have a maintenance plan do a Full Backup to a local drive as well as a UNC path so that I have it off-server in case the server is destroyed.
I'd like to do Transaction log backups so that I can do a point-in-time recovery. I'm thinking that I can't just do the same thing because then the sequence of the transaction logs would be mixed across the two backup destinations.
i.e. If I do a transaction log each hour to locations A and B, the last transactions going to B won't include the ones that went to A.
If there's no way around that, I'm thinking the best way to do it would be to do the Transaction log backups only to the local drive and then have a Robocopy to periodically send them to the UNC location.
Am I missing something? Any help much appreciated!
My goal would be to have the Full Backup and Transaction Log backups scheduled so that both are regularly going to the two different destinations.
Copy-only looks like a good option for ad-hoc backups as well as for a full-backup that doesn't break a differential chain.
For transaction log backups, I'm thinking what would be needed would be for SQL Server to keep track that the sequence going to the local drive and know that it is independent of the sequence going to the UNC path.
I'm not seeing a good way to do that, besides doing it outside of SQL Server: Schedule transaction log backups in the Maintenance plan to go to the local drive, then for the UNC destination, make a copy of those transaction logs using Robocopy or the like... or am I not understanding something still?
If you are have Enterprise Edition you can specify the MIRROR TO option - which will create up to 3 secondary backups on the mirror locations.
Note: if you use the mirror to option and one of the locations is unavailable then the backup will fail. Backing up across the network to a UNC path will also take longer than backing up locally and can cause more failures during the backup operation.
You will probably be better off running the backups locally and including a step to copy the files to the UNC location.
You can specify multiple TO destinations in the BACKUP command.
BACKUP LOG db_name TO DISK = 'c:...', DISK = '\\remoteserver...'
You might find it easier to just copy the local backup to a remote server, not least because you can compress the backup before you transmit it, but you could also put out two backup files rather than one from the same backup pass.
Specifying multiple DISKs in the TO clause splits the backup across multiple files and you need ALL those files to restore from. The MIRROR TO is needed to write multiple redundant copies and that's an Enterprise only feature.
Personally I'd go with DFS Replication or a Robocopy script to replicate the files once the backup completes.
Oops, right. Sorry, mixed up my dbms's there. Some put out separate copies with effectively the "TO" clause. MIRROR is the right method for SQL Server.
If you're doing two separate full backups AND you're doing transaction log backups, one of the full backups should have the "copy only" flag set. Otherwise, you'll break your log chain.
Not really - transaction log chain is not dependent upon a specific full backup. You can restore from any full backup as long as you have an unbroken chain of log backups to the point in time you are restoring, even if there are multiple full backups taken between the full backup being restored and the point in time.
Differential's - on the other hand - are dependent upon the latest full backup taken prior to the differential backup.
The only time you really need to worry about copy only backups is if you are using separate technologies to perform the backups - and the full backup is not easily available. For example - performing a SQL native full backup and then performing a VM server backup that also backs up SQL Server, then a differential - the diff will be tied to the VM backup and not the SQL native backup.