Backup strategy

[olmos]

We have a system that is constantly recieving data - an intrusion detection system.
We cannot afford to lose any data coming into the database.

If we do a full backup, does the database need to be shut down ?
If so, how can we ensure we keep all data even when the database is
shutdown ?

thanks,
olmos

 

[ClaireHCM]

If we do a full backup, does the database need to be shut down ?
No,but try to backup during off-peak hours.
Take off-peak full database backup can prevent slowing down system performance

If so, how can we ensure we keep all data even when the database is shutdown ?
Schedule complete nightly database backup and tran log backup very 15 min or 30 min can help you bring back to all the data up to certain point of time.
There are 2 version of backup strategy
(1)Nightly full database backup(everyday)+15 min tran log backup(for sensitive database)/hourly tran log back(not that sensitive)
(2)Weekly full database backup+nightly differential backup+15min/hrly tran log backup

 

[ecobb]

My backup plans are set to do full backups every 4 hours, and Transaction Log backups every 30 min. Some of the databases are 10+ gigs, but most of them are less than 5 gigs, and I've not seen any performance issues yet. SQL Server is able to perform the backups without having to shut down the databases, it can even backup databases while they are being used.

The problem I've always had with only doing full backups once a day is, having to restore all of those Transaction Logs. If you backup your database at midnight(12:00am) and it fails the following night at 11:00pm, then you have to restore from a 23 hour old backup, and then restore every transaction log in the order it was taken since the last backup. If are backing up your logs ever 30 min, that's 46 logs you have to restore to get to the point of failure. BUT, if you backup your database every 4 hours, and let's say the last backup ran at 8:00pm, that's only 6 Transaction Logs to restore, 40 less than before.

Hope this Helps!

 

[ClaireHCM]

But only when your database is small,you can backup full database that often.
Backup took lots of I/O Usage

 

[MaffewW]

ECobb, you probably make things easier for yourself by doing differential backups as part of your strategy. For example a differential b/u every 3 hours then in your scenario above, you would restore the full back up, then the 21:00pm differential backup, then the logbacks from 21:00 until 23:00. This would have much less impact on system performance than doing a full back up on a 10gb database every 4 hours. I know that full backing up a 10gb database here certainly has an impact on performance, though I guess it depends on how busy your db is.

just a thought

thanks

Matt

Brighton, UK