| Author |
Topic |
|
Sitka
Aged Yak Warrior
571 Posts |
 Posted - 2004-04-08 : 08:27:04
|
| I feel obligated to set backups at least partially via DBMaintenance plan so someone who dosen't know how to script SQL server would have a chance at taking over administration. The hit by a truck scenerio. What would you do? This is going to be on an SQL 2000 standard licence so the logshipping part will be home grown but I could still have the full and tlog backups being done in a Maintenance plan.I'm trying to look out for the next guy, but patronizing may not be doing any favors. J.It is a rich creamy color with a high fat content of 5-7 percent. Being so high in fat, it is usually processed into butter, cheese, or yogurt. An average cow will produce 110 kg. Milk in a lactation period of an average of 149 days |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-04-08 : 08:34:33
|
| I would write my own scripts and document them. That way, your intentions are kept with the code.Damian |
 |
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2004-04-08 : 08:52:52
|
| Thanks. That would require "the next guy" to move beyond theclick the Start>Programs>Microsoft SQL Server>Enterprise Manager>....type of instruction set. I guess that is scenerio I'm figuring on. I can't seem to set a scope for how far back into the civilian population I should accomodate.It is a rich creamy color with a high fat content of 5-7 percent. Being so high in fat, it is usually processed into butter, cheese, or yogurt. An average cow will produce 110 kg. Milk in a lactation period of an average of 149 days |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-04-08 : 09:06:06
|
| To be honest, a backup job isn't all that complicated, and the maintenance plans are overkill. Not to mention if (more like when) they fail, whoever is responsible will never have a reason to know how to do backups if all they know is the maintenance plan wizard. Better to let them scratch their heads a little in the beginning while they're learning, as long as they learn to do it without the wizard. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-08 : 12:12:15
|
| Why would the next guy who is responsible for DBA work not know how to do SQL backups or any other DBA related jobs?Tara |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-08 : 12:52:35
|
| You would be doing the company a favor by setting the guy up to get fired if he can't figure out how backups work. It's not brain surgery. The maintenance plans are a nightmare. Document your work. If they can't figure that out, there's no sufficient accommodation for stupid people. Well.....there's always welfare I guess if you're in the USA.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-04-08 : 13:21:10
|
| Indeed. I have trouble convincing management ehre that I am not going to write documents that teach monkeys how to fly. If you have tasks that you want a DBA to execute, hire (or train) a DBA. Don't send a web developer to my desk and tell me to teach him how to restore the backups... |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-04-08 : 13:44:53
|
| So, you wouldn't be happy handing backup duty over to me ?Damian |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-08 : 14:21:38
|
| I don't have problems with developers learning DBA stuff. As a matter of fact, a lot of them know quite a bit already. The problem that I have is when they throw a non-IT person in or some with very little IT experience. Some of our processes are supposed to be documented so that an operator could pick up the document and be able to do everything described in the document. This is for disaster recovery purposes only though. IMO, the backup command is pretty self-explanatory already. BACKUP DATABASE DBNameTO DISK = 'C:\MSSQL\BACKUP\DBName.BAK'If you can't understand what the above command is supposed to do, then you've got no business performing backups or other maintenance type stuff. I just don't think that you need to document what the command is doing. A maintenance plan just adds complexity to the maintenance tasks. When a maintenance plan fails, it is harder to troubleshoot the problem that it is without the maintenance plan. The document would have to describe looking at the SQL Server Error Log or the maintenance plan history. You usually can just look at the job history of failed jobs, but not with maintenance plans as it will only say that sqlmaint.exe failed.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-08 : 14:45:59
|
| I think you should password protect and encrypt the maintenance plan so the DBA can't see it. Then hand it off to a paper pusher who will break it. Then you can quit.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2004-04-08 : 16:22:18
|
quote:
The maintenance plans are a nightmare
yep it adds that sqlmaint runner. as another trobleshooting layer.The old "yeah I'll add a daily reindexing in the plan in full recover mode" even though the implication of huge logs isn't understood.quote:
Some of our processes are supposed to be documented so that an operator could pick up the document and be able to do everything described in the document. This is for disaster recovery purposes only though.
I've been asked to do this kind of stuff too at times. And had some success but it always requires me to go through it a couple times with various "operators" because what is plainly obvious to me is often excluded and therefore causes failure in the sequence. I find that the most difficult kind of document to write. It goes back to scope or Who is your audience. So on the assumption that the operator knows nothing then there could be an arguement to at least let them use the Microsoft GUI way cause that document is already there. But then there is the moral crossroads in that you certainly aren't helping anybody by giving that as an option, 'cause 5 minutes of understanding is worth years of right clicking. And as we here know there are better ways.So the SQL licence in this instance is going to stay Standard version so loggshipping is an addon set of scripts anyway so Maintenance plan would merely be a portal which adds stupid complexity. I presented the maintenance plan "burden" to the IT manager and he was comfortable with no GUI for backups. At the end of the day the files are painlessly getting to tape and offsite so no biggy. Restores remain right click so most bases are covered.quote:
write documents that teach monkeys how to fly
Just get all humble and say"Microsoft does an excellent job of that already, I feel an attempt to do it better than them would add little value to the company..."  Thanks all for the comments, wish I could stay and chat ....BUUUUUT....I'm going on vacation 11 DAYS.North Carolina OBX. Windsurfings Holy Sound.If you see an Old Grey Caprice pulling a trailer with an SQLTEAM sticker on it give a "shakka". Onward..with the quest to have the sky and sea shake hands in my soul. It is a rich creamy color with a high fat content of 5-7 percent. Being so high in fat, it is usually processed into butter, cheese, or yogurt. An average cow will produce 110 kg. Milk in a lactation period of an average of 149 days |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-04-12 : 07:08:50
|
| Sorry for late reply, only just arrived.I've been struggling with this issue too. What if some developer does a quickie Full Backup "just-in-case" before some risky process, everything goes OK, so they delete the backup file, for tidiness, and then a restore is needed a few hours later and the transaction files no longer pair up from the previous full backup. Or, more likely, the backup file hangs about for ever until a disk space crisis and then noone known whether the file can be deleted or not - but they take a guess based on the antiquated file date.Also, what if a new DB is added and they forget to add it to the maintenance plan (lets assume we aren't using "All user databases" because we have some ReadOnly DBs that fail the "fix minor inconsistencies" and stuff like that).And maybe we'd like to have differential backups on weekdays ... can't find a way to do that using RightClick.And then we want to be able to restore the LIVE to the TEST - which requires the files to be mounted on a different physical disk, but same server. Now we've got a nightmare of Logical names and trying to get RightClick to restore files for a pre-existing database, on this server, but to a different physical location.Or the TEST database is on a different server and we've got to sort out reconnecting the User permissions based on a different set of IDENTITIES in master.And a Muppet is going to be able to use the RightClick tools to restore last Sunday full backup, each evening's Differential and the last 17 hours transaction backups? That's a heck of a lot of RightClicking, and probably just after some disaster when time is of the essence.And what about if FULL backups are made weekly, and transaction backups hourly, no doubt both will be set to, say, 14 days retention. So we are storing Transaction backups for 7 days longer than we can restore them (without recourse to Tape)Much as I dislike having to engineer a solution, given that RightClick comes pretty close, I wind up thinking I have little choice. Something that will allow the developers to just type "xx_SP_Safetybackup 'MyDatabaseName'" and it chucks it in the database backup history, allocates it a default retention time and they don't have to worry. Also a similar RESTORE sproc that will work out which files to restore to get them back to a given date/time (or more probably the last, or last but one, transaction backup) and take care of renaming logical names and soring out permission reconnection; knowing what has been stored and where; deleting FULL/DIFFERENTIAL backups when the Child [transaction] backups have all "expired"; alerting when new databases arrive on the server which have not been added to the backup strategy; not trying to do Integrity Repair on readonly databases (nor transaction backups!) and so on and so forth.Glad I've got that off my chest!Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-12 : 12:13:07
|
quote:
Originally posted by Kristen
Or the TEST database is on a different server and we've got to sort out reconnecting the User permissions based on a different set of IDENTITIES in master.
That's what sp_change_users_login is for.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-04-12 : 12:37:44
|
| And it's not IDENTITY...Just a surogate...sp_help sysusersBrett8-) |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-04-12 : 13:30:00
|
quote:
Originally posted by Merkin
So, you wouldn't be happy handing backup duty over to me ?Damian
Depends, would I have to train you? If you're skilled in DBA-type activities, I have no problem with you doing it. But if you are primarily a web developer who writes the occasional SQL call, I am not writing a document for you. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-04-13 : 02:44:11
|
quote:
Originally posted by tduggan
quote:
Originally posted by Kristen
Or the TEST database is on a different server and we've got to sort out reconnecting the User permissions based on a different set of IDENTITIES in master.
That's what sp_change_users_login is for.Tara
Indeedie, but Mr RightClick doesn't help me muchly on that one.Plus I tend to find that we have gained some logons in the DB, which are therefore also in the backup, that are better removed as part of the restore.Here's the code I use FWIW, which I run on the current database after RESTORE:-- NOTE: Use "Results in Text" mode in QASELECT DISTINCT CASE WHEN L.sid IS NOT NULL THEN '-- User ' + COALESCE(U.[name], '[NULL]') + ' already exists on server' ELSE 'EXEC sp_addlogin ''' + U.name + ''', ''password'', ''' + db_name() + '''' + CHAR(9) + '-- Only add if required!!' END, CHAR(13)+CHAR(10)+'-- EXEC ' + db_name() + '.dbo.sp_dropuser @name_in_db = ' + '''' + U.name + ''' -- Remove if access no longer require to this DB', CHAR(13)+CHAR(10)+'EXEC ' + db_name() + '.dbo.sp_change_users_login ''Update_One'', ' + '''' + U.name + ''', ' + '''' + U.name + '''', CHAR(13)+CHAR(10)+'------------------------------'FROM sysusers U LEFT OUTER JOIN (sysmembers M JOIN sysusers G ON G.uid = M.groupuid) ON M.memberuid = U.uid LEFT OUTER JOIN master.dbo.syslogins L ON L.[name] COLLATE SQL_Latin1_General_CP1_CI_AS = U.[name] COLLATE SQL_Latin1_General_CP1_CI_ASWHERE U.islogin = 1 AND U.isaliased = 0 AND U.hasdbaccess = 1 AND ( G.issqlrole = 1 OR G.uid IS NULL ) AND U.name NOT IN ('dbo')Kristen |
|
|
|
|
Backups the gui/book way.