Script log backups

dwjongbloed

I have an interesting question. I am wanting to come up with someway to back up my T-logs so I can run a script on my DR server to restore them. I have this running for my full backups but my log backups are a little bit more tricky as I am doing one every hour. Right now the backup is doing a <AUTO> for the name but I am not sure of how to go about what it is I am wanting to do. Any ideas would be greatly appreciated.

Thanks

petey

1. Restores can use search patterns.

E.g.

EXEC master..sqlbackup '-sql "RESTORE LOG AdventureWorks FROM DISK = [g:\backups\AdventureWorks*log*.sqb] WITH NORECOVERY"'

will cause SQL Backup to pick up all files matching the search pattern 'AdventureWorks*log*.sqb' in the 'g:\backups\' folder, sort them, and restore them in order.

2. Successfully log files can be moved to another location, using the MOVETO option.

E.g.

EXEC master..sqlbackup '-sql "RESTORE LOG AdventureWorks FROM DISK = [g:\backups\AdventureWorks*log*.sqb] WITH NORECOVERY, MOVETO = [g:\backups\restored]"'

will move all successfully log backup sets to the ':\backups\restored' folder.

The above 2 features of SQL Backup should help to address your requirements.

dwjongbloed

thanks for the reply Petey. I like the fact that Restores support search/wildcards but now here's another potential issue. Everyday I am restoring the "FULL" back up of the database(s). I only want to restore the logs after the given Full back up time for the given day. Any Ideas on how to do this?

I am thinking that this is going to be more difficult than I want it to be and will just end up writing instructions to do it in the GUI screen as I am needing this in case we have a disaster and I am not around

petey

SQL Backup doesn't have the capability to know if a trx log backup file should be restored, or skipped.

If the full restore is a scheduled job, I would add a job step before the restore step, to first move all existing trx log backup files to another folder (e.g. using the DOS MOVE command). In this way, any trx log backups that exist in the folder after the full restore are 'relevant' to that full restore.

If it's a manual restore in a disaster recovery situation, I guess you'll just need to manually sort the files first.