What are the challenges you face when working across database platforms? Take the survey

Autodetect full recovery model databases for TL backup

oderksoderks Posts: 67 Bronze 2
edited March 9, 2011 10:23AM in SQL Backup Previous Versions
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.


  • Options
    peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    oderksoderks Posts: 67 Bronze 2
    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:
Sign In or Register to comment.