November 7, 2008 at 5:08 pm
Hi Folks,
Essentially, I’m wondering how often I should backup my database:
Usually, debases are backed up according to a schedule. for example every night at 2 am…
However the information that is important to me, is the site membership…and I’d like my backups to always have the all the current membership info.
(I’m creating a web site using asp.net with MS SQL as the membership provider)
By simply scheduling a database backup at set intervals…if something happens to the database in between backups…how will I know, how much information has been lost?
And how can the “in between info” be recovered?
So, to avoid losing any information, I’m thinking that the database should backup every time a new member is added…that way I’m sure to always have a backup with the latest info. Is this a good idea?
I’m thinking that it would ensure to have a proper backup, but my only concern with this approach, is how much performance will it drain? Probably too much…
although I’m not sure at what rate members will sign up…
What is the best practice regarding this dilemma?
Should I backup for each new member registration to ensure a comlete backup set?
Or is there a smarter way to recover data lost “in between” scheduled backups..?
Regards,
- Joel
November 7, 2008 at 5:23 pm
You need to lookup in Books Online the topics 'Recovery Model' and Backups. Basically, what you need is:
o Database in Full Recovery Model
o Daily Backups (2am)
o Hourly Transaction Log Backups (or more often - can be as often as every 5 minutes).
Optionally, you can include differential backups. Differential backups will shorten the time to recovery, but will require additional storage to keep the backups online.
Make sure you understand what the different recovery models are, and what the backup requirements are for each. For example, the simple recovery model does not allow transaction log backups - the full recovery mode requires frequent transaction log backups.
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
November 7, 2008 at 5:28 pm
I wouldn't recommend a full backup everytime something changes. The "standard" methodology is to take backups of the transaction log at regular intervals between full backups. With the last full backup and the accumulated logs, you can recover the database to the point of the last log backup.
For the "I can't afford to lose anything" scenario, replication, mirroring, or setting the database up on a clustered set of machines is necessary. These scenarios are more expensive than the ones mentioned above, so you really need to examine the financial consequences of lost data to make a good determination as to the appropriate backup strategy.
November 8, 2008 at 2:31 pm
First backup to separate drives than you store data on, or script out a copy immediately.
Depending on size, I'd run a full every night and differentials in the middle to speed recovery time. I'd then run logs every 5 minutes if you're worried, making sure these are not on the same disk drives. They will help you recover data if there is a loss.
November 10, 2008 at 3:46 am
hi,
there are many different scenarios to backup a database.
be sure that recovery model is set to full backup (if not, you can change this in the properties of the database at options - recovery model).
then you can do a full backup every night and several differential backups during the day (a differential backup stores every information since the last full backup) and in between the diffs transaction logs (only store the ldf file with open transactions).
to restore: full, only last diff and all transaction logs since last diff.
but as mentioned before, it is necessary to read something about it because the backup scario depends on the size of your database, the information, the interval of new or changing data....
other possibility if there are only some new entries in one table: you can store a history with a trigger on insert in another database..if it is only one tabel..
good luck,
sue
Susanne
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply