December 9, 2010 at 9:16 am
We have a very specific scenario.
CLARIFICAITION!!!!!
The customer has indicated we will NEVER need to restore to a point in time before our last Full or Incremental backup.
CURRENT CONFIGURATION / PROCESS:
1. Simple Recovery Mode in SQL Server 2000
2. We take a full backup every Saturday at 2:45 AM and incremental backups every 4 hours every day starting at 7:45 AM until 2:45 AM the next day. (We have big batch process that runs between 3 and 7 AM and thus no backups during that time.)
3. With each backup (full or incremental) we truncate the transaction log (This is needed due to lack of disk space on the physical server and the inability to add more at this time.)
PLANNED CONFIGURATION / PROCESS:
NOTE NOTE NOTE: We would like to make AS FEW CHANGES AS POSSIBLE in the new configuration until everything has stabilized with the migration.
1. Full Recovery Mode in SQL Server 2008
2. We take a full backup every Saturday at 2:45 AM and incremental backups every 4 hours every day starting at 7:45 AM until 2:45 AM the next day. (We have big batch process that runs between 3 and 7 AM and thus no backups during that time.)
3. With each backup (full or incremental) we truncate the transaction log (We would still like to do this so we don't have to change our processes as part of the migration.)
Would the planned configuration and process allow us to forward recover our SQL Server 2008 database from the last backup to the point in time a failure occurred (assuming a tail log backup)? If not -- why? I have read that TRUNCTING the log file will ONLY get rid of transactions SQL Server decided it no longer needed and since we will NEVER need to recover to a point in time BEFORE our last backup, all the needed transactions should be in the current log files that we would run the tail log backup against correct?
December 9, 2010 at 9:28 am
GCeaser@aol.com (12/9/2010)
1. Full Recovery Mode in SQL Server 2008
Why full recovery if you never need to run log backups?
3. With each backup (full or incremental) we truncate the transaction log (We would still like to do this so we don't have to change our processes as part of the migration.)
You cannot truncate the log (backup log ... truncate only) on SQL 2008. The command has been removed. (it was deprecated on SQL 2005). The replacement is switching the database recovery model to Simple Recovery.
If not -- why? I have read that TRUNCTING the log file will ONLY get rid of transactions SQL Server decided it no longer needed and since we will NEVER need to recover to a point in time BEFORE our last backup, all the needed transactions should be in the current log files that we would run the tail log backup against correct?
Nope.
Truncating the log (backup log ... truncate only) says to SQL 'break the log chain, stop retaining log records once the transactions become inactive'
If you truncate the log then, at any point after that but before another full/diff backup, attempt to take a log backup the log backup will fail with a message indicating that there is no current database backup. The truncating of the log switched the DB into a pseudo-simple recovery model.
Just put the database into simple recovery and leave it alone. Any form of log truncation prevents log backups (including tail-log backup) after that and quite frankly is mismanagement of the log.
Full recovery is for when you need log backups and point in time recovery. If you don't, switch to simple recovery and accept that you will not be able to do any form of point-in-time restores.
p.s. SQL doesn't have incremental backups. It has full, differential and log.
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
December 9, 2010 at 10:00 am
GilaMonster (12/9/2010)
GCeaser@aol.com (12/9/2010)
1. Full Recovery Mode in SQL Server 2008
Why full recovery if you never need to run log backups?
Please read - I did not say we never need to run log backups -- we would only need to run it as a tail log backup though - at least in theory.
3. With each backup (full or incremental) we truncate the transaction log (We would still like to do this so we don't have to change our processes as part of the migration.)
You cannot truncate the log (backup log ... truncate only) on SQL 2008. The command has been removed. (it was deprecated on SQL 2005). The replacement is switching the database recovery model to Simple Recovery.
That is what I was trying to figure out. So the only way to truncate a transaction log file in SQL Server 2008 is a log backup.
If not -- why? I have read that TRUNCTING the log file will ONLY get rid of transactions SQL Server decided it no longer needed and since we will NEVER need to recover to a point in time BEFORE our last backup, all the needed transactions should be in the current log files that we would run the tail log backup against correct?
Nope.
Truncating the log (backup log ... truncate only) says to SQL 'break the log chain, stop retaining log records once the transactions become inactive'
If you truncate the log then, at any point after that but before another full/diff backup, attempt to take a log backup the log backup will fail with a message indicating that there is no current database backup. The truncating of the log switched the DB into a pseudo-simple recovery model.
Just put the database into simple recovery and leave it alone. Any form of log truncation prevents log backups (including tail-log backup) after that and quite frankly is mismanagement of the log.
Full recovery is for when you need log backups and point in time recovery. If you don't, switch to simple recovery and accept that you will not be able to do any form of point-in-time restores.
p.s. SQL doesn't have incremental backups. It has full, differential and log.
So the big point here is that in SQL Server 2008 it is impossible to truncate the log file unless you have backed it up. Also - simple recovery mode will not allow us to FORWARD recover the database from the last backup. Thus we would loose all transactions from the last backup to point of failure. That is what we are trying to avoid.
The tail log backup would of course only be taken and used in the event of a database failure to forward recover as I indicated in my original posting.
So it looks like we will need to make changes to the processes as it is impossible to truncate the logs outside a log backup so our existing processes will simply not work.
December 9, 2010 at 10:38 am
GCeaser@aol.com (12/9/2010)
....The tail log backup would of course only be taken and used in the event of a database failure to forward recover as I indicated in my original posting.
Did you ever consider how big your tail would be ?
Because you weren't planing on taking regular log backups, it would just keep on growing.
So it looks like we will need to make changes to the processes as it is impossible to truncate the logs outside a log backup so our existing processes will simply not work.
Indeed.
Don't take the one that stated "we never need PIT recovery" on his word, because eventually you will be the one trying to recover a database that just crashed.
If you don't need PIT recovery before the last full recovery, just delete the log-backup files after you've made the full backup and then start a new log backup sequence.
Make sure YOU are covered with this procedure, it will be you taking the hits at disaster time !
Off course, it is the better strategy for the company too.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply