To simle recover or full recover, that is the question...

wyrd

wyrd

Well-known member
Joined
Aug 23, 2002
Messages
1,408
Location
California
Okie dokie, I went ahead and started to toy with backups, but I found that backing up a log requires Full Recovery model. Hrm. Well, Im backing up a database for a small business (fictitious as this is just me messing around, but thats beside the point, lets assume I really am) and wanted to know if backing up the log is should be something I do, or is it not to important with a small business thats info is probably updated only once a day at most?

As Im understanding this, Simple Recovery model allows just database backup, and database recovery is to just recover data to the point of last backup of the db. Full Recovery model allows db and log backup, which includes Simple Recovery but also allows log recovery, which can recover data from a certain point time.

If I should be using Full Recovery model, how do I set this for the db Im using? Please keep in mind that Im using MSDE and Server Explorer that comes with .NET Professional, so Ill probably have to do this through osql.

Thanks in advance.
 
Generally speaking if the database undergoes few updates then the log itself will remain fairly small and as such the increased disk space is minimal.

In a production environment backing up the logs can give a much more recoverable system.

Take the scenario where logs are kept seperate from data (ideally seperate physical disks or raid arrays)

OS on C:
Data on D:
Logs on E:

if the data is backed up at midnight (Full backup) all data is backed up and logs are purged.

work progresses through the day (no further backups) and a disk fails at 15:00

if the disk that fails holds the logs then all the data is still safe on D:

if the disk that fails is the Data then last nights backup holds everything up to the start of the day and the Logs (E: ) hold all changes - either scenario results in minimal to no data loss.

I cant remember the exact command of the top of my head to change this but it uses the sp_dboption procedure.

Hope that helps a bit.

<edit> the E: bit was turned into a smilie </edit>
 
Last edited by a moderator:
Thanks for the response. My question wasnt really what log backups vs db backups were (as I already knew the difference) but whether or not log backups were needed for a small business app.

As for changing the db model, I found the answer (I didnt see any option for this under sp_dboption);
ALTER DATABASE dbname SET RECOVERY FULL
 
Teach me to read the question properly ;) And you were right about the sp_dboption thing (bad day and I was away from anything with SQL 7 or better on it - did have SQL 6.5 handy though)

I would tend to always use full backups every night (if possible) as this makes restores reasonably easy. As to log backups - depends on how many updates occur - if as in your example one or two a day probably not.
 
Back
Top