<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 Administration
»
Back-up strategy
17 posts, Page 1 of 2
1
2
»»
Back-up strategy
Rate Topic
Display Mode
Topic Options
Author
Message
kapfundestanley
kapfundestanley
Posted Tuesday, January 03, 2012 7:38 AM
Right there with Babe
Group: General Forum Members
Last Login: 2 days ago @ 1:47 AM
Points: 774,
Visits: 1,047
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
Post #1229265
GilaMonster
GilaMonster
Posted Tuesday, January 03, 2012 7:45 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 37,722,
Visits: 29,977
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 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1229270
Grant Fritchey
Grant Fritchey
Posted Tuesday, January 03, 2012 8:53 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 13,380,
Visits: 25,164
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
The Scary DBA
Author of:
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for
Red Gate Software
Post #1229326
have2much
have2much
Posted Tuesday, January 03, 2012 11:34 AM
Forum Newbie
Group: General Forum Members
Last Login: Monday, January 09, 2012 10:05 PM
Points: 7,
Visits: 12
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.
Post #1229474
GilaMonster
GilaMonster
Posted Tuesday, January 03, 2012 12:05 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 37,722,
Visits: 29,977
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 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1229492
have2much
have2much
Posted Tuesday, January 03, 2012 1:49 PM
Forum Newbie
Group: General Forum Members
Last Login: Monday, January 09, 2012 10:05 PM
Points: 7,
Visits: 12
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
Post #1229541
GilaMonster
GilaMonster
Posted Tuesday, January 03, 2012 2:03 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 37,722,
Visits: 29,977
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 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1229551
kapfundestanley
kapfundestanley
Posted Wednesday, January 04, 2012 12:11 AM
Right there with Babe
Group: General Forum Members
Last Login: 2 days ago @ 1:47 AM
Points: 774,
Visits: 1,047
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
Post #1229695
GilaMonster
GilaMonster
Posted Wednesday, January 04, 2012 1:57 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 37,722,
Visits: 29,977
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 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1229717
kapfundestanley
kapfundestanley
Posted Wednesday, January 04, 2012 2:14 AM
Right there with Babe
Group: General Forum Members
Last Login: 2 days ago @ 1:47 AM
Points: 774,
Visits: 1,047
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
Post #1229724
« Prev Topic
|
Next Topic »
17 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
