Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jhermiz
3564 Posts |
 Posted - 2005-07-20 : 15:17:52
|
We constantly are running out of space on a specific drive on our server due to db backups. I am really thinking after 1-2 weeks we dont need the old backups of some of our databases.Any admins have a script to delete "old" backups?Thanks,Jon
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url] |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jhermiz
3564 Posts |
Posted - 2005-07-20 : 15:23:28
|
Hmm how would I go about going back to the maint plan wizard settings though? I can see the db backup job however should I recreate the db backup plan ?Thanks Tara
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url] |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-07-20 : 15:25:43
|
| In Management, go to Database Maintenance Plans. Double click on the one that corresponds to your backups. Go to both Complete Backup and Transaction Log Backup and modify the Remove files older than option.Tara |
|
|
|
jhermiz
3564 Posts |
Posted - 2005-07-20 : 15:31:00
|
ok found it thanks have most of your dba routines in place, looks good now.Jon
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url] |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-21 : 02:01:45
|
We solved this by doing FULL backups once a week and DIFFs on a daily basis (plus the original TRANS hourly etc.).However, the wizzard doesn't support DIFFs, so you'd have to write SProcs etc. yourself.Mind you, you could sort out the other failings of Maint. Plan at the same time  Kristen |
|
|
|
jhermiz
3564 Posts |
Posted - 2005-07-21 : 09:34:55
|
Ya I realized that, the problem is some of these backups were created by the original developers of the systems a long time ago, so rummaging through the maintenance plans has been reallllllllllly fun (not!)
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url] |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-21 : 09:43:38
|
| Our SProc backups up everything it finds. It has a table containing database name + backup plan; when the SProc runs it added any new database with a "Full monty" plan - a database can always have its plan reduced to a lesser plan - or no backup even - this way we never forget to back up a new database.So, instead of mucking around with Maintenance Plan Wizzard we just "tune" the backup for a given database - but to be honest thats a Blue moon event, the default gets used for 99% of DBs. The Sproc takes care of DBs that are set to "Simple" or "Read only" or "Off line" etc. and doesn't attempt to do transaction backups when it can't and stuff like that (someone tell me that's fixed for Main. Plan in 2005? )(One thing that is quite handy - when a backup is made its Deletion Date is set (i.e. in an Events Table) - there is a default number of retention-days for each database, but the Sproc that does the backup has an override parameter for that - so, for example, before a new rollout we might do an ad hoc Full Backup and specify a longer retention period "just in case"). It's great for ad hoc backups - just type "usp_MyBackup 'MyDBName'" with an optional Diff/Transaction type parameter and Bingo! the backup is run, and added to the Events list so it gets deleted at the appropriate time.Kristen |
|
|
|
jhermiz
3564 Posts |
Posted - 2005-07-21 : 09:49:00
|
Slick solution
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url] |
|
|
|
|
|
|
|
|
backups to delete...
: