May 14, 2012 at 1:05 am
We have two database servers...one is QA and one is PROD
there are about 5 separate databases on each server
i want them to back up on a schedule to a mapped drive as follows
First saturday of the month: 1st full back up of all databases on 100
monday - incremental backup
tuesday - incremental backup
wednesday - incremental backup
thursday - incremental backup
friday - incremental backup
2nd saturday of the month: 2nd full back up /delete the incremental backups
sunday - incremental backup
monday - incremental backup
tuesday - incremental backup
wednesday - incremental backup
thursday - incremental backup
friday - incremental backup
3nd saturday of the month: 3rd full back up /delete the incremental backups/ delete 1st full back up
sunday - incremental backup
monday - incremental backup
tuesday - incremental backup
wednesday - incremental backup
thursday - incremental backup
friday - incremental backup
similarily the 4th saturday too
Any hint, how to accomplish this task via t sql script or through Maintenence plans?
Whcih is easier and better...
Regards,
Skybvi
Regards
Sushant Kumar
MCTS,MCP
May 14, 2012 at 6:37 am
To create this through maintenance plans, just follow the wizard. It'll walk you through it. It's easy. Try it out first on your QA system so you feel confident, but Maintenance Plans are incredibly simple. T-SQL is going to be more work. In fact, I'd suggest, if you're going to go the T-SQL route, don't do that work. Instead, see if you can go through the documentation located here[/url] and make Ola Hollengren's scripts work within your environment. It's going to be more difficult to set up than using the Maintenance plans, but you get more flexibility and power to control exactly when/how your backups take place.
One concern I do see for your plan, what about log backups? Are all these databases in Simple Recovery mode? Is the business OK with the idea that you can lose all data for a given day, back to the last incremental or full backup? If so, great. If not, you also need to factor in log backups on a regular basis.
"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
May 14, 2012 at 8:53 am
By 'incremental backup' do you mean 'differential backup' or 'transaction log backup'?
If you mean differential, then do you really want to delete them? If you're not doing transaction log backups, you lose the ability to restore to any day of the previous week except Saturday.
Even if you do back up the log, it should be faster to restore the full backup, then the latest differential, then the transaction logs.
May 14, 2012 at 10:19 am
I assume you mean differential since there is not incremental. Grant and Peter have good advice. Here's what I'd do, given what I think you are doing.
Dev:
Sat: full backup, delete 3rd full backup (keep 2)
Sun-Fri: differential backup, delete 3rd oldest diff, keeping 2 on disk.
Log backups as needed. I would assume point in time recovery isn't an issue in dev. You can use simple mode, or do a log backup a day, whack the 3rd oldest one every time.
This gives you recoverability for the most part back two days, or a week in the worst case.
For Prod, I would do something similar, except I would definitely add log backups, and until I deleted a full backup, I wouldn't remove any logs made since that full backup.
May 14, 2012 at 11:23 am
Steve Jones - SSC Editor (5/14/2012)
I assume you mean differential since there is not incremental. Grant and Peter have good advice. Here's what I'd do, given what I think you are doing.Dev:
Sat: full backup, delete 3rd full backup (keep 2)
Sun-Fri: differential backup, delete 3rd oldest diff, keeping 2 on disk.
Log backups as needed. I would assume point in time recovery isn't an issue in dev. You can use simple mode, or do a log backup a day, whack the 3rd oldest one every time.
This gives you recoverability for the most part back two days, or a week in the worst case.
For Prod, I would do something similar, except I would definitely add log backups, and until I deleted a full backup, I wouldn't remove any logs made since that full backup.
Have to agree with Steve here. I also agree with Grant on his suggestion. I haven't used those scripts myself having rolled my own where I needed them.
May 16, 2012 at 7:18 am
Peter Maloof (5/14/2012)
By 'incremental backup' do you mean 'differential backup' or 'transaction log backup'?If you mean differential, then do you really want to delete them? If you're not doing transaction log backups, you lose the ability to restore to any day of the previous week except Saturday.
Even if you do back up the log, it should be faster to restore the full backup, then the latest differential, then the transaction logs.
Best practice suggested by peter.... and this can be achieved by using scheduled job or any script easily.
Best backup plan as you can say like
Daily full backup every day 01:00 AM
Differential backup every 4 hours.
T-Log backup every 15 mins.
Very less data loss, and even we can recover data in those last 15 mins as well if failed .....
Sagar Sonawane
** Every DBA has his day!!:cool:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply