June 9, 2011 at 8:22 am
The networking group (the guys with the tape backup unit) need me to backup certain databases on the the last workday of the last week of the month ("end of month"). For example, if the last day of a month is Saturday the 31st they want this backup to happen on the 30th. Any ideas how to approach this? (I can only think scripting, reflecting Holidays being the last workday of the week, etc -- ugly.)
TIA,
Barkingdog
June 10, 2011 at 2:45 pm
Barkingdog (6/9/2011)
The networking group (the guys with the tape backup unit) need me to backup certain databases on the the last workday of the last week of the month ("end of month"). For example, if the last day of a month is Saturday the 31st they want this backup to happen on the 30th. Any ideas how to approach this? (I can only think scripting, reflecting Holidays being the last workday of the week, etc -- ugly.)TIA,
Barkingdog
Why wouldn't you just backup the database every day? Not sure I would even consider not backing up a database on a daily basis - since that is the only way I could guarantee that we didn't lose a full month's worth of data in the event of a disaster.
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
June 11, 2011 at 8:58 am
Why not a full backup every day? Time window.
The databases on this server are related to our datawarehouse and the smallest one is 500GB. We also need to copy the dumps to tape as well as run nightly maintenance jobs so.... couple that with several such servers and we have on occasion had tape backups running into production hours. We are trying to trim our dump sizes andtimes so "differetial" backups are being considered.
Barkingdog
June 11, 2011 at 9:53 am
If it's last workday and holidays come into play, then you need scripting. There are a few good calendar table articles here. I'd build one and then use a short script that joins the current day with the calendar table, and if it's the last workday in a month, execute the backup. Should be a simple query to write.
June 11, 2011 at 10:17 am
I guess I still don't get it - you are looking at modifying when you backup the databases, and not when that is copied to tape.
I would be worried about the potential data loss and how much effort it is going to take to restore the system in the event of a disaster.
Can you afford to lose a full month's worth of data and rebuild the system from the previous backup? Do you have access to all of the data used to build the system? If so, how long is that going to take you to recover?
Let's say you have a system failure on the day before your backup is scheduled. To restore the system, you have to get the tape and restore the previous months backup (how long is this going to take?), then you have to restore the database (how long will the restore take?), and finally - you have to get all of the data that was loaded for the last month and reload that...
If your plan is to perform a full backup monthly, and differentials daily - that could work, but probably will not save you anything because of the size of the differentials. The aggregate total of the sizes of the differentials are going to exceed the size of a full backup - probably in just a couple of days.
Remember, differentials contain all changes since the last full backup. On a data warehouse system, you load a lot of data during the build process - and probably rebuild indexes after the load. Once you rebuild those indexes, your differentials are going to contain every table that is touched - all rows in the table.
Even if you do not rebuild the indexes, if you are loading millions of rows a day that is going to add up over time. So, instead of keeping a copy of the full backup on disk (500GB) - you now have to keep that copy on disk, plus each differential from that point forward until the next full backup.
And, you cannot delete the old full and differentials until you have made sure they are copied to tape - or you lose the ability to restore to the differentials that have not been copied to tape if you need to go back to the previous month.
If you are running into timing issues, you need to look at using some type of compression utility (Redgate, Idera, Quest or native compression). Make the database backup files smaller so they can be copied to tape faster. Or, you need to look at what the SAN has to offer - there are a lot of opportunities there that can reduce how long your backups take, or what kind of impact copying to tape has.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply