January 20, 2011 at 3:42 pm
using SQL server 2000 (sp3)
I set up a maintenance plan for a Database in which I do a full backup each Saturday morning along with a Transaction Log Backup that is done daily. I keep 4 weeks work of backups which, if I understand correctly should give me a rolling most recent 4 weeks of data with potential loss of no more than a day.
Please correct me if I'm wrong.
If the above is correct then:
1) Is that all I need to safeguard the database given that I only need to restore, if necessary, to the previous day or am I missing something?
2) Within the maintenance plan I have the transaction log backup scheduled for the same time as the Saturday full database backup. Is this safe or should I spread the time out.
3) After the Transaction log backup do I need need to run any procedures (Shrink database, truncate log, etc..) or is this done by the maintenance plan?
January 20, 2011 at 3:51 pm
Transaction log backups should be scheduled to run much more often than once per day.
We usually run them every 15 minutes, 24x7. If the database has very high activity, we may run them more often, like every 5 minutes.
January 20, 2011 at 3:58 pm
I understand that transaction logs are a quick save - that's not my concern however. I'm told once a day is more than adequate for our needs.
Is what I described above accurate?
January 20, 2011 at 5:55 pm
1) Is that all I need to safeguard the database given that I only need to restore, if necessary, to the previous day or am I missing something?
- All you need is the previous day's full
2) Within the maintenance plan I have the transaction log backup scheduled for the same time as the Saturday full database backup. Is this safe or should I spread the time out.
- doesn't matter. After SQL 2005, the log backup isn't blocked by the full or vice versa
3) After the Transaction log backup do I need need to run any procedures (Shrink database, truncate log, etc..) or is this done by the maintenance plan?
- No, you do not shrink, and the log backup will mark the backed up portion of the log as inactive and it can be resused. Do not truncate.
January 20, 2011 at 6:29 pm
Steve Jones - SSC Editor (1/20/2011)
1) Is that all I need to safeguard the database given that I only need to restore, if necessary, to the previous day or am I missing something?- All you need is the previous day's full
Thanks for the reply Steve, but just to clarify, The Full backup of the database is done once per week on Saturday. As it takes 8 hours we can't do it nightly as it interferes with other time consuming procedures that are needed to prepare for each business day.
The maintenance plan allows me to backup the database on one tab and the transaction in another and set up individual scheduling so...
The schedule for the Database is once a week: Saturday (12:01 AM)
The associated Transaction log is scheduled Monday (12:01 AM) thru Saturday (12:01 AM).
Do you see any issue with this? And apologies, I see I posted this in the wrong forum - we're using (*cough*) SQLServer 2000
January 20, 2011 at 7:11 pm
Your full backup takes 8hrs and you only backup the transaction logs once a day? How many transactions a day are you running?
Do you need to be able to recover to a point in time during the previous day? If not, have you considered a differential backup?
How long do the transaction log backups take?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 20, 2011 at 7:14 pm
You could backup transaction logs more frequently, even though 1 day's loss is acceptable to you. You may encounter a problem one day and be glad for the extra recovery ability.
January 20, 2011 at 9:24 pm
You really need to read up on how backups work, but here's the basic theory. Lets' assume you back up on Sat for your full. You then have
- Sat Full
- Sun Tran log 1
- Mon tran log 2
- Tues tran log 3
- wed tran log 4
...
If your database crashes on Wed, you will need to restore:
- Sat full
- Mon tran log 2
- Tues tran log 3
and potentially the Web log, depending on the time of the crash. You would also potentially want the tail lo backup from the crash.
However. If there is any issue with the sun log backup (or any other), then you cannot restore past that. So if the file is corrupt, lost, or something else from Sun, you would be restoring back to Sat on a Wed crash. You need an unbroken chain of log backups.
So, your strategy isn't great.
Here's what I would do. First, I would consider doing a differential backup each night, and potentially getting a full backup halfway through the week. That would help recovery. You typically would want to have a couple of full backups and logs in case there is an issue with a file somewhere. If you had the Wed crash, then you would be looking at
- sat full
- Tue diff
- Wed logs since the Tue diff.
Next, I would add in a couple of log backups during each day. I know that people think they can stand a day's loss, but that's when it hasn't happened. Whether you have 1 or 24 log backups a day, the overall size is roughly the same. And by spreading the load, you also somewhat can spread out the resources needed on the system.
Lastly, I would start to investigate why you need 8 hours to take a backup. Likely you have some issues here. How large is the db? What do you back up to?
January 21, 2011 at 3:37 am
Thanks again for all the replies.
I know it's not the best situation, but I don't have control over the database, I have to work within the limits that I'm told.
The reason for the 8 hour backup is that there is a lot of historical data being backed up, about 10 years worth. IMO that data should be pulled and stored elsewhere.
I am by no means a DB admin and frankly the entire concept of transaction logs is relatively new to me and quite daunting to say the least. To my understanding, a Full backup plus an unbroken chain of transaction log backups is enough to restore the database to the day prior to the point of failure.
The reason why this is acceptable is that there is another database that is storing the same data and acts as a hot backup for the main and both are running Raid 10. In case of failure the database can be restored to the day prior and the intraday data can be restored from the other Database.
From the owners perspective this is more than adequate and so I have to work with these limits.
January 21, 2011 at 4:01 am
CirquedeSQLeil (1/20/2011)
Your full backup takes 8hrs and you only backup the transaction logs once a day? How many transactions a day are you running?Do you need to be able to recover to a point in time during the previous day? If not, have you considered a differential backup?
How long do the transaction log backups take?
The full backup takes 8 hours due to historical data. but leave that be as there is nothing I can do about it.
Transactions per day? if each update to a row is considered a single transaction, then tens of thousands, maybe hundreds of thousands?
Differential won't do any good as it takes too long. There are other procedures that need to run that are fairly time consuming. If i tried to do both we would not be ready for the next business day.
I've no idea how long it takes as they've never been backed up before now. I imagine the first one will be quite time consuming, after that it should be relatively painless.
January 21, 2011 at 7:16 am
If you have a second server, then I would raise the potential issues, but not too hard. Likely you are protected enough for now.
I would make sure the backups move off the server quickly, in order to protect them. If there is a major issue, you want the backups somewhere they can be recovered. That means a separate physical array and copying them to another server.
I might also suggest that you add in more t-log backups so that they copy easier/quicker to somewhere else. More files to track, but it can provide a little better protection.
There is the possibility of filegroup backups, and moving historical data to a separate filegroup, but that's a little tricky if you aren't used to it. I would suggest you learn a bit about it, but if it sounds good, I'd hire a consultant for a day or two to help you get a plan to implement them. It won't be done in a few days, but you can understand and plan for it.
January 21, 2011 at 9:49 am
the backups are stored on an external HD. An array would be better but simply isn't going to happen...unfortunately...
But Thank you and everyone else for all your answers and suggestions, greatly appreciated.
January 22, 2011 at 10:05 am
fnostro (1/21/2011)
CirquedeSQLeil (1/20/2011)
Your full backup takes 8hrs and you only backup the transaction logs once a day? How many transactions a day are you running?Do you need to be able to recover to a point in time during the previous day? If not, have you considered a differential backup?
How long do the transaction log backups take?
The full backup takes 8 hours due to historical data. but leave that be as there is nothing I can do about it.
Transactions per day? if each update to a row is considered a single transaction, then tens of thousands, maybe hundreds of thousands?
Differential won't do any good as it takes too long. There are other procedures that need to run that are fairly time consuming. If i tried to do both we would not be ready for the next business day.
I've no idea how long it takes as they've never been backed up before now. I imagine the first one will be quite time consuming, after that it should be relatively painless.
Sorry - but I am not going to leave that 8 hour backup alone 🙂
I have a 1TB+ database that I can backup in less than 2 hours. This is using Litespeed to compress and speed up the backup, but even without that we would be backing that up in less than 4 hours using native.
If your backups are taking 8 hours - then you need to identify why it is taking so long and start working towards getting the right hardware in place to support the system.
With that said, for now I would recommend a weekly full, daily differential, and at least hourly transaction log backups. If you really only need to be able to recover to the previous day, you could change the recovery model to simple and not bother with backing up the transaction logs.
I would not recommend changing the recovery model on a production system.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 23, 2011 at 11:37 pm
Jeffrey Williams-493691 (1/22/2011)
Sorry - but I am not going to leave that 8 hour backup alone 🙂
As you wish...
Jeffrey Williams-493691 (1/22/2011)
I have a 1TB+ database that I can backup in less than 2 hours. This is using Litespeed to compress and speed up the backup, but even without that we would be backing that up in less than 4 hours using native.
Show off! 😛
Jeffrey Williams-493691 (1/22/2011)
If your backups are taking 8 hours - then you need to identify why it is taking so long and start working towards getting the right hardware in place to support the system.
ya think!
Let's go on the assumption that there is no money available for new equipment/software, let alone a consultant of any sort for any length of time.
Here's what I'm working with:
Dell Xeon Quad Core @ 2Ghz w/2GB Ram and a 60 GB RAID
Windows Server 2003 Standard edition (SP1)
Sql Server 2000 Standard edition - 8.00.760 (SP3)
USB 500GB HD
Now, by my math, a 38GB database *should* backup to a USB HD in about 15-20 minutes.
The full backup I scheduled for this past Saturday took 12 hours to write and 12 to verify.
So where is the bottleneck?
I'm leaning towards a configuration issue in SQL someplace because the only thing eating he CPU during backups is sqlserver.
Jeffrey Williams-493691 (1/22/2011)
With that said, for now I would recommend a weekly full, daily differential, and at least hourly transaction log backups. If you really only need to be able to recover to the previous day, you could change the recovery model to simple and not bother with backing up the transaction logs.
I would not recommend changing the recovery model on a production system.
As it turns out the Recovery model is indeed set to Simple for all the Databases, so if I read the documentation correctly, backing up the transaction logs for recovery purposes is moot.
I've set up a full backup for Saturdays with a nightly differential Monday thru Friday.
Do I still need to run regular backups of the TL anyway to keep the size down or is there a better way?
The transaction log gets about 500,000 to 700,000 transactions per day. It tends to eat our remaining HD space if not monitored.
And here is a question that has been bugging me, why is there a transaction log at all if the database recovery model is set to Simple and the TL cannot be used in the recovery?
Thanks again for all your insights.
January 24, 2011 at 7:53 am
If you are set to simple mode, transaction log backups do not work or matter. Each checkpoint (every few minutes) results in a clearing of committed transactions from the log.
2GB RAM is low. The backup doesn't run through the buffer pool, but I would suspect that your SQL Server might be somewhat starved for memory. Have you checked target v total SQL Server memory? If it's not close to equal, I might beg for some $$ to add memory. That's a fairly cheap change and might dramatically improve performance. Course, if it's just the backup that's slow and not performance, maybe it doesn't matter.
If you are backing up to an external drive, the USB interface might be your bottleneck. However you'd have to experiment, and if you don't have any $$$, that might be something you live with.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply