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 |
|
tkizer
Almighty SQL Goddess
38200 Posts |
 Posted - 2003-01-09 : 18:40:00
|
| Is there a way to script out a maintenance plan? I ask because it would be very handy to script out Log Shipping so that it could be easily recreated. We log ship our databases from San Diego to Las Vegas every 15 minutes. We retain 2 days of backups/transaction logs on disk. When the server administrators take the secondary servers down for more than 2 days (rarely happens, but yet it still does), Log Shipping is unable to recover from it because the backup/transaction log files would already be deleted on the primary servers. So, we have to recreate log shipping. Right now we log ship about 10 databases that vary in size from 500MB to 10GB, so it takes a good portion of our time to get it recreated for all of the databases. It would be much simpler to just run scripts to recreate it than to have to fill out those Maintenance Plan Wizard screens. I realize that I don't have to use the Maintenance Plan Wizard to create Log Shipping, but it is so much easier to do it this way than to have to create it by hand. So, is there any way to script out the log shipping plans? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-09 : 19:19:46
|
| Basically a log shipping plan is just a series of standard jobs, so you should be able to script them out without too much trouble. After you create the log shipping plan, look in EM under Management/SQL Agent/Jobs and you'll find them all. You can right-click each job, choose All Tasks/Generate Scripts. And I'll tell ya now, if you read up on the log shipping system procedures, I guarantee you can write up a sproc that builds the entire log shipping plan for every database in about 15 minutes, and once it's done, you'll never have to rebuild them again manually. Just run the sproc and you're done. You can probably just copy and paste the code from the generated scripts and dump them into the procedure. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-01-10 : 04:20:43
|
quote:
And I'll tell ya now, if you read up on the log shipping system procedures, I guarantee you can write up a sproc that builds the entire log shipping plan for every database in about 15 minutes, and once it's done, you'll never have to rebuild them again manually. Just run the sproc and you're done.
I'll go with that. Log shipping is a pretty simple concept. No reason to rely on the server to implement it for you when you can do it yourself easily and know exactly what's going on.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-01-10 : 13:07:22
|
| Ok, I'll give it a try. I don't usually use the wizards just for the plain fact that I feel like I'm cheating as a DBA, but I have always used it for Log Shipping. |
|
|
|
|
|
|
|
|
Scripting out maintenance plans - log shipping