backup history

  • Hi,

    i want to know the database backup history.

    It means , i want a query to generate the list of database name and backup time.

    thanks,

    kiran

  • Hi,

    1. Use DOS command to list all files in a given location.

    2. Execute that through SQL and store results in a table with a time stamp.

    You'll see backup file growth over the period of time. If it's too much info than list servers instead of single databases.

    Problem with this is if the backup fails you'll need a seperate process or to analyse the job logs.

    Another problem us that there is no way to establish partial writes.

    Would be nice to hear alternatives,preferably something that doesn't involve SSRS

  • This script here probably answers your question:

    http://www.mssqltips.com/tip.asp?tip=1601

    Gethyn Elliswww.gethynellis.com

  • I have read the article on MSSQL Tips website.

    The system views do provide useful information about the backups.

    In my opinion, the following things are missing:

    1. In their example, you can check if the backup has run in the past 24 hours by doing a select on a system view. This works on the assumption that the backup must run at least once. What you could have, is a backup schedule table where you as a DBA can specify what type of backup you want to perform and how often.

    2. I believe those system views are missing in SQL 2000, you'll have to check this.

Viewing 4 posts - 1 through 3 (of 3 total)

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