March 24, 2009 at 11:41 am
Hello,
We have our backup strategy as below:
1. Full backup dailly at 1:00AM
2.Diffrential every 4 hrs starting from 5:00AM
3.Log backup every 1 hr starting from 2:00AM
Here at 5:00AM, there will be a differential backup and also log backup occuring.
If something happend at 7:00AM,
1.restore the full backup of 1:00AM
2.restore 5:00AM differential
3.Here before restoring 6:00AM logbackup, do I need to restore 5:00AM log backup?
How exactly it works?
March 24, 2009 at 12:10 pm
I don't think so. I think the diff backup is when it ends.
You could just offset your logs slightly. It will also tell you if you're out of sequence with the 6am log backup.
March 24, 2009 at 12:33 pm
You want me schedule the log backup every hr starting from 2:01AM(1 min past 2:00AM)
Then diff backup will occur at 5:00AM and log backup will occur every hr like 2:01, 3:01...5:01
Then while restore, restore full of 1:00AM, restore diff of 5:00AM and restore log backup of 5:01 and 6:01 is that your telling? correct me if I misunderstood.
thanx
March 24, 2009 at 12:38 pm
klnsuddu (3/24/2009)
You want me schedule the log backup every hr starting from 2:01AM(1 min past 2:00AM)Then diff backup will occur at 5:00AM and log backup will occur every hr like 2:01, 3:01...5:01
Then while restore, restore full of 1:00AM, restore diff of 5:00AM and restore log backup of 5:01 and 6:01 is that your telling? correct me if I misunderstood.
thanx
I think that's what he means too
but unless your DB is very small, give the DIFF backup more than 1 minute but 15~30 minutes
so you have a FULL @ 1 am, DIFF @ 5 am, TLOG backup @ 5:30 am
Normally I don't even worry about such issues, hopefully no issues
March 28, 2009 at 3:55 pm
klnsuddu (3/24/2009)
Hello,We have our backup strategy as below:
1. Full backup dailly at 1:00AM
2.Diffrential every 4 hrs starting from 5:00AM
3.Log backup every 1 hr starting from 2:00AM
But, Why are you taking log backups each an hour?? I assume you know that you are not truncating the log for an hour which obviously makes your transaction log huge if you perform any heavy transactions and also having such a huge log backups would slow down your restoration and narrow down your point in time recovery.
Can you explain your backup strategy please?(just a matter of curiosity:-D)
March 31, 2009 at 10:38 am
Hi Krishna,
We have our backup strategy as below: We are following this strategy to keep the data loss minimum in case of any disaster
1. Full backup dailly at 1:00AM
2.Diffrential every 4 hrs starting from 5:00AM
3.Log backup every 1 hr starting from 2:00AM
How actually you guys schedule Full, diff,log backups? could you please explain me
March 31, 2009 at 11:04 am
Hello,
It depends on your business requirements and how much of data loss your business can afford. If your business can afford 1 hr loss of data then your plan is fine as you are taking tlog backups for every one hour. At most you can lose 1hr worth data but still you can narrow it down by taking a log backup in case a disaster occurs.
You could start this way:
oEstablish a full backup schedule either on a multi-day, daily, weekly or monthly basis
oIncorporate differential backups into the plan to increase the available recovery points between full backups
oIf additional points in time are needed, then consider transaction log backups as a solution to meet a finer level of granularity for recovery purposes
Your plan is fine, but I was just curious. Well the safe way is to take frequent transaction log backups and Differential to compliment your Full database backup.
Planning according to your SLA's and your business requirements? Any questions please post them here we'll help you out.
March 31, 2009 at 11:17 am
I still have the same question and Iam not claer how all the DBA's schedule the Backups. For Our Backup startegy Could you please tell me how can I schedule the backups
1. Full backup dailly at 1:00AM
2.Diffrential every 4 hrs starting from 5:00AM
3.Log backup every 1 hr starting from 2:00AM
Here at 5:00AM, there will be a differential backup and also log backup occuring.
If something happend at 7:00AM,
1.restore the full backup of 1:00AM
2.restore 5:00AM differential
3.Here before restoring 6:00AM logbackup, do I need to restore 5:00AM log backup?
As forum member saying chage the log backup schedule something like 15 min later, I mean starting from 2:15 (In my case)
is this the way you guys setup?
March 31, 2009 at 11:34 am
klnsuddu (3/31/2009)
Here at 5:00AM, there will be a differential backup and also log backup occuring.
If something happend at 7:00AM,
1.restore the full backup of 1:00AM
2.restore 5:00AM differential
3.Here before restoring 6:00AM logbackup, do I need to restore 5:00AM log backup?
No you don't have to restore tlog backup if you are considering the differential log backup at 5:00 AM, remember Differential log backup contains all the data changed after most recent Full backup, that is in your case it contains all the changed data from 1:00 till 5:00 AM
So, you restore it in 2 ways:
Restore 1:00 AM Full Backup WITH NO RECOVERY,
Restore 5:00 AM Differential WITH NO RECOVERY
Restore 6:00 AM Tlog backup WITH NO RECOVERY and
Restore 7:00 AM Tlog backup WITH REOCVERY
OR
Restore 1:00 AM Full Backup WITH NO RECOVERY and
Restore all the log backups from 2:00 AM till 6:00 AM WITH NO RECOVERY
Restore the last 7:00 AM tlog backup WITH RECOVERY
Hope this helps!
April 1, 2009 at 1:30 am
klnsuddu (3/31/2009)
I still have the same question and Iam not claer how all the DBA's schedule the Backups.
As someone already replied, mostly DBA are not responsible for deciding backup strategy, it depends on business owners to decide such strategies. As a dba we can suggest and choose the best option available. I work with several application teams, we follow different backup strategy, as required by application owners.
For your question of disaster @7:00 Am, if your transaction backup is recent than your diff. backup, restore will happen, otherwise, it will fail. No need of bothering that much. You can grant following command without giving any backup file name to recover if it failed -->
restore database your_db_name with recovery
April 2, 2009 at 7:08 am
You need to look at your recovery SLAs when you plan your backup strategy. The main point in taking a backup is to be able to do a recovery, so business expectations for recovery should govern your backup strategy.
If the business is happy with restoring to the end of the last busines day and loosing any updates since that time, put the database in SIMPLE mode and take a full backup at the end of the business day (whatever that means at your site).
If the business expects you to recover to the point of failure with no dat aloss then you need the database in FULL recovery mode and you need to do full and transaction log backups.
If the business can wait maybe 4 hours while you get the data back, then maybe you don't need to do any differential backups.
If the business wants the data recovered within maybe 2 hours then differential backups become important.
If the business wants the data back within 10 minutes then you must use techniques such as mirroring or clustering to achieve this.
Finally, it is best to avoid running Full and Transaction log backups on the same database at the same time. You can get locking issues between the backups, which can cause one of the backups to wait or fail.
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
April 2, 2009 at 5:58 pm
EdVassie (4/2/2009)
Finally, it is best to avoid running Full and Transaction log backups on the same database at the same time. You can get locking issues between the backups, which can cause one of the backups to wait or fail.
I thought they would run fine. Because Tlog backups tracks only changed extents extents and also I think they run at very low isolation levels? is it not?
April 2, 2009 at 8:17 pm
Krishna Potlakayala (4/2/2009)
EdVassie (4/2/2009)
Finally, it is best to avoid running Full and Transaction log backups on the same database at the same time. You can get locking issues between the backups, which can cause one of the backups to wait or fail.I thought they would run fine. Because Tlog backups tracks only changed extents extents and also I think they run at very low isolation levels? is it not?
This use to be an issue on SQL 2000 - but, it has been fixed in 2005.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 2, 2009 at 8:26 pm
Jeffrey Williams (4/2/2009)
Krishna Potlakayala (4/2/2009)
EdVassie (4/2/2009)
Finally, it is best to avoid running Full and Transaction log backups on the same database at the same time. You can get locking issues between the backups, which can cause one of the backups to wait or fail.I thought they would run fine. Because Tlog backups tracks only changed extents extents and also I think they run at very low isolation levels? is it not?
This use to be an issue on SQL 2000 - but, it has been fixed in 2005.
Yes, so shouldn't be a problem if backups run at the same time and also just wondering at what isolation level does the backup process reads the extents in the database while taking backups?
April 3, 2009 at 2:21 am
This use to be an issue on SQL 2000 - but, it has been fixed in 2005.
Jeffrey - thanks for the update, I had missed that one!
Krishna, a log backup normally only reads the log file, so there are no locking or isolation issues on the data files. There are locks taken on other objects, such as a schema lock to prevent anyone dropping the database while a bacukp is running.
However, where a database is in Bulk Logged mode the log backup will read the data files to include the extents updated by the bulk logged insert. I am not sure what isolation mode would be used for this, but my expectation it is read uncommitted as by its nature the log backup does not need to know what rows are committed. When you do a recovery, when you get to the end-point of the recovery (PT, end of log, etc) the recovery process will work out what transactions are not complete and back them out.
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply