January 25, 2011 at 7:24 pm
I've gone through Managing Transaction Logs, http://www.sqlservercentral.com/articles/64582/
My final decision is
1. I choose Recovery Model is FULL
Based on article quote "Full recovery can be difficult to manage as the log can grow beyond expected if transaction log backups don’t occur, or if there’s an increase in the amount of database activity that occurs between log backups. Because log records are not discarded until they have been backed up, a database in full recovery mode can be recovered to any time using a combination of full, differential and log backups."
I would like in daily to schedule transaction log backups but I dont know, "How the scripts looks like"
Need help
January 25, 2011 at 7:29 pm
Little Nick (1/25/2011)
I've gone through Managing Transaction Logs, http://www.sqlservercentral.com/articles/64582/My final decision is
1. I choose Recovery Model is FULL
Based on article quote "Full recovery can be difficult to manage as the log can grow beyond expected if transaction log backups don’t occur, or if there’s an increase in the amount of database activity that occurs between log backups. Because log records are not discarded until they have been backed up, a database in full recovery mode can be recovered to any time using a combination of full, differential and log backups."
I would like in daily to schedule transaction log backups but I dont know, "How the scripts looks like"
Need help
Try looking up BACKUP LOG in Books Online.
January 25, 2011 at 7:48 pm
This is my syntax
BACKUP LOG CCN TO DISK='C:\2nd Database\Backup\CCN_log.bak'
I hope it's accurate
January 25, 2011 at 7:48 pm
Little Nick (1/25/2011)
I've gone through Managing Transaction Logs, http://www.sqlservercentral.com/articles/64582/My final decision is
1. I choose Recovery Model is FULL
Based on article quote "Full recovery can be difficult to manage as the log can grow beyond expected if transaction log backups don’t occur, or if there’s an increase in the amount of database activity that occurs between log backups. Because log records are not discarded until they have been backed up, a database in full recovery mode can be recovered to any time using a combination of full, differential and log backups."
I would like in daily to schedule transaction log backups but I dont know, "How the scripts looks like"
Need help
You can use the Maintenance Plan Wizard to easily set up & schedule all types of backups (Full, Diff, T-Log), along with cleanup tasks to delete files older than 'X' number of days.
In a production database, a FULL backup every night, and T-Log backups every 15-20 minutes is common. Your needs may vary.
January 25, 2011 at 7:55 pm
Little Nick (1/25/2011)
This is my syntax
BACKUP LOG CCN TO DISK='C:\2nd Database\Backup\CCN_log.bak'I hope it's accurate
Hopefully you have read all of the info regarding BACKUP LOG. Your command above will create the file (C:\2nd Database\Backup\CCN_log.bak) the first time it is run and the file does not exist. Subsequent BACKUP LOG statements, if identical, will append the log file backup to that file.
You may want to consider using a maintenance plan as suggested by Homebrew01.
January 25, 2011 at 8:07 pm
In order to create a maintenance plan in SQL Server 2008, open SQL Server Management Studio > Object Explorer > Expand Server > Expand Management > Right-click Maintenance Plans
I can't see Expand Server in my Object Explorer
January 25, 2011 at 8:18 pm
I don't have 2008, sorry.
From Microsoft:
To start the Maintenance Plan Wizard
Expand the server.
Expand the Management folder.
Right-click Maintenance Plans and select Maintenance Plan Wizard. This launches the wizard and you can now step through and create a plan customized to meet your maintenance requirements.
January 25, 2011 at 8:26 pm
tq to all. all answers is my inspiration
January 25, 2011 at 10:32 pm
Daily is not enough for transaction logs. At that rate you may as well use simple recovery. Hourly, every 30 min or every 15 are far more reasonable.
In the case of data loss, what's your allowable data loss for this system?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 25, 2011 at 10:56 pm
I dont understand the meaning of allowable data loss
January 26, 2011 at 12:54 am
If there's a disaster (drive fails, server blows up, server room goes up in flames, etc), how much data loss is considered 'acceptable' by business? Normally measured in minutes, and zero is not a valid answer.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 26, 2011 at 2:14 am
They can accept 50% of the loses
January 26, 2011 at 2:22 am
Err... 50% of what?
Data loss allowance is typically measured in minutes or hours, it's a measurement of time. It affects how often you schedule the backups, the granularity of log backups, etc
e.g. if business says that no more than 10 minutes of data can be lost in a disaster, then scheduling the log backups hourly is a risk as you could lose up to 60 minutes of data in a disaster, which is well over the SLA (service level agreement).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 26, 2011 at 2:40 am
Main stakeholders said not more than 20 minutes of data can be lost in disaster.
January 26, 2011 at 2:45 am
In that case you need to schedule your log backups at least every 20 minutes. Make sure that they, like your full backups, are copied off the server to some other storage (nothing more useless than backups that only exist on the same drive as the data file)
Once you get the log backups scheduled and running, monitor the used portion of the log for a couple of days. DBCC SQLPERF(LogSpace) will show you the % used. With that and the total size you can calculate the space used. Give it a couple days at least so you can see the patterns of the log usage. When you know what the max used size of the log is, you can do a once-off shrink of the log file to that size + 10%
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply