January 8, 2006 at 12:54 pm
I'm trying to get my head around SQL Server from an server admin point of view.
Out SQL server has various databases and plans setup by a couple of our guys who went on a MOC training course. We started off using SQL for one small thing and now we're using it more and more so I'm having a nosey at the server and trying to get my head around certain things as I think I need to be involved.
My first thoughts are "backup backup and backup" so right now I'm looking at the "Database Maintenance Plans" section.
We have a maintenance plan in place that does the following for "All User Databases":
An integrity check every day at midnight.
A complete database backup and verify every day at 1am, set to remove files older than 2 weeks.
A complete transaction log backup and verify every day, every hour between 6am and 8pm, set to remove files older than 1 week.
I'm assuming the only reason for keeping such an amount of logs is so that instead of being able to go back say 5 days to the full backup at 1am you can go back to 4 days and 17 hours to the exact hour that you think you need?
There is a maintenance plan set to do a Database Backup of "master, model, msdb" but then there's also a plan to backup "All System Databases" which AIUI are master, model and msdb?!
There isn't a plan that I can see to backup the transaction logs for the system databases (either by name, or by the "All System Databases" reference).
There are also maintenance plans set to backup various specific databases every night, but again, AIUI these should be covered by the "All User Databases" backup - as it is we appear to be backing all user databases up at 1am, and then one of those databases again at 2am, and a different one at 2.15am.
I'm going to order a book first thing tomorrow, but I wondered if anyone had any thoughts from the above.
I don't want to appear to be second guessing these guys, and I've not read much yet so by all means flame away and tell me I'm being stupid, but something doesn't look quite right - basically it looks like various things are being duplicated and done several times for no obvious reason.
Oh and any tips on a book?
cheers,
Paul
January 8, 2006 at 4:31 pm
A database can be set to one of three recovery models: Full, Bulk-Logged and Simple. The system databases are set to Simple. When a database recovery model is set to Simple, the transaction log is truncated periodically, so you don't have to, in fact can't, do transaction log backups. So, you don't need to do transaction log backups of the system databases.
Looks like they have two maintenance plans covering the system databases, you could delete one of them.
The "All User Databases" should cover the user databases, so the maintenance plans to cover individual databases could possibly be removed if they do the same things.
The transaction log backups allow you to recover to a point in time as you guessed.
Depending on how much drive space you have and your company's requirements, saving daily backups for two weeks sounds excessive. Are your backup files picked up by tape? In my company we usually have two days worth on disk where we can get to it quickly, the rest is on tape for a month. In 3.5 years as a DBA, I've only had to get a backup from tape a handful of times.
Besides thinking "backup backup backup" you should also think about documenting the process and testing restores.
There are lots of great books. Kalen Delaney's "Inside SQL Server 2000" covers just about everything. I have an article on doing point in time restores that might be worth a look and there are lots of great articles on this site.
Hope this gets you started,
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
January 10, 2006 at 9:43 am
Kathi is correct about the transaction logs. You may also want to look at the scheduled jobs. From Enterprise Mgr..Server..Management..SQL Server Agent..Jobs.
Most of the installations I've seen make use of the maintenance plans, but as strange as it sounds, I've seen backup plans that include seperate jobs for backing up the transaction logs.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply