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 |
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
 Posted - 2006-11-28 : 17:56:25
|
| i have a few maintanance plans on one database that i wanna copy acrross the whole plans to another database of same set up... is there a way to do this?instead of re typing all plans? |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-11-28 : 18:26:08
|
| Not really.In SQL 2000, the only thinkg the maintenance plan info in the database actually contains is a list of databases.The maintanance jobs actually hold all the information about the maintenance actions.CODO ERGO SUM |
 |
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 2006-11-28 : 22:08:15
|
| yeah i need to copy accross those maintanance actions |
|
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 2006-12-07 : 22:40:26
|
| there has to be a way to copy maintanance plans instead of retyping them anyone? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-08 : 00:57:52
|
| I don't think too much to the maintenance plans. So I (and others here) have written a stored procedure for what I want, and it works on any of our servers.Basically:Add any new databases to a table of "actions" (with default backup plan of Full Backup + regular TLog backups)Loop through all databases in the table checking to see if a backup is overdue, if so:Make the backup and set the anniversary time for the next backupIt does a bunch of other stuff too - like defragging indexes and updating statistics.I can change the backup "plan" if I want to - but I never have done. If the database is Simple recovery model it skips the Tlog backups (which Maintenance Plan barfs on), if the database is offline / restoring / suspect / etc. it skips it (again Maintenance Plan barfs IIRC) and so on.Tara has routines in her Blog that does all this if you want to go down that route.http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Tara%20Blog%20Houskeeping%20RoutinesKristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-08 : 01:19:47
|
| From your other thread that this is a 180GB database with timeout problems during Full backup you need to move to customised backup and housekeeping procedures IMHO.For others for reference: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76011Kristen |
|
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 2006-12-11 : 23:14:44
|
| thanx |
|
|
|
|
|
|
maintanance plan