August 29, 2008 at 5:17 am
Hi Gurus,
I have just taken over the administration of a database. The backup procedure is
1. Shutdown SQL server
2. Copy the SQL files to "D:\Backup\yyyy mm dd"
3. Start SQL server
4. Restart the web services on DS-QL1 and DS-QL2
The content of D:\Backup is then backep-up to tape.
Do you think this system is ok or it should be changed?
Any idea including backup scripts will be greatly welcomed.
Many thanks.
Sahoong.
August 29, 2008 at 5:30 am
It all depends on the requirements you have for recovery and the requirements you have for downtime. With the stopping of the instance you have immediately incurred downtime and while you wait for the files to transfer you are still incurring downtime. As the database grows that time is going to increase.
Additionally, your requirement has to be considered and as of right now you can only get back to the prior day data.
If the downtime for backup and the data loss in the event of recovery are acceptable for your organization then what you have is probably ok. However, I'm not sure why you would do it that way even if that does meet the requirements as you can execute backups with the instance online and customers accessing the data. The backup files can be pulled to tape right from disk and all this happens without one second of downtime. AND, you can run other backups, whether they be differential or log, throughout the day so that your data loss potential becomes less and less.
Ultimately it is a business decision but I would guess that most databases will become business critical and will only increase in the role they have within a business. With that being said, we should architect them with that in mind and part of that planning and implementation is a backup solution that will be robust enough to make sure that we can provide the availability and recoverability that the business needs.
So, to answer the question more directly, the solution that is in place now may be sufficient but I would have to strongly state that it shouldn't stay that way.
Hope this helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 29, 2008 at 6:35 am
I agree with David, it is ultimately up to your customers or sponsors. However, let me add that you should let them know what their options are since they may not be aware.
I would suggest the backup plan that is currently in place be used on a periodic basis, like weekly or monthly, so that recovering the file system is possible, when necessary. Beyond that, assuming that the Database is mission critical, I would suggest that at least one Full Backup per day and then Transaction Log backups at least every hour during the business day. During low utilization time the Transaction Log backups can be reduced to every 2 or 4 hours.
All the databases I deal with are backed up more frequently than what I have stated here, but what I have learned is that Transaction Logs can really save your hide. Running them more frequently and on a consistent basis through the entire day is the best way to go.
Best of luck to you on figuring this one out.
Regards, Irish
August 29, 2008 at 9:59 am
sahoong (8/29/2008)
Hi Gurus,I have just taken over the administration of a database. The backup procedure is
1. Shutdown SQL server
2. Copy the SQL files to "D:\Backup\yyyy mm dd"
3. Start SQL server
4. Restart the web services on DS-QL1 and DS-QL2
The content of D:\Backup is then backep-up to tape.
Do you think this system is ok or it should be changed?
Any idea including backup scripts will be greatly welcomed.
Many thanks.
Sahoong.
Changed. As mentioned above, why go through all those steps and have downtime ??
Make sure DB is in FULL RECOVERY mode.
Run a regular SQL Backup and schedule it every night, pointing it to D:\Backup\Full.
Run transaction log backups every 30 minutes to D:\Backup\Logs.
August 29, 2008 at 3:00 pm
Several different ways to the same result.....
I do full backups on Sunday. I do logs during the weekdays, every hour. On weekday evening, I do differential backups. The logs will provide point in time retores, the diff allow me to skip a day (or 2 or 3, etc.) of log restores, depending on what point in time I'm trying to get to. All backup files are swept to tape each evening and archived. Files remain on disk for a week and tapes are reused annually.
-- You can't be late until you show up.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply