July 17, 2009 at 9:37 am
My boss has asked me to provide him with a list of databases on every server along with where each backup file (not the data or log file) is physically located. With over 200 servers and 20 databases on each server, is there an easy way to do this? Could someone point me to a script that could list this information for me?
July 17, 2009 at 9:59 am
Hi,
Have a look in;
SELECT * FROM msdb.dbo.backupmediafamily
It contains a number of records for each db, so you will need to get it to only pick up the latest one, but it seems to detail where the backup for each was written to.
I'm not sure if there is a better DMV for this.
Sorry I cant be of any more help.
July 17, 2009 at 10:10 am
Also found this, it may be of some use.
http://www.sqlbackuprestore.com/backuprestorehistorytables.htm
July 17, 2009 at 11:15 am
Do it for one server, then build a set of scripts to pull this together. It sounds like busy work, since this is just documentation and the location can change regularly with each backup. Each backup file ought to be named differently, so this could be dangerous.
You can build a script for one server, then run that with SQLCMD against every server.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply