SQL Backup Location and Timing details

Question

Post reply

SQL Backup Location and Timing details

ramyours2003

SSChampion

Points: 12268
More actions
March 9, 2010 at 5:35 am

#217648

iam in production area, i want tyo prepare a report which contains the following columns SQL Backup Location , Full Backup - Time,Diff backup - Time,Log backup - Time, can u pls send me the query to find all the inform,ation in sql2000 as well as in 2005
Ur response is highly appreciated .

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

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

Renuka__ Hall of Fame Points: 3656 More actions · Answer 1

Hi,

I think you can query 'backupmediafamily' table in MSDB for backup locations.

[font="Verdana"]Renuka__[/font]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Start here, everything you need is in several of the tables listed:

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/aa615add-54e6-40f5-8b55-3728b26884ee.htm

ramyours2003 SSChampion Points: 12268 More actions · Answer 3

thanks for ur responses the link which u mentioned is not opening .

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Not sure why it doesn't work. Copy and paste it to the address bar, that worked for me.

Or, you could look it up in Books Online by entering System Tables in the Look For on the Index tab.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 5

Take help from -->http://www.mssqltips.com/tip.asp?tip=1860

Also, include type column from backupset table for type of backup performed.

MJ

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 6

ramyours2003 (3/9/2010)
thanks for ur responses the link which u mentioned is not opening .

The link that Lynn posted will work if you paste it into the URL box in SQL Server Help... but it needs to be SQL Server 2008.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rakesh_ SSC Enthusiast Points: 161 More actions · Answer 7

You can try this one :

SELECT DATABASE_NAME,

PHYSICAL_DEVICE_NAME AS BACKUP_LOCATION,

BACKUP_SIZE,

BACKUP_START_DATE,

BACKUP_FINISH_DATE ,

BACKUPTYPE =CASE TYPE WHEN 'D' THEN 'FULL DATABASE BACKUP'

WHEN 'I' THEN 'DIFFERENTIAL BACKUP'

WHEN 'L' THEN 'LOG BACKUP'

WHEN 'F' THEN 'FILE/FILEGROUP BACKUP'

END

FROM MSDB..BACKUPSET BCKSET

INNER JOIN MSDB..BACKUPMEDIAFAMILY BCKFMLY

ON BCKSET.MEDIA_SET_ID = BCKFMLY.MEDIA_SET_ID

- Win. SSCertifiable Points: 6790 More actions · Answer 8

Good one rakesh... 🙂

Cheers,
- Win.

" Have a great day "