Backup Methods
-
I am looking for some advice/ammunition. I started with a new company about 6 months ago and while reviewing the routines and methods here I have found that the present dba is backing up all of the database by the use of a script she found on the internet. I have about 5 years experience as a dba and have never seen this done like this before. In the past I have always used maintenance plans to run both the system and user databases. The problem is, when I suggested that we switch over to a documented and supported method I got the old "it works fine now and I don't want to change the way I do it" answer. I have used some of the basic reasoning for switching over to use maintenance plans but am wondering if anyone else has any other suggestions/arguments?
-
About the only reason I've ever had to not use the maintenance plans is if you want to use differential backups.
I suppose that if the current method provides a stable process there is little or no need to switch, since the wheel has already been reinvented...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Addition to previous reply, i have encountered few issues using Maint plans in SQL 7/2000. If the script is already is place and working fine its best to be left as it is else if something goes wrong after the changes have been done - Be ready to take the blame even though it was not a major issue based on what you have posted the previous DBA is not willing to budge/take change without enough reason
-
I'm just wondering why you think the only way to run a backup is using maintenance plans? What is your DBA doing in their script that suggests it is undocumented or unsupported?
We use batch scripts that use osql to call the BACKUP DATABASE command. We have other batch scripts that call SQLMAINT to run backups using Maintenance Plans, and some that just do a database backup via SQLMAINT without a maint plan.
We run these under an enterprise job-scheduling system so that failures are caught etc and support calls made as appropriate.
Heck, the foundations for some of these scripts probably even came from the SSC script libraries!
It's on the internet too 
-
I can't speak for ramses2nd but I think it's kind of funny that so many people seem to spend so much time reinventing the wheel unnecessarily. I have interviewed several DBA candidates who were really proud of their "accomplishment" in fully automating backups for SQL Server.
In every case there was manifestly no need to spend time doing what they did because they merely replicated functionality that already existed. Their reasons generally boiled down to this: they disliked the maintenance plan "black box" and wanted more transparency in the process.
Also in every case they didn't get the job because while I can appreciate their desire for transparency and control, the maint plans don't do anything magic and you can easily verify that using Profiler if you aren't sure. More than that, generally those who do such things seem to want to build in job security by putting in a custom system that only they are familiar with and which lacks an intuitive GUI, so that even the most experienced DBA would have to spend some time to determine what exactly is being done, and most other people might never figure it out. It seems to me to be more of an ego trip than anything else.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Thanks dc,
I was starting to think that I was going crazy. The reinventing the wheel thing is what comes to mind right away. An analogy someone once gave me was the trunk release button in your car. If you don't know where the button is does it make it right to run a wire out your window and into the trunk so that when you pull on it the trunk opens? It may work, but it's not how the system was setup to work. In addition, if you ever run into a situation where you need to call Microsoft for support in a backup problem they are not going to troubleshoot your custom stored procedure code. They are going to tell you to run the maintenance plan.
-
Maintenance plans are ok for system databases and small sized user databases that have a maintenance window. Try running a maintenance plan on a 24/7 database that's over a few GB in size and then explain to you're users why the database is unavailable or running very slow. The black box, "all or nothing" approach is the one main failing in maintenance plan.
Eg: I only want to reindex the specific tables where the fragmentation level is above 30%. Or how about, I want to take a backup before and after perfoming the maintenance. Only a custom script will allow you this degree of flexibility.
All of our servers here are maintained using a set of scripts that I 've put together myself, this includes my own log-shipping procedures. They are all using standard T-SQL commands that are documented in Books Online. All these scripts work exactly the same on MSDE, Standard and Enterprise editions and yes dcpeterson, they are setup with an intuitive GUI. I'm currently on leave at the moment and the network admin guy had to switch the backups on one server to another location. Took him a grand total of probably 15-20secs.
There is nothing un-supported about using documented commands to maintain your databases. That'd be like Microsoft saying they won't provide support if you run a SELECT, or UPDATE, on your database. What probably wouldn't be supported is if you were using undocumented system procedures and functions, or a third-party tool to perform the maintenance.
ramses2nd, I'd think about getting out of the "wizard" mode of being a DBA. There may come one time when you need to perform a backup of the database in a disaster recovery situation and all you have is OSQL on the command line. How would you run your maintenance plan then?
--------------------
Colt 45 - the original point and click interface
-
Phill,
If the maintenance plans fit your needs, using them doesn't necessarily mean that you are in full "wizard" mode. Yes there are some things that the maint plans won't allow you to do, and if you need to do them, great.
I use QA much more than EM because for most things it's faster and I have more control, but there are some things where EM and the included wizards are much faster and provide a more convienient mechanism for managing tasks, in most situations I have found the maintenance plans to be such an exception.
No, there is nothing unsupported or WRONG about "rolling your own" backup/restore system, but it is unnecessary in most situations so why waste time, unless you really need to?
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Thanks for the input Phil. First off, I am not a "wizard" dba, I have 5 years experience doing this job and am more than capable of running command line administrative functions. Secondly, I was not discussing doing multi facetted reindexing jobs or complex linked processes. As I stated in the original post it is a script that executes a simple backup command. This can easily be accomplished using a maintenance plan which will schedule the job, remove old files, check the integrity of the file and print out a nice report as well. To get into nitty gritty details, the script in question accomplishes this by directly querying the system tables, generating a dynamic sql command and executing it within a cursor.
I really wonder what peoples aversion to using the tools that Microsoft includes with their software is. Granted there are cases when they are not functional enough to accomplish more complex tasks, but simply running a backup? As was stated earlier in the post and from my own past experience, this customization and reinvention of known proven processes seems to be either an ego thing, or a vain attempt at job security. Other than that, if you really feel the need to code set tasks you own way I would suggest Oracle on a Linux platform.
-
One more thing to add here is that many of these custom scripts accomplish their amazing feats by directly accessing and querying system tables. In terms of support, Microsoft has been warning us for years that we shouldn't query system tables because they're undocumented and could change in future releases/service packs.
-
"As I stated in the original post it is a script that executes a simple backup command. "
Ahh ... no ... you didn't mention that.
Yes, many of the custom scripts available do query system tables, but as long as they are documented system tables that is supported. If you're using system tables, then what won't be supported is troubleshooting problems caused by a service pack or hot fix.
If you look in Books Online you will find a topic called System Tables that documents quite a number of the system tables. There is also a topic for System Procedures.
My feeling is that if the script is running fine and is not using any un-documented tables or procedures, then other than ensuring it's functionality is documented, I'd just leave it alone.
--------------------
Colt 45 - the original point and click interface -
Thanks Phil, and thanks everyone else as well. Everyone had some good points and suggestions and it is definitly going to give some stuff to think about over the next while.
-
We run an Enterprise Scheduler and we want to place backups in the jobs prior to and after updating in some cases. By scripting backups, reindexing, etc., we can control the timing of the executions along with all the dependencies involved in an Enterprise wide system. We also backup our databases locally to the server, then copy them to a backup server all through one script. When new DBAs start here, one of the first things we do is take the new hire through the whole process of backups, maintenance, etc. We also felt that IF we would ever need to change platforms, the change would be less drastic if all was scripted and using Perl. There are reasons for doing it differently, other than job security or ego! One should always look at the particular situation and do what is best for that!
-
Agree with dbamark - it's much the same reason we use an Enterprise Scheduling system - we can schedule job dependencies, precedents, start times, "can't start after x time", overrun warnings etc, job failures are monitored 24x7, we have restart procedures in place so that the jobs can be restarted after problems have been fixed without needing a full rerun.
We interface to Enterprise backup systems etc so there are a number of knock-on effects if the backend systems are down - these can be handled by our scripts.
Maintenance plans are fine for simple setups where you don't have tight SLAs etc, and where it doesn't matter if SQLAGENT dies etc. We've had a few cases where the agent has died and not restarted automatically. We've had cases where the agent didn't start after a reboot and not been noticed for a *long* time. The agent is not visible to Operations staff, so they have no idea if things are running or not.
It's not about reinventing the wheel in our case - more like bolting on a Turbo and some performance mods. Our push was to get as much automation as possible throughout the enterprise using a COMMON scheduling package across all platforms - Mainframe, Unix and Windows. Maintenance plans just don't fit the requirements in these cases.
I'd agree with ramses2nd about using the system tables for things like this too. You only have to look at the changes between 6.5 and 7 to see how you could come to grief.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply