[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

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.
0
pma111
Asked:
pma111
3 Solutions
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now