INF: Understanding MSscheduled_Backups Entries

• Microsoft SQL Server, versions 4.2x

SUMMARY

When you encounter difficulties with SQL Monitor scheduled tape events, it is frequently helpful to review the MSscheduled_backups table to quickly review the events that are scheduled. This article discusses the fields that directly affect what is dumped, and when and where it is to be dumped.

MORE INFORMATION

The MSscheduled_backups table has the following structure for SQL Server versions 4.21 and 4.21a. Starting with SQL 4.2, the 'stat' field was added, and in 4.21 both the 'attempts' and 'email_name' fields were added.

NOTE: SQL Server version 4.2b for OS/2 does not contain these additional fields.

   Event_id        int                  4     NOT NULL
   Database_name   varchar             30     NOT NULL
   Database_owner  varchar             30     NOT NULL
   Database_dump   varchar             30     NULL
   Log_dump        varchar             30     NULL
   Day             tinyint              1     NOT NULL
   Frequency       tinyint              1     NOT NULL
   Start_time      char                 5     NOT NULL
   Enabled         bit                  1     NOT NULL
   Last_dump       datetime             8     NULL
   In_progress     bit                  1     NOT NULL
   Stat            tinyint              1     NULL
   attempts        tinyint              1     NULL
   email_name      varchar             60     NULL

The following chart represents the relationships:

   Activity                         Database_dump   log_dump   stat
   ----------------------------------------------------------------------
   Dump Database to disk               Device         NULL       0
   Dump LOG to disk                    NULL           Device     0
   Dump Database with INIT             Device         NULL       0
   Dump LOG with INIT                  NULL           Device     0
   Dump both DB & LOG with INIT        Device         Device     0
   Dump Database with APPEND           Device         NULL       2
   Dump DB with INIT, APPEND LOG       Device         Device     3
   Dump both DB & LOG with APPEND      Device         Device     4
   Dump Trans with truncate_only       NULL           NULL       5
   Dump Trans with no_log              NULL           NULL       6

The start_time, day, and frequency fields will determine when the scheduled event actually occurs. The time is entered using a twelve-hour clock in SQL Administrator but is converted to the 24-hour clock, and it is entered in the start_time field. The day is the day of the week, and the values start with Sunday with a value of one. Daily dumps will have a day value of zero with a frequency value of one.

The additional frequency values are as follows:

   Dump              Frequency      Day

   Daily                1           day = 0
   Weekly               7           day in {1,2,3,4,5,6,7}
   Bi-Weekly           14           day in {1,2,3,4,5,6,7}
   Monthly Week 1      31           day in {1,2,3,4,5,6,7}
   Monthly Week 2      32           day in {1,2,3,4,5,6,7}
   Monthly Week 3      33           day in {1,2,3,4,5,6,7}
   Monthly Week 4      34           day in {1,2,3,4,5,6,7}