SQLTeam.com | Weblogs | Forums

How to choose a best recovery models in SQL Server?

sql2008r2

#1

I want to know which recovery model is best for Backup purpose, because there is less memory space on my system, So i want to manage my SQL Server database?

What to do? any suggestion?


#2

Recovery model is about "recovering" your data, so you should start from that position, and not from how much memory / disk space you have :smile:

If you lost your database how would you get it back?

Would a backup from "last night" be OK? Even if you lost the database at 17:00 after a full day's work?

If not then you need to use FULL Recovery Model and backup the Log File every 10 minutes (or however often you COULD afford to lose the data, but backing it up more often will keep the Log File smaller, which is important if you have limited disk space.

If you can afford to lose all the changes SINCE the last full backup then SIMPLE Recovery Model will do. That will keep the Log File small.

Since SQL2008 you can use COMPRESS in the backup command (before it was only available in Enterprise version). This will reduce the size of your backups a lot - probably by 80% - (and also reduce the time it takes to create the backup - and when you need to restore it). It is possible to set COMPRESSed backups as the default, so you could make that change, and no other, to have that benefit.


#3

Thanks kristen for clearing my confusion about recovery and backup. :dart:


#4

You may checkout this blog.
They have answered it very well
https://technet.microsoft.com/en-us/library/ms175987%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396