February 12, 2009 at 8:28 am
I have a MS SQL 2005 server that is one of many systems I have to figure out for lack of documentation. My question is in regards to maintenance plans backups vs. a dump.
There is a nightly maintenance plan that does a full backup of all databases to a NAS device at 1 AM.
In addition, there is a scheduled SQL agent job that also runs at 1 AM, which runs the following steps:
BACKUP LOG DBName WITH TRUNCATE_ONLY
DUMP DATABASE DBName TO DBName_data_dump WITH INIT
With DBName_data_dump being a backup device that points to a share on another windows server. The share is on a SAN device that the MSSQL server also has an attachment to. I have no idea why.
Also, there are no Log backups in place, and all DB files are allowed to autogrow, including the log files.
It has been awhile since I was responsable for a SQL server (2.5 years) and that was SQL 2000. Seems to me that unless I am OK with recovering my databases back to EOB yesterday that I need to set up some transactional log backups.
So, my questions are this:
1) Can I get rid of the redundant SQL agent job that is doing the dumps? Or, maybe a better way to put it is, would you do it this way?
2) Since SQL 2005 is new to me, can you point me in the right direction to setup hourly transactional log backups?
Thanks,
David Griswold
IT Manager
OneWorld Community Health Centers
February 12, 2009 at 8:33 am
Per Books Online:
The DUMP statement is included for backward compatibility. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Instead, use BACKUP.
I would definitely move towards getting rid of that, and setting up a maintenance plan that included daily full and hourly tran log backups. That's more standard.
Of course, your actual backup needs may vary, but that kind of plan is usually good for most needs.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 12, 2009 at 9:39 am
SSCarpel - thanks, that's what I was thinking. I don't want to assume I know best, but when you see something that is so very different than what I am used to, I thought I should ask.
David
February 12, 2009 at 1:12 pm
I wouldn't necessarily move to maintenance plans. That's up to you, and they work fine, but there are limitations and great scripts here on the site that can help you meet needs if the maintenance plans don't work.
For now, I'd replace DUMP with BACKUP DATABASE whenever I found it since DUMP might not be supported at some point if you upgrade.
February 12, 2009 at 1:19 pm
scripted backup\maintenance jobs are your best option. Maintenance plans are becoming legacy.
why truncate the log before the backup? What is the recovery model of the database?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 12, 2009 at 1:56 pm
"why truncate the log before the backup? "
Yeah, that's what I was thinking too. Like I said I didn't set this up, just trying to figure out what to keep and what to get rid of.
Thanks,
David
February 12, 2009 at 2:01 pm
Definitely don't truncate the log file. Didn't even notice that in there.
Steve: I have to ask, why not use maintenance plans? I've found them very reliable and easy to set up. Scripts work too, but they do pretty much the same thing in my experience. Is there somewhere that MS says they will be deprecated sometime soon?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 12, 2009 at 2:19 pm
GSquared (2/12/2009)
Definitely don't truncate the log file.
its not too much of a problem because a backup runs immediately after, but still not recommended.
scripts are easier to push out than plans, all plans do at the end of the day are created scheduled jobs. Scripted jobs are IMO much better. Certainly easier to modify and re deploy as well
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 12, 2009 at 3:12 pm
I'm sold. I would like to look into scripted backups.
I'll start looking around in the Scripts <---
David
February 12, 2009 at 3:47 pm
BACKUP LOG DBName WITH TRUNCATE_ONLY
DUMP DATABASE DBName TO DBName_data_dump WITH INIT
Here is my concern about the above. Scenerio setup: Nightly full backup with transaction log backups hourly between full backups.
Truncating the log just before the full backup breaks the log chain. If Monday nights (Tuesday mornings) full backup is accidently deleted or becomes corrupt and you need to restore to a point in time on Tuesday, you can't because of the log truncation just before the full backup. If the log is not truncated, you could use the previous days full backup plus all the transaction log backups after that backup to recover in this event.
February 12, 2009 at 4:43 pm
Lynn Pettis (2/12/2009)
Truncating the log just before the full backup breaks the log chain.
if you had to restore from the previous day without using the end of day backup you'd lose the last hours transactions because of the log truncation
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 12, 2009 at 7:25 pm
Starting with the full backup taken Sunday night/Monday morning with transaction log backups hourly until the full backup taken Monday night/Tuesday morning als followed by transaction log backups hourly.
If you had to restore to Tuesday morning at 6:00 AM, and the Monday night/Tuesday morning full backup was lost/deleted/corrupt, you could to it by starting with the previous full backup and all subsequent transaction log backups. However, if you truncated the transaction log just prior to the Monday night/Tuesday morning full backup, you'd break the log chain, and in the same circumstance, you'd be unable to restore your database to Tuesday morning 6:00 AM.
Agree?
February 13, 2009 at 2:34 am
in that scenario yes
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 13, 2009 at 7:02 am
Okay. That is why I am concerned about the log truncation just before running the full backup.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply