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 |
|
benwilson
Starting Member
6 Posts |
 Posted - 2005-01-17 : 17:32:11
|
| Hi All,I am working on a backup plan for the main DB my compay uses. It is an OLTP DB, that is available over the web 24 hours a day (with little or no use between 12 and 5am. The DB is just over 2gb atm, and will continue to grow reasonably quickly over coming months.The plan i am considering is as follows:Sunday 2am: Full DB backupAll other nights @2am: Differential backupTransaction log backups: every 30 minutesDoes this sound reasonable?I have a few questions about where to keep the backups, and how long to keep them for too...Should the tlog backups all be appended to the one file? A file for each day?Is there any reason to keep the trans log backups once the differential backup is done? (How long do other people keep them for?)Should the differential backups be appended to one file, or a seperate file each?Im sure i will have more questions, but thats it for now! Thanks in advance,Ben |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-17 : 18:34:16
|
| "Does this sound reasonable?"Yup"Should the tlog backups all be appended to the one file?"IMHO absolutely not - separate files is better"Is there any reason to keep the trans log backups once the differential backup is done?"You should keep the transaction logs for as long as you might need to recover to a particular point in time. It would seem reasonable that after a month or so to only be able to recover to 2AM on a given day may be sufficient. After a year being able to recover to the nearest week might be sufficient. There again, to dsicvover HOW a fraud was committed you might need to be able to see what happened transaction-by-transaction, and it might be ages until the fraud was discovered.Also, consider that if a Differential backup was damaged you could recover from an earlier backup plus ALL the subsequent transaction logs."Is there any reason to keep the trans log backups once the differential backup is done?"Again, IMHO, I would never use a single appended file, always separate files.Consider copying the backups to another machine (geographically separated if at all possible) immediately after they are created. If the main SQL box fails at 01:59am it would be good if the backups for the previous 24hours had already been copied to another machine, rather than waiting for the once-per-day tape backup!Kristen |
 |
|
|
benwilson
Starting Member
6 Posts |
Posted - 2005-01-17 : 19:38:50
|
| Thanks Kristen,We dont have a tape drive, so backups would be made onto a hard drive...If the backups are copied to another machine, or copied to a DVD weekly, there is no reason to keep the original backup on the drive once it has been copied is there? (to free up space on the drive) |
|
|
|
benwilson
Starting Member
6 Posts |
Posted - 2005-01-17 : 19:41:23
|
| one other question...with the transaction log backups, would these be done by creating a job to run a bacup script every 30 minutes that creates a filename based on date/time?Thanks again! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-01-17 : 20:22:55
|
For real database security, you should plan on a once a day full backup, and transaction log backups about every 10 or 15 minutes. Keep the tansaction log backups for at least 3 days, and keep the full backups until they are backed up to tape. Don't forget to backup the system databases also.If you can, backup your database to a different disk than the one that contains the database files. As an alternative, if your network is fast enough, backup to a different server, or replicate the backup directory to another server.For any important database ( and which aren't important? ), you should backup the full backup and transaction log backups to tape on a daily basis, with off-site rotation. Keep the tapes around for awhile. I would suggest 8 weeks for daily, and 2 years for weekly tapes.The best way to setup the backups and transaction log backups is to use a database maintenance plan. It does most of the work for you, and is easy and fast to setup. It takes care of timestamping the filenames, and deleting old backup files.This may seem excessive if you are just starting out as a DBA, but it really isn't. Trust me on this, and just remember this rule: No DBA ever got fired for having too many backups.quote:
Originally posted by benwilson
Hi All,I am working on a backup plan for the main DB my compay uses. It is an OLTP DB, that is available over the web 24 hours a day (with little or no use between 12 and 5am. The DB is just over 2gb atm, and will continue to grow reasonably quickly over coming months.The plan i am considering is as follows:Sunday 2am: Full DB backupAll other nights @2am: Differential backupTransaction log backups: every 30 minutesDoes this sound reasonable?I have a few questions about where to keep the backups, and how long to keep them for too...Should the tlog backups all be appended to the one file? A file for each day?Is there any reason to keep the trans log backups once the differential backup is done? (How long do other people keep them for?)Should the differential backups be appended to one file, or a seperate file each?Im sure i will have more questions, but thats it for now! Thanks in advance,Ben
Codo Ergo Sum |
|
|
|
benwilson
Starting Member
6 Posts |
Posted - 2005-01-17 : 21:13:51
|
| Thanks Michael,Thats a big help...that kind of info seems to be really hard to find! If i was to have transaction log BUs every 15 minutes, what happens if the full or differential backup is still runnin when the tlog backup starts? does this cause any problems, or is it ok? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-01-17 : 21:30:12
|
No. The log and database backups are separate operations. Both can run at the same time, they'll just slow each other down somewhat (probably not noticeably) Full and differential backups DO contain some of the transaction log, but only those transactions that took place between the start and end of the backup operation. Even with this fact a regular log backup is still needed.I would disagree with Michael on using maintenance plans though. You cannot do differential backups with them, and they sometimes don't work properly. Everything else he said is 100% dead on. If you want to have the same functionality and more you can read Tara's blog:http://weblogs.sqlteam.com/tarad/She has a number of entries on various backup scripts she's written.If you absolutely HAVE TO use a maintenance plan, you can, but beware that once you start using them you lose any motivation to learn and to do backups another way.  |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-18 : 01:57:01
|
| "If the backups are copied to another machine, or copied to a DVD weekly, there is no reason to keep the original backup on the drive once it has been copied is there?"We keep our backups online for "a while". The exact length of time depends on avaliable disk space and how long it takes to get the tape/DVD - for example, for the databases hosted at ISP's we reckon it could take them several hours to find the tape and restore it to disk!"if your network is fast enough, backup to a different server"I'm not keen on this - it raises issues when the network share changes, or if the network is down (e.g. backups fail but no alerts are received!). My preference is to COPY to the network share after the backup has been made locally.We've stopped using the maintenance plans. They caused too many problems, and never gave enough diagnostic data when they went wrong.Transaction log backups not being deleted - manual file deletion required.When a database was set to readonly/offline/single-user - other databases not backed upSet index rebuild to allow 10% "free space" - caused tables to be altered such that clustered index on IDENTITY columns performed VERY badlyAllow minor corruption fixes - causes an attempt to put database in SingleUser mode before even attempting the database consistency check. If database in use setting single user fails, then NO checks are run - and single user IS set then all user are locked out!The list goes on ...Make sure you have something in place to purge MSDB - otherwise the backup history is never cleared (maintenance plan doesn't do that either - another nail in that coffin)And you'll need stuff to ReIndex / DeFrag indexes, and rebuild statistics, to keep the who running smoothly.Kristen |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-01-18 : 23:49:53
|
I backup about 200 databases every day using maintenance plans, and I am not having any problem with it. I create a separate maintenance plan for each database to be backed up, and another plan for each transaction log backup.I use maintenance plans for re-indexing most databases. I setup a separate plan for that also. I always set the maintenance plan re-indexing to keep the original amount of free space; I assume that who ever setup the table had a reason for the way they setup the free space percentage. In a small number of cases, I use a stored procedure to do DBCC REINDEX or DBCC INDEXDEFRAG, instead of using a maintenance plan.I don’t use maintenance plans to check the databases; I use a stored procedure to run DBCC CHECKDB against every DB every week.I setup each maintenance plan to produce a text log file that is deleted after a couple of weeks for backup plans and optimization plans, and four days for transaction log backups. This makes it each to find and look at the log file if there is a problem on a job.I run a job on each server that purges the MSDB every day back to a rolling 90 days.I am not saying that a maintenance plan is perfect, or that it requires no thought, but it is a simple, proven way it quickly setup jobs to do database backups. Because it is simple to setup, it makes it easy to remember to do when I create a new database. With so many databases to track, I want to make sure that I don’t forget to setup a backup.quote:
Originally posted by Kristen
"If the backups are copied to another machine, or copied to a DVD weekly, there is no reason to keep the original backup on the drive once it has been copied is there?"We keep our backups online for "a while". The exact length of time depends on avaliable disk space and how long it takes to get the tape/DVD - for example, for the databases hosted at ISP's we reckon it could take them several hours to find the tape and restore it to disk!"if your network is fast enough, backup to a different server"I'm not keen on this - it raises issues when the network share changes, or if the network is down (e.g. backups fail but no alerts are received!). My preference is to COPY to the network share after the backup has been made locally.We've stopped using the maintenance plans. They caused too many problems, and never gave enough diagnostic data when they went wrong.Transaction log backups not being deleted - manual file deletion required.When a database was set to readonly/offline/single-user - other databases not backed upSet index rebuild to allow 10% "free space" - caused tables to be altered such that clustered index on IDENTITY columns performed VERY badlyAllow minor corruption fixes - causes an attempt to put database in SingleUser mode before even attempting the database consistency check. If database in use setting single user fails, then NO checks are run - and single user IS set then all user are locked out!The list goes on ...Make sure you have something in place to purge MSDB - otherwise the backup history is never cleared (maintenance plan doesn't do that either - another nail in that coffin)And you'll need stuff to ReIndex / DeFrag indexes, and rebuild statistics, to keep the who running smoothly.Kristen
Codo Ergo Sum |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-01-18 : 23:53:48
|
There is no problem with the transaction log backup running at the same time as the full backup.quote:
Originally posted by benwilson
Thanks Michael,Thats a big help...that kind of info seems to be really hard to find! If i was to have transaction log BUs every 15 minutes, what happens if the full or differential backup is still runnin when the tlog backup starts? does this cause any problems, or is it ok?
Codo Ergo Sum |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-20 : 01:07:11
|
| "I want to make sure that I don’t forget to setup a backup"That's precisely why I have rolled my own!I have a table of DatabaseName + "backup strategy"Backup Strategy is either "None" or Full[daily]+TLogs[hourly] or some other variationWhen the backup SProc runs it adds any databases which are NOT in the list (using a default backup strategy) - so I never miss backing up a brand new database, erring on the side of over-backup.You strategy of one maintenance plan per database will work well - failure of one plan will not prevent backup of other databases - but I reckon the Microsoft-provided wizard should be capable of backing up all databases (i.e. a single plan) without fear of it failing because one database didn't backup, one one database was set to SIMPLE and the backup plan was set to do transaction log backups etc."I always set the maintenance plan re-indexing to keep the original amount of free space"Absolutely agree. However, unfirtunately, that is NOT the default setting when you make a new plan. I see this as being another hole that the unwitting, naive user can fall into :-(Kristen |
|
|
|
|
|
|
|
|
Backup Plan: Comments/Suggestions please!