asked on SQL Server maintenance plan
Although I've used SQL Server extensively over the years, I've never really had to worry about maintenance plans and backups and all that. So I now have a client who has several SQL Servers and I'm trying to develop a maintenance plan for one of those servers, the one where my data resides.
My thought is that I would like to perform full backups of each of the databases on the server (including the server databases) weekly, and then perform incremental backups nightly, so at all times, I would have two full backups, the incremental backups between those to, and then during the 2nd week I would end up with 6 daily incrementals after the most recent backup.
Several questions:
1. Do I really need to have the two fulls and all the incrementals, or should I simply delete the previous full and all the incrementals after the new full runs successfully? It looks like the default for this is to expire the backup set after 14 days, is that normal?
2. Should I do this all within the Maintenance Plan, using TSQL to perform individual tasks, or create SQL agent jobs and scheduling them separately?
My thought is that I would like to perform full backups of each of the databases on the server (including the server databases) weekly, and then perform incremental backups nightly, so at all times, I would have two full backups, the incremental backups between those to, and then during the 2nd week I would end up with 6 daily incrementals after the most recent backup.
Several questions:
1. Do I really need to have the two fulls and all the incrementals, or should I simply delete the previous full and all the incrementals after the new full runs successfully? It looks like the default for this is to expire the backup set after 14 days, is that normal?
2. Should I do this all within the Maintenance Plan, using TSQL to perform individual tasks, or create SQL agent jobs and scheduling them separately?
Microsoft SQL ServerMicrosoft SQL Server 2008
Last Comment
Dale Fye
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Do you have any software that you are using to backup the server and the drives that you are looking to have for the sql backups?
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
The data on this server is all reporting oriented, although not truly configured as a data warehouse. We pull data from other, operational databases about 05:00 each morning, and actually merge 7 days worth of data from these other systems, because some of those values can be updated for up to 7 days, so on this particular server, we are not going to loose any data if we only perform full backups weekly and differential backups nightly.
Just out of interest, why do you find a need to maintain 4 full backups? I can understand differential backups every 4 hours and holding on to a couple of full backups, but why 4? are you destroying the differential backups between those full backups, or do you maintian all of those differential backups as well?
Do you perform the differentials every 4 hours, including the period where you are performing the full backup?
Just out of interest, why do you find a need to maintain 4 full backups? I can understand differential backups every 4 hours and holding on to a couple of full backups, but why 4? are you destroying the differential backups between those full backups, or do you maintian all of those differential backups as well?
Do you perform the differentials every 4 hours, including the period where you are performing the full backup?
Hi Dale.
I'm keeping 4 Full backups because on day 5th my whole network is going to Tape Backup and in case of some emergency I need to have option to go back to full database backup up to 4 days back.
I'm performing Incremental backup from 6AM to 10PM and this is SLQ agent backup to disk, because At 11PM my all servers incremental backup to NAS is starting so I'm OK.
I'm keeping 4 Full backups because on day 5th my whole network is going to Tape Backup and in case of some emergency I need to have option to go back to full database backup up to 4 days back.
I'm performing Incremental backup from 6AM to 10PM and this is SLQ agent backup to disk, because At 11PM my all servers incremental backup to NAS is starting so I'm OK.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Dale, a feedback will be appreciated.
Cheers
Cheers
ASKER
Thanks for the help, guys.