How can we help you today? How can we help you today?

Autodetect full recovery model databases for TL backup

Every time there is a new database with full recovery model (or changed to), I need to manually script the database name in my scheduled job for it to be going with the regular backups.
I'm very happy now with SQL Monitor triggering me for dua TL backups, but with MOSS and other db_creator apps it was very hard to keep track of such 'events' without scripting alerts.

Is there a way to have the backup script automatically detecting databases with full recovery model? For full database backups we have the * option.

I could probably do this like this topic (http://www.red-gate.com/MessageBoard/vi ... very+model), but then each tl backup would be fired seperatly instead of being one sqlbackup command.
oderks
0

Comments

2 comments

Sort by
  • petey
    Similar to BACKUP DATABASES [*] ..., there is a BACKUP LOGS [*], which will only back up the transaction log for databases with full and bulk-logged recovery models.

    However, it will not check if a full database backup has been performed, so you may get errors if the t-log backup kicks off before the first full database backup for the new/modified database is performed.
    petey
    0
  • oderks
    Wow! :D Never knew this and the GUI itself doesn't generate it this way. For reasons I can understand.

    But it's good enough for me. The moment such an event occurs, the I'll be immediatly alerted by the emails I receive and be triggered for other administrative actions on it. And when I miss it.... I'll be ensured of TL backups once the full backup is made (and of transaction logs being cleared).

    Now I'll just have to plan adjusting this on all instances... :shock:
    oderks
    0

Add comment

Please sign in to leave a comment.