July 9, 2015 at 2:25 am
I was trying to configure maintenance plan to take nightly full database backup and Log backup. I was trying to configure it like in attached file. Could some one please suggest links so that i can follow and configure as in attached file.
July 9, 2015 at 2:37 am
First, don't use the append existing option, or you'll end up with a single file with dozens of backups in it.
Second, have separate maintenance plans for full and log backups, since you'll want to do log backups more frequently than full backups.
Third, consider Ola Hallengren's database maintenance solution. You'll find it with a simple search. It's free and it's more flexible than maintenance plans.
John
July 9, 2015 at 3:37 am
Thanks for reply and suggestion Sir. As per our business requirement, we need to take only one time FULL database back up and transnational log backup . Could you please suggest me how to configure maintenance plan as in attached file. I was searching one you tube video or good url to set up that but could not find.
July 9, 2015 at 4:07 am
Taking one log backup a day is a very poor 'strategy'. If you're going to do that, just put the DB into simple recovery and then you don't need the log backup at all.
The reason for log backups is so that you can restore to point in time. You typically take them more once an hour, or more often, depending on the application's data loss allowance. Your current plan allows for 24 hours of data loss, so you may as well just do full backups and no log backups at all.
Please read through this http://www.sqlservercentral.com/articles/Administration/64582/
Maint plans are pretty easy. From management studio, object explorer, right click -> new maintenance plan, then add in the tasks you need, configure them to suit your requirements and that's about it.
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
July 9, 2015 at 4:09 am
What is the exact wording of your business requirement? Taking transaction log backups at the same frequency as full backups is useless and you may as well just put your database in simple recovery mode and not worry about log backups. I recommend you acquaint yourself fully with transaction logs and how they work. This[/url] is a good place to start.
John
July 10, 2015 at 12:15 am
Thanks Sir for reply.
We have transaction replication to 2 other instance's databases. if something happens and main database server there will be loss of 1-2 second's records only.
We have 3 database architecture as below.
RW-Database database server ( This database server is for read and write purpose)
R-Database server (This Database server is for Read only purpose )
DR-database server (This database is for disaster recovery purpose)
We have Active-Active fail over windows and SQL server fail-over cluster between WR and R server and have 3 node.
This is our database architecture. is it recommended to do frequent log back up for our scenario. IF we take log backup in business our. it will affect in replication right? Please suggest.
July 10, 2015 at 2:16 am
OK, even if you've tested your DR plan and you have proved that you can recover to fulfil the required RPO and RTO (recovery time objective and recovery point objective), you still have the issue that your transaction log is going to grow very large if you only back it up once a day. This will also affect the size of your full backup if you make it before you do the log backup. Read the article that Gail and I linked to, and you'll learn why it's important to take regular log backups in order to control the size of your transaction logs.
John
July 10, 2015 at 2:22 am
keshab.basnet (7/10/2015)
is it recommended to do frequent log back up for our scenario.
Yes. Your DR strategy doesn't protect you in the slightest from the accidentally dropped table, or incorrect data or a pile of other problems.
You should never have a single layer of protection (which you do), the rule for DR is the same as for security. Defend in depth.
IF we take log backup in business our. it will affect in replication right?
No, it won't
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply