January 3, 2012 at 7:38 am
What would you recommend as the ideal back-up strategy for SQL Server?Currently i do daily full back-ups and burn them all to disc and my database are mirrored.However i feel this is not adequate.What are the weakness of this strategy?If I can improve it,what do i need to do?
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
January 3, 2012 at 7:45 am
kapfundestanley (1/3/2012)
What would you recommend as the ideal back-up strategy for SQL Server?
There's no one answer to that. It depends on the size of the DB, your backup windows, your RTO and RPO agreements and a few other things.
Some reading on backup strategies:
http://sqlskills.com/BLOGS/PAUL/post/Planning-a-backup-strategy.aspx
http://sqlskills.com/BLOGS/PAUL/post/Importance-of-having-the-right-backups.aspx
http://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx <- Must read
http://technet.microsoft.com/en-us/sqlserver/gg545012.aspx Video
Currently i do daily full back-ups and burn them all to disc and my database are mirrored.
No log backups? If you have mirroring, you're in full recovery and that means you need log backups to make the log space reusable. Please read through today's headline article - http://www.sqlservercentral.com/articles/Administration/64582/
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 3, 2012 at 8:53 am
The biggest question you have to answer is, how much data can the business afford to lose. Get a very solid answer to that question, and then you can build a backup strategy around it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 3, 2012 at 11:34 am
if the database is not too big, I will recommend going for nightly full backup with hourly or 2 hourly log backup to clear the logs. If the database is big then full backup once a week, differential backup for rest of the days of the week and log backup on hourly\2 hourly basis.
Since your databases are mirrored you always have a copy of db in mirror end even if the principal database goes down, but given the fact that mirroring tries to immediatly transfer all changes from principal to mirror I use the stategy of backup mentioned above so that you always have a choice to restore the database back to a previous state. classic example is if by mistake anybody run the incorrect update\delete statement, mirroring will replicate it to the mirror so you can only rollback it using the backups.
This is generalised option, it may defer depending on any exceptional condition that you may have in your databases setup.
January 3, 2012 at 12:05 pm
have2much (1/3/2012)
if the database is not too big, I will recommend going for nightly full backup with hourly or 2 hourly log backup to clear the logs.
Log backups are for point-in-time recovery and minimising data loss, not clearing the log. The time interval between log backups should be primarily decided by the RPO for the application. See today's headline article: http://www.sqlservercentral.com/articles/Administration/64582/
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 3, 2012 at 1:49 pm
Seems you misunderstood, if the recovery model of the database is full then the log will not be cleared unless the log backup or the full database backup is performed
January 3, 2012 at 2:03 pm
have2much (1/3/2012)
Seems you misunderstood, if the recovery model of the database is full then the log will not be cleared unless the log backup or the full database backup is performed
Full database backups don't clear the log. Only log backups do.
The main purpose of log backups is not to truncate the log. If all that's important is truncating the log, then switch the DB to simple recovery model and the log will automatically truncate (clear) on a regular basis.
If the DB is in full recovery (or bulk-logged) it's because point-in-time recovery and minimal data loss is important. In that case, the interval between log backups should be determined by the amount of data that one is willing to lose in the case of a disaster, not the need to truncate the log.
See today's headline article: http://www.sqlservercentral.com/articles/Administration/64582/ (which, btw, I wrote)
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 4, 2012 at 12:11 am
What's wrong with running a maintenance plan thats does log back-ups for my databases and store in a folder on the same machine.If I were to burn the log back-ups to disc,should i burn all of them...considering they are being done after every 2 hours?
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
January 4, 2012 at 1:57 am
kapfundestanley (1/4/2012)
What's wrong with running a maintenance plan thats does log back-ups for my databases and store in a folder on the same machine.
Other than the fact that if the drive fails you lose all of them, nothing much
If I were to burn the log back-ups to disc,should i burn all of them...considering they are being done after every 2 hours?
Well, to restore you need all log backups since the full backup you're using. If even one is missing, you can't restore past that point.
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 4, 2012 at 2:14 am
If I perform the latest full back-up i will not need the previous log back-ups right.
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
January 4, 2012 at 2:25 am
Providing the latest backup is undamaged at the time it's restored, yes. If it is damaged, then you'd have to go back to the previous one, meaning 2 or so days of data loss if you deleted the log backups.
Please, please go and read through all those backup strategy links I posted.
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 9, 2012 at 4:06 am
As other people have said, you need to start with the RTO and RPO for your applications. If you do not understand the importance of these terms then read about them and talk to your manager about them. The process of taking a backup serves no purpose on its own - a backup is only useful if you can restore from it, and a restore is only useful if your business can operate after the restore is complete.
It is also wise to always plan to have two routes for recovery, so that you can save the buisiness if something goes wrong with a given backup. If you have multiple applications and databases you also need to look at the 'jagged edge of recovery' where different databases get restored to different points in time and how your applications will cope with this.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 9, 2012 at 5:44 am
have2much (1/3/2012)
Seems you misunderstood, if the recovery model of the database is full then the log will not be cleared unless the log backup or the full database backup is performed
Thanks, that's the best laugh I've had in years.
It's like telling the fireman "man you don't know what it feels like to be in this heat... and talking about under 100 F tempreature" 😀
No offense intended, I guess you didn't know Gail 😉
January 9, 2012 at 10:04 pm
I believe it's a technical forum where we are discussing and helping other to find out a resolution. At times we may not very sure about the things and experienced person like Gail do correct and help guiding the right way..thanks to Gail
But then there are guys like you who are not interested to write a single line of technical information but enjoying making fun of others.
No offense but with your words it looks like you are born with sql server info in your head even before you learnt A,B C 😀 Enjoy making fun of other because you have only that to share.
January 11, 2012 at 8:16 pm
have2much (1/9/2012)
I believe it's a technical forum where we are discussing and helping other to find out a resolution. At times we may not very sure about the things and experienced person like Gail do correct and help guiding the right way..thanks to GailBut then there are guys like you who are not interested to write a single line of technical information but enjoying making fun of others.
I guess he's just meaning that you should have refrained from posting if you're not really sure what you're talking about. Explicit misinformation is a very bad thing specially on a forum so well known as this one, that everyone reads. But we're all learners here. I post stupid things from time to time and I'm trying to study a lot about something before hitting the REPLY button. But sometimes it doesn't work that way for me. But I learn a lot from people like
Ninja's_RGR'us and Gail. 🙂
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply