| Author |
Topic |
|
homeri14
Starting Member
5 Posts |
 Posted - 2006-12-27 : 16:42:22
|
| Hello All, I have been looking for a solution that would allow all databases to be backed up as a scheduled job. Yes I know that I can create a job or maintenance plan to handle this but I need something a little different than the standards. Here is the scenario. You have several SQL servers for multiple purposes. On these servers you may have databases created for conversions, testing, production, temporary access, etc. As these are created and then one day dropped you have to either create/delete the backup job or modify the related maintenance plan. I have found several stored procedures that will backup all and delete based on retention but I still need some guidance to add more to this. If I were to start with a wish list, it would be to first backup the database(s) then delete the old files. If the backup or delete failed for whatever reasons it would continue to the next db. On completion of the process it would either write to a history file or email someone of the failures if any occurred. We have over 300 clients that have from 1 to 3 SQL Servers for our products and we have 6 SQL Servers for our corporate needs. It would be great to have a solution that no matter what the condition we know that backups are being preformed and we don’t have to change the job or maintenance plan every time something new is introduced. Honestly am I looking for something that can't be done? My language (T-SQL) skills are limited but I have no problem trying to learn I just need a few good places to start. I appreciate all positive replies.thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-12-27 : 17:02:12
|
| Check out my alternatives to the maintenance plans:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspxI will be posting an update to isp_Backup in a few weeks. The new version supports both 2000 and 2005. It does a lot more error checking and other things as well. But the current version on my blog should get you going.Tara Kizer |
 |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-12-27 : 17:10:29
|
| You can do this with a little trick.Create a maintenance plan named ALLDATABASES using the wizard that operates on all databases but which does absolutely nothing.Create another maintenance plan named USERDATABASES using the wizard that operates on all non-system databases but which does absolutely nothing.You can then write your own call to xp_sqlmaint and reference the Maintenance plan by name as a means of grouping all the databases together for processing. Here is an example of the call that I use:EXECUTE master.dbo.xp_sqlmaint N'-PlanName AllDatabases -Rpt F:\SQLBackups\BackupDatabases.txt -DelTxtRpt 5Days -WriteHistory -BkUpDB F:\SQLBackups -BkUpMedia DISK -DelBkUps 5Days -CrBkSubDir -BkExt BAK'This is scheduled in a job to run every evening.I have a slightly different call for hourly log backups.Now, no matter what databases are created or dropped, they are rolled into the backup strategy.One thing though...all of your database must be in full recovery mode if you are going to perform log backups on them. This includes any sample databases such as Northwind, and you should set your Model database to full recovery so that all new databases will have that status by default.STAR SCHEMAS ARE NOT DATA WAREHOUSES! |
|
|
|
Kristen
Test
22859 Posts |
|
|
homeri14
Starting Member
5 Posts |
Posted - 2006-12-28 : 09:20:42
|
| Thanks Tara, Blindman and Kristen.Tara I was trying to modify your sp to first do the backup then delete. Likewise I was going to try to change it some more to allow it to backup, delete, log, backup, delete, log,etc so that if a backup fails I did delete the last one and if a failure is logged I receive an email. (Using SQL 2005 with dbmail). Any thoughts? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-12-28 : 14:31:13
|
| I don't understand what you are trying to modify. My stored procedure already deletes old files, you just need to pass the number of days to the retention variable. BTW, I wouldn't recommend only saving 1 file on disk. Keep a minimum of 2 days, preferrably more like 5-7 days. Make sure that the files get swept to tape. For the e-mail portion, you would just setup the stored procedure as a job. Have the job notify via e-mail on failure.Tara Kizer |
|
|
|
homeri14
Starting Member
5 Posts |
Posted - 2006-12-28 : 14:39:07
|
| Sorry, let me clear this up. Your procedure deletes the file(s) before the backup. I would like to make a modification that it deletes after the backup. This way if the backup portion was to fail for db1 it will not delete the retained backups for db1. Lets say the retention was set for 5 days and you are on vacation and the other dba is out sick. if the backup portion continues to fail then in 5 days all backups are gone. And yes I agree that these should be on tape or copied to another location but... this is just a "what if" kind of thing.My "wish list" was to make changes that if you are backing up db1, db2, db3 as a daily full with 5 days retention and for some reason the backup of db2 failed, the job would continue to do db1 and db3 and not delete the backups of db2 while failing. The failures are emailed as a notification that a portion of the job failed. The one thing that I don't know is if midstream through the database list if one fails will the whole job fail? Would that be a fatal error or non-fatal? Sorry to keep going on and on. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-12-28 : 15:21:22
|
| Seriously, you want a maintenance strategy that accounts for backups failing five straight days without anybody addressing the issue?STAR SCHEMAS ARE NOT DATA WAREHOUSES! |
|
|
|
homeri14
Starting Member
5 Posts |
Posted - 2006-12-28 : 16:26:33
|
| Blindman,The scope that I was also looking at is a potential last ditch fail safe for our clients. We have over 300 (government only) clients that have everything from a fully staffed IT department that can and will handle every need to those where there is this one guy that does the job of 7 other guys and has little time to invest... For me, I do not seriously want a maintenance strategy that goes un-noticed. Or at least I hope that I will never have one. These were just a few thoughts of a plan that might fit in the what if category. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-29 : 06:34:00
|
| Our Backup Sproc deletes after the Backup is successful. If the backup fails then the Sproc fails and it never gets to the point of making the delete.At least that what I built it to do ... there may be circumstances that would allow it to carry on after a failed backup ... but then we have other failsafes in place that will notice that there is no backup file for N hours, or that it has suddenly become smaller (or larger, etc.) and raise a separate alert.And another process that will complain if the Archive attribute on the file has not been set within 24 hours ...EDIT: Actually I think the Archive File Attribute has to CLEAR when it is basked up to tape, NOT become set)Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-12-29 : 12:53:03
|
| We delete then backup in case there isn't enough disk space for both files. Occasionally, we have to modify the retention period due to running out of disk space. It can take our disk guy a few days to add more SAN space, so we need the backup stored procedure to help us out until the space has been added. We'd prefer getting a current backup than not being able to take one at all.Tara Kizer |
|
|
|
TonyTheDBA
Posting Yak Master
121 Posts |
Posted - 2007-01-02 : 05:54:41
|
quote:
Originally posted by tkizer
We delete then backup in case there isn't enough disk space for both files. Occasionally, we have to modify the retention period due to running out of disk space. It can take our disk guy a few days to add more SAN space, so we need the backup stored procedure to help us out until the space has been added. We'd prefer getting a current backup than not being able to take one at all.
Seconded, I've just got back to discover that our backups have failed on one server due to running out of disk space. Guess I'm lucky in that the departments were out for the entire holiday period and no work was done on them. I'm implementing Tara's stored procedures on these as is as I would much rather have a backup than not . . . Of course I would also refer to the disk space alert email. (Or even change the stored procedure to check for Disk Space then IF there is enough space, do the backup and delete the old file, Otherwise Delete the Old Files Do the Backup And Send an email to Alert Ops that Space is an Issue) As Tara Said She is posting an Update in a few weeks which I will wait to see, although I will continue to develop what is an excellent sp to start with so that it suits each of our servers (Which don't necessarily have the same requirements)-- RegardsTony The DBA |
|
|
|
|
recursive all inclusive backup sp