Trying to determine if there's anything I should include from the former DBA's backups

I don't recommend mixing full and log backups in a maintenance plan. Besides, which of the system databases needs log backups? master is in simple recovery, tempDB can't be backed up, msdb defaults to simple recovery and model shouldn't change.

Do you need to back up the system databases daily? Do you add/change logins, linked server, jobs, etc that often?

That looks fine.

A full database consistency check every hour? Good luck with that.... What's going to happen when the checkDB takes 6 or more hours and hammers the database for the entire 6 hours and you have it scheduled hourly?

How much data can you lose in the case of a disaster? Say the drive with the DB files on implodes in the middle of the business day. How much data can the business tolerate losing?

First, what recovery models are the databases using? You will find that the master database is using the simple recovery model (and that can't be changed), so transaction log backups on master will fail.

Second, I'm not sure about the hourly database integrity check. Nightly, I am okay with depending on the size of the database.

Third, and this is just my opinion, if I were to use maintenance plans, I would have separate plans for each user database. This would give me more flexibility over scheduling the backups.

Fourth, it may be over kill but I would run the t-log backups hourly and make sure that it didn;t overlap with the nightly full backup. You may have processes running at night that you want to continue with t-log backups.

Fifth, I would not shrink the databases or transaction logs.

Good question, Lynn. I was't sure how the previous student DBA had set the recovery model for the system databases, so I just checked. He set it up so that master, model and msdb are all full recovery model, and tempdb is simple. And as you predicted the SQL jobs are failing on those, so no matter what I'd better change how he has things set up for my new backup scheme, re: system databases.

OK, I'll remove the database integrity check on hourly jobs.

Right now our databases are small enough that there's really no overlap. Although I will watch that to see how it goes.

I've eliminated all database shrinking in my scheme.

Gail, I didn't realize that it may not be necessary to backup the transaction logs of system databases. WOW, interesting. OK, I'll remove that.

As you suggest, our database schema doesn't change that frequent, so I could turn back the system database backup files to once or twice a week.

You got me again with the full database consistency check on an hourly basis. I'll remove that as well.

Now, as to how frequently we perform the intermediate backups (I have an hour at the moment) I'm kind of concerned with how much space we'll have on our backup media. I've estimately what 2 weeks of backups would take by seeing how has changed on the test database server. One of our major applications caches data locally for up to an hour and a half before commiting it to SQL Server, but other applications run more frequently and commit data to SQL immediately. I could try shorting it to every 30 minutes. This really is a "it depends" type of scenario. I'll have to play with it to see what works best in our environment.

Nothing to do with database schema, that's in the user databases. master stored logins, server-level permissions, linked servers and things like that. MSDB stores jobs, job history, backup history, SSIS packages.

Do not design your backup strategy based on how much space you have available. Design your backup strategy based on how long you will have to restore the database in the case of a disaster and how much data the business can accept losing in the case of a disaster.

If you design your backup strategy based on space usage you may well end up with a backup strategy that does not support your business' requirements. If the business requires no more than 30 minutes data loss and you have a backup strategy that can result in up to 2 hours data lost, when a disaster happens and those 2 hours of data are lost, the next thing lost might well be your job. It has happened.

Gail,

For me, this has been a great day of learning. I didn't know that MSDB stored jobs, job history, etc. Cool, thank you!

Concerning what our business needs are, concerning data recovery, well right now it's 24 hours. For the last 16 years the best our users have ever had is the ability to only be able to recover whatever they did yesterday. Everything done today is lost, if there's a server hickup, etc. I'm trying to give them what they've never had before, and that's at least the ability loose at most 1 hour. I've beeen here a long time, and I've seen users sometimes (honestly, not often) loose a whole days worth of work, and its because someone else, way back in the day, set up backups this way. Then the student DBA who came in a few years ago, just copied that same pattern when he set things up the way they are now. I'm just trying to make it possible for us to never again loose a whole day's worth of data. I've not had the opportunity before, and have only gained enough knowledge to make the attempt more recently. Also, with other responsibilities I can only work on this when I have time (DBA activities isn't my primarily job responsibilities, but it is one of them since we don't have anyone else here to do DBA stuff).

I've got a follow-up question to ask. Lynn said that the master db can't have a recovery model other than simple. In our case, somebody in the past set the recovery model to full, but it is failing backing up the transaction logs. Should I just do a backup of the system dbs (except for tempdb) and leave the transaction logs out of it?

Here is a link that covers what I was talking about regarding the master database: http://msdn.microsoft.com/en-us/library/ms191488.aspx.

master's recovery model is always simple, regardless of what the actual database setting value is set to. It cannot ever have log backups taken or restored

Re log backups for the system databases, which of the system databases do you need point-in-time restore for and why? Only if you have a sensible answer to that should you do log backups for any of them.

Thank you, Lynn, I'm reading it now.

Thank you, Gail.

Gail and Lynn, thank you both very much for your help. Using your advise I've come up with a couple of simpler maintenance plans (no shrinking of any databases), and have put them into place. I'll be watching them to see how them do, making sure they do what they should be doing. At least as well as I know how.