Doing SQL Server backups - FULL / DIfferential and how often?

Question

Hi there,

Can anyone let me know what is the best practice for doing sql server backups i.e. FULL, differential and partial differential

I will doing this from code using SMO (c#) but anyway...

is it best to do a FULL backup every 1 month and a differential every day??

Just looking for a bit of guidance really

Any ideas?

Answer 1

We do a full backup once a week on the weekend, and then differential backups every night for the other 6 days.

Answer 2

Your needs and transaction level will dictate this (the more transactions between full backups, the larger the differentials will be, obviously). Where I used to work, we did full database backups every night, but for larger databases this would be a little too oppressive, I would imagine. I think it's going to depend entirely on the size of your database and what your transaction load is.

Answer 3

I wanted to add that no matter what decision you make please get in the habit of testing your backups. You should periodically test your full, differential and transaction log backups on a standby server or a development server. Obviously you don't need to test every backup but make it a routine.

Answer 4

I wanted to thank everyone for there comments..

i am going to do a full backup once a week and then differentials every night....

I may even move full to 1 month..