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 |
|
jung1975
Aged Yak Warrior
503 Posts |
 Posted - 2006-03-07 : 14:28:18
|
| I am trying to create a script that will back up all of the Databases in one server. Can you give me some examples? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-03-07 : 14:38:15
|
| http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspxCheck out isp_Backup.Tara Kizeraka tduggan |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-08 : 04:33:26
|
PMFBI  but have you got a "shopping list" of features?I reckon you need to consider:Database backup type:FULL, DIFF, LOGIf you have a database set to SIMPLE Recovery Model a LOG backup will fail, so your process needs to be smart enough to spot that. Also databases set to READONLY, SINGLE-USER and OFFLINE etc. need special handling (which Maint. Wizard can't handle ).For disk space reasons you may want to consider, say, Weekly FULL and Daily DIFF (plus whatever TLog backup frequency you need).Delete stale files:Either delete all files after a couple of days (IIRC that's how Tara's script works) or keep a database table of the backups you make, with retention periods, and delete when they expire (which is what I do as we have different retention periods for what we term Daily & Weekly backups; we also manually extend the retention sometimes - e.g. for a backup just before an upgrade)Add new databasesWe automatically "add" new database to the "backup list", with default settings, unless the database name ends in "_NOBACK" (which we use for the occasional emergency restore to a "temp" database, no sense adding a a sudden 2GB database temporary restore to the backup schedule!)Statistics:You may want to store a History of when backups were made. And file sizes (to review growth over time).Restore:If you make TLog backups every, say, 10 minutes then that's 6x24 per day. If you have to restore that's a lot of scripting!! so you may want to consider being able to script a restore from your "history" table.Backup-Backup Once you have made your backup file on disk what next? Perhaps your tape will pick it up tonight, but you may want to consider allowing an other job to run after each backup.You may want to COPY the backup file to a network share for added redundancy. Or to a standby server (e.g. log shipping type scenarios etc.)We have an option to ZIP and/or PGP the backup files (including move to different folder), which we use for databases [e.g. containing credit card numbers] on servers which are hosted externallyHousekeeping:You may want to consider index Rebuild/Defrag, and DBCC CHECKDB type stuff as part of the jobs. Also UPDATE STATISTICS & sp_recompile of "slow running" SprocsI expect there are other things too which skip my mind for the moment.Kristen |
|
|
|
kpsreenath
Yak Posting Veteran
52 Posts |
Posted - 2006-03-08 : 13:29:46
|
| I dont understand the need of the scripts, all these things can be acheived using the maintenance plans or is there any issues which you forsee in the maintenance plans?ThanksSree |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-03-08 : 14:06:15
|
quote:
Originally posted by kpsreenath
I dont understand the need of the scripts, all these things can be acheived using the maintenance plans or is there any issues which you forsee in the maintenance plans?ThanksSree
I hate using maintenance plans. And maintenance plans can't do everything that I need, which is another reason why I rolled my own.Tara Kizeraka tduggan |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-08 : 17:28:45
|
| "any issues which you forsee in the maintenance plans"Some, and probably more that I don't know of ...Unreliable behaviour when some databases are Readonly/Offline - as I mentionedA plan that encompasses any-newly-added-database can't have differing requirements for each database.Mixture of SIMPLE and FULL Recovery models requires multiple plans (and then you cannot have a plan that automatically encompasses all new databases that are added).Default settings muck up your index fill factorError messages are very hard to attribute to their causeHard to add on post-processing of the Backup Files - such as ZIP and COPY to another serverAnd so on ...Edit: Maintenance Plan fails to delete TLog backups past retention period. Work around for this is to delete the Plan and recreate it from scratch.Kristen |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-03-13 : 08:20:54
|
Hi Kristen / Tara.I REALLY hate to bring this up again, so apologies in advance. But I would really like to be convinced.Absolutely everyone says stay away from the wizard for one reason or another. Now on SQL Server 2005 I don't have a problem with that, but I do like creating a plan (without the wizard, but still using SSIS) for the sake of bringing it all together.Now, my question is this, Microsoft, in all it's glory has clearly tested SQL Server over and over. In the instance of there being a bug in it's Maint plans' application, it releases a fix in the next sp (as seen in sp2). Tara's scripts are superb. I envy you. I can't write something like that (yet!), but I (and I'm sure many dbas that use pre-written scripts not written by themselves) cannot guarentee how well it works because it's not being tested by thousands of people. These scripts may work in the majority of cases (as does the wizard), but if it were tested by thousands of dba's would there not equally be situations and instances were the script causes some kind of anomoly? So why would one use their own plans, when SQL Server provides already made ones that does the majority of what a self-written one does? For example, if all you need to do is backup (and nothing else fancy), why write a backup script when it's there. Indeed Tara had to put in the extra effort to have the script retain baks for so many days. I'm asking because I don't want to be looked upon as a poor dba. I would like to say in my next job interview that I do (or do not) use the wizard, and have a genuine reason for it. I'm also asking because I value your opinion. At the moment, it's a bit peer-pressurish  , but at the same time I don't want to be a victim of blind faith! Convince meeeeeee! I would like to go away from this thread and never have to think about it again.Thanks,Drew |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-13 : 09:32:15
|
| "Now on SQL Server 2005 I don't have a problem with that"I tried it for the first time last night. I've just set up a new server, and I was keen to have at least SOME backups until I can set up something pucker.I spent 3 fruitless hours and only got to cryptic error messages saying "Syntax error in Line 1 near ')'" - that's in the SQL that it generated .... I have no clue what is wrong, and not sure I want to spend any more time working on it. I could have migrated my own "backup the world" solution in the time not fixing anything took ..."I cannot guarentee how well it works because it's not being tested by thousands of people"Yes, I too have that worry. But there again I've used the maintenance plans and for whatever reason they have let me down. One of them was that they started NOT deleting the TLog backups, the first I knew about it was when the server ran out of space and the site was down .... we then had several months of deleting the TLog backups manually until we could schedule enough time to build a replacement."So why would one use their own plans, when SQL Server provides already made ones that does the majority of what a self-written one does?"The ones in SQL 2005 look better. The default is no longer to change all your Indexes from 100% fill to 90% fill ... and they can do Differential backups and stuff. And it looks like they do actually purge the MSDB now ...... but whenever they break you have zero chance of debugging why, IME."all you need to do is backup (and nothing else fancy), why write a backup script when it's there"Yeah, but that ain't all you need. You need Index and Statistics rebuild. In my case I couldn't contemplate a Reindex, we need to use Defrag (not sure if that's in the maintenance plans, it probably is, I haven't looked yet. I need FULL SCAN on the Statistics rebuild for some tables too.Horses for courses, I reckon, but build-your-own takes plenty of time, but gives you the ultimate in configurability.I think you also need to consider the "out of sight, out of mind" issue with the GUI tools. All too easy to THINK you have a water-tight solution, and you haven't. If you build your own you will at least think through what you want, why you want it, and how it should all hang together. You may end up with the same thing, basically, at the end of the day, but I reckon at he very least it will be a better through-through solution.For example, what happens if your FULL backup takes more than an hour, and you have TLog backups every 10 minutes - do they get blocked? Would you even bother to think about that when you clicked merrily on the "All in one job" option in the Wizard?Kristen |
|
|
|
|
|
|
|
|
Backup all databases in server