August 17, 2007 at 6:49 am
Hello all,
I have a little knowledge in SQL Server 2000. I am Administrating SQL 2000 Servers (running on Win 2000 & Win 2003 Server Systems) at 25 different locations. Everywhere I have to create Automated Backup Plan (from Database Maintenance) for almost same (same Name) Databases and sometimes a few other Databases are also added to it.
I want to simplify it. so that I do not need to Recreate the Database Maintenance Plan everywhere from scratch. Is there any way to transfer Database Maintenance Plan from one SQL Server system to other SQL Server System and do a little changes in it, which is connsistent with the new Server (like change in Drive letters / Partitions, where Backup is to be taken).
Also, please tell me how to take Backup in SQL Server 2000 (using Database Maintenance Plan) on Remote Computer. In DB Maintnce Plan "//DestinationPC/BackupFolder" is not taking.
Eagerly waiting for reply. With sincere regards,
Barun Kumar Tiwari, New Delhi, INDIA.
August 21, 2007 at 9:58 am
Could you use Transfer Jobs Task in the DTS Package?
August 22, 2007 at 12:19 pm
Look at the three tables in the msdb, sysjobs, sysdbmaintplan_jobs, and sysdbmaintplans. We can join these tables with necessary criteria and export to our target server. An example of snippet is:
SELECT * FROM sysjobs sj, sysdbmaintplan_jobs sdj, sysdbmaintplans sdp WHERE sj.job_id = sdj.job_id AND sdj.plan_id = sdp.plan_id AND plan_name = 'DB Maintenance Plan1'
August 23, 2007 at 1:17 am
Hello Sir,
Can you please elaborate the Transfer Job Function. I am not having any knowledge about it.
With regards,
August 23, 2007 at 2:45 am
We use some 'generic maintenance' scripts to do backups, integrity checks, index rebuilds, etc on our servers. They do not use maintenance plans mainly because maint plans are limited in what they can do.
If you want a copy of our scripts, send me a private message with your email address.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 23, 2007 at 8:15 am
If you want to use the basic maintenance plans across servers, the best bet is to use master/target servers and have a central server where you create the plan and send it out. This works for very generic plans as you can't then edit the plans on the target servers.
Transferring the job doesn't work unless you move the plan as well. It's kludgy and by the time you worked through 1 or 2 errors, you'd be better off just setting up the plans.
If you're doing basic backups, indexes, etc., then you might just set up the plans on the servers. How many are we talking about? I used to manage over 100 servers and we just set up a standard plan as part of the install. It almost never changes, but when you have an exception, like a particular db that needs to have a more or less frequent backup, it's nice to have each server running independently with it's own plan.
If you're an ISP type situation, then the master/target probably makes sense as you're adding and remove instances regularly. If these are stable servers, take a few days and set up the plans.
August 30, 2007 at 12:26 am
Steve,
Will you please elaborate of what you posted
August 31, 2007 at 7:19 am
Steve is talking about the MSX administration in SQL 2000. It works very well for centralizing your maintenance plans and all Job Agent functions. On a test server, just Right-click on the Job agent node in the treeview in Enterprise Manager. Choose Multi-Server Administration -> Make this a Master. Follow though the wizard to see how the setup works.
Basically, you configure one server to be a Master. Then, you can enlist your other servers as target servers. You create maintenance plans and jobs on the master server and it transfers the maintenance plans (and changes to them) and the jobs out to the target servers. You can then go to the Master server to see all of the job statuses but each target server is actually doing the work of the backups, optimizations, etc. It all works reasonably well. I have implemented this at two companies and neither had reliability problems doing this.
I have come across three real issues. The first is that if you have your drives configured differently on each server, the maintenance plans don't allow you to select a different backup file destination for each server. This is easily dealt with by putting the backups to the default locations. The next issue is that when selecting individual databases for maintenance, you always get the list of databases on your Master server, not the destination server. So, you really end up restricted to shared maintenance plans using the "All Databases", "All System Databases", or "All User Databases" options. Again, this is typically ok. Finally, if you have any SQL 7 or SQL 2005 servers, they cannot enlist with a SQL 2000 Master server. It is all version-specific.
August 31, 2007 at 8:28 am
Michael has it right. Look up Multi-Server Admin in BOL or Master Server/ Target Server.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply