SQL Server has 3 recovery model types which control types of backups so let’s discuss in more details all recovery types
when you do these operation sequential
right click any database—> properties –> select Pages –> options
you will see the dialog bellow
as you see in fig above you have 3 types
the relationship between
backup types(Rows Headers) and
Recovery Models(Columns Header) are
| |
Full |
Bulk-logged |
Simple |
| Full Backup |
Yes |
Yes |
Yes |
| Differential Backup |
Yes |
Yes |
Yes |
| Transaction Backup |
Yes |
Yes |
No |
it’s very clear that you can’t make trasaction log backup in simple mode.
so what is the Differential between Full mode and Bulk-Logged mode?
Full mode log any transaction in Transaction log file but Bulk-logged
can’t log huge transaction to log file like bulk insert or create index
in huge tables
you can use Bulk logged for more performance to insert bulk data to database because in this mode you will not log in transaction log.
you can use Full mode to log any small things to transaction log file to feel safe when to return to end point of disaster
