Post reply

How to view database properties, last database backup and last transaction log backup using query?

  • October 4, 2006 at 9:28 pm

    #114478

    Hi,

    I have a new project on asp.net to make a UI like the enterprise manager to create enterprise manager in asp.net.

    I can already query the database properties but the database status is different, supposed to be NORMAL but the value is ONLINE.

    How can I query the last database backup, last transaction log backup and maintenance plan?

    Please help me...

     

     

     

    GiantHornet

    MAY THE FORCE BE WITH YOU...

  • October 5, 2006 at 1:57 am

    #664238

    hi

    use the backupfile and backupset tables in msdb. i think these table should help you although i myself have not used them and dont know much about them. There are other system tables, just look in BOL.

    "Keep Trying"

  • October 5, 2006 at 2:30 am

    #664248

    This should help you in getting latest backup taken from QA.

     

    SELECT TOP 10 BS.backup_finish_date, BMF.physical_device_name

    FROM msdb.dbo.backupmediafamily BMF

    JOIN msdb.dbo.backupmediaset BMS

    ON BMF.media_set_id = BMS.media_set_id

    JOIN msdb.dbo.backupset BS

    ON BS.media_set_id = BMS.media_set_id

    WHERE BS.database_name = <DB_NAME>

    AND BS.backup_finish_date

    BETWEEN <FROM_DATE> AND <TO_DATE>

    ORDER BY BS.backup_finish_date DESC

     

    ------------
    Prakash Sawant

    http://psawant.blogspot.com

  • October 5, 2006 at 11:29 pm

    #664508

    Thanks for the reply guys. Its already work for the Last Database backup info, Im still looking to query the Last Transaction Log Backup and Maintenance Plan.

    GiantHornet

    MAY THE FORCE BE WITH YOU...

  • October 6, 2006 at 1:00 am

    #664514

    ITS OK ALREADY, ITS ALL ON THE TABLE MSDB. THANKS GUYS. I REALLY APPRECIATE YOUR HELP.

    MAY THE FORCE BE WITH YOU...

    GiantHornet

    MAY THE FORCE BE WITH YOU...

  • October 6, 2006 at 1:04 am

    #664515

    did you got the tables in which this info is stored?

    can you share that info with us as well?

     

    ------------
    Prakash Sawant

    http://psawant.blogspot.com

  • October 6, 2006 at 1:06 am

    #664517

    Look for the table name "msdb". All information about the databases, objects are there including backups.

    GiantHornet

    MAY THE FORCE BE WITH YOU...

  • October 6, 2006 at 1:11 am

    #664519

    Im so sorry, not table name BUT DATABASE NAME "msdb".

    GiantHornet

    MAY THE FORCE BE WITH YOU...

  • October 6, 2006 at 1:11 am

    #664518

    oke, thanks for the info.

    I know that all the backup info is stored in MSDB db, but i was looking for some specfic query if at all you know it.

     

    ------------
    Prakash Sawant

    http://psawant.blogspot.com

  • October 6, 2006 at 1:16 am

    #664520

    You can already make simple query to retrieve the info using the tables, you only have to know the information about the table, columns and data. You can search for the info's on the books online of the sql.

    GiantHornet

    MAY THE FORCE BE WITH YOU...

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply