backup query configuration

I am not a SQL DBA but I presume when you setup a new database you also setup a job on how often to backup the database and where to write the backup to. Is there anyway for SQL 2005 to detail exactly what backup schedule is setup, and where the backup is being written to. A query would be useful so it can be done in bulk over several systems.
LVL 3
pma111Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Barry CunneyCommented:
Hi
It is possible to set up backup maintenance plans.
There is a Maintenance Plan Wizard.
http://technet.microsoft.com/en-us/library/ms189953(v=sql.90).aspx

You can add a backup task and also a cleanup task to remove backups older than a given date.
0
pma111Author Commented:
I was more after a query to see how the maintenance plan has been configured
0
Scott PletcherSenior DBACommented:
Actual DBAs won't use a maintenance plan (mp).  I've never set up one on servers I fully manage (although, sadly, I have at times been forced to maintain legacy mps).

There are mp tables, though.  Look for tables in msdb starting with:
sysdbmaintplan
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

pma111Author Commented:
Do you manually do backups then scott isnt that prone to human error ie forgetting?
0
Scott PletcherSenior DBACommented:
Of course not.  Who would do something as critical as backups manually? (Other than the IRS Exchange admins, I guess :-).)
0
pma111Author Commented:
Can you elaborate on your backup procedures for sql then and why they are more effective then using maintenance plans?
0
pma111Author Commented:
Or the problems with mps in general and why dbas dont use them
0
Scott PletcherSenior DBACommented:
MPs are too generic and too broad.  They're also error prone, particularly when you select specific db(s) to process.  If one of the dbs is dropped or renamed, often the entire plan fails or, worse, simply skips the remaining dbs.

DBAs use procs/scripts to do backups and other maintenance tasks.  Probably the best-known version is Ola Hallengren.  I don't use them, because they are not table-driven, but many do.
0
Anthony PerkinsCommented:
I don't use them, because they are not table-driven, but many do.
They can be.  It is a simple task to setup a "BackupSettings" table and use it to drive Ola Halengren's SQL Backup script
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.