SQL Server Performance Forum – Threads Archive

Multiserver Maintenance Plans

Hi guys – good to speak to you all again. New job new situation. Trying to clean up the administration plan in here and have encountered the following problem. Basically, most of their SQL server databases are quite small so I’m planning to use SQL Maintenance plan to back up each DB and I’m planning to do this from a master server (MSX). The problem I am having is when it comes to Backups – the main part of the maintenance plan. I can’t figure out how to get the backup folder to change between different servers. On my master server I have a drive T:Backup that I’m backing up to before the network backup occurs (handled by third party piece of software). I have tried tricking SQL Server by creating a network drive on the target server (there’s just 1 target just now for testing purposes) with the letter T pointing at its own backup location – actually pointing at the parent of the backup location so that in theory T:Backup might also work here – it doesn’t, though the maintenance plan actually reports success! What is the standard way for doing this? On some small servers, backups happen straight on the C drive, others D, others T, others Z etc etc. I want to create a central maintenance plan that can handle the different backup locations on each server – surely a standard thing? Dave.

Can you create a manual job to handle the backups from the master server, rather than depending upon maintenance plans. As those are meant to be flaky and sometimes will return SUCCESS even though the job is half-done or failed. Why not use own log shipping to reduce the stress on network for full database backups.
How many databases are there?
What are their sizes?
How about the availability in case of any failure? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

They are *meant* to be flaky?? Oh dear. Well I have seen a script that will pick up new databases automatically and back them up, so something like that could be useful. To be honest, I can’t understand how they might be flaky since it’s just a front end for T-SQL is it not? I can run profiler and catch all the T-SQL running underneath. I was hoping to be able to use these so that when I leave here in 3 months the people here have a nice graphical interface to use from one single location to manage all their locations. If not possible, I’ll create a script. At least in the script it will be possible to programatically identify the correct backup location. Can you elaborate on the flakiness of these as I have them in use for a couple of servers? Can anyone else provide a solution/answer? Dave. Ask a SQL Server Question
www.matiogi.com

Guys i’m just going to write my own script for this. Can’t believe no one else has written a script to perform something like this. Dave. Small Businesses
www.matiogi.com

]]>