SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Backup all databases in server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jung1975
Aged Yak Warrior

USA
503 Posts

Posted - 03/07/2006 :  14:28:18  Show Profile  Reply with Quote
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

USA
37316 Posts

Posted - 03/07/2006 :  14:38:15  Show Profile  Visit tkizer's Homepage  Reply with Quote
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Check out isp_Backup.

Tara Kizer
aka tduggan
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 03/08/2006 :  01:28:19  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Also refer
http://www.mindsdoor.net/SQLAdmin/BackupAllDatabases.html

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 03/08/2006 :  04:33:26  Show Profile  Reply with Quote
PMFBI but have you got a "shopping list" of features?

I reckon you need to consider:

Database backup type:

FULL, DIFF, LOG

If 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 databases

We 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 externally

Housekeeping:

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" Sprocs

I expect there are other things too which skip my mind for the moment.

Kristen
Go to Top of Page

kpsreenath
Yak Posting Veteran

USA
52 Posts

Posted - 03/08/2006 :  13:29:46  Show Profile  Reply with Quote
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?

Thanks
Sree
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37316 Posts

Posted - 03/08/2006 :  14:06:15  Show Profile  Visit tkizer's Homepage  Reply with Quote
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?

Thanks
Sree



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 Kizer
aka tduggan
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 03/08/2006 :  17:28:45  Show Profile  Reply with Quote
"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 mentioned

A 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 factor

Error messages are very hard to attribute to their cause

Hard to add on post-processing of the Backup Files - such as ZIP and COPY to another server

And 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

Edited by - Kristen on 10/09/2007 04:30:46
Go to Top of Page

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 03/13/2007 :  08:20:54  Show Profile  Reply with Quote
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

Edited by - drewsalem on 03/13/2007 08:30:30
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 03/13/2007 :  09:32:15  Show Profile  Reply with Quote
"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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000