Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

backup history Expand / Collapse
Author
Message
Posted Tuesday, January 5, 2010 2:42 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 10:41 PM
Points: 424, Visits: 1,048
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
Post #841923
Posted Tuesday, January 5, 2010 3:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 16, 2011 6:57 AM
Points: 126, Visits: 102
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
Post #841952
Posted Tuesday, January 5, 2010 4:10 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:04 AM
Points: 1,030, Visits: 2,795
This script here probably answers your question:

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


Gethyn Ellis

gethynellis.com
Post #841962
Posted Tuesday, January 5, 2010 11:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 16, 2011 6:57 AM
Points: 126, Visits: 102
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.
Post #842277
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse