Is the Bulk-Logged recovery model in SQL Server ever preferable to Simple if point-in-time restore is never needed? I have read many articles about transaction log models, but cannot find a clear answer to this. The database in question has a lot of activity during nightly loading, but the data doesn't change after that. I am doing a full backup daily. Wouldn't Simple be the best choice here, while also potentially using less storage?
SQL Server – Is Bulk-Logged Recovery Model Good Without Point-in-Time Restore?
backupperformancerecoverysql server
Related Question
- Sql-server – Sql Server 2008 R2: Simple recovery model with transaction log backups
- Sql-server – Is it good idea to repair sql file
- Sql-server – logging in Bulk logged recovery VS Full recovery model
- SQL Server – Database in SIMPLE Recovery Model Full Using SSIS Bulk Insert
- Sql-server – SQL recovery model for ADFS deployment
- SQL Server Recovery – What is the Technical Meaning of Point in Time Recovery?
- Sql-server – SQL Server Replication and Recovery Model
- SQL Server Full Recovery Model – Tablock Inserts Logging
Best Answer
No, use simple
Bulk-Logged is a special case of FULL, not an enhanced version of SIMPLE.
Bulk logged with minimally logged operations also requires the data files to be present. Full does not because each row changes is logged. This introduces another complexity.
So use SIMPLE if you can restore to the last full database backup
If you have a nightly load, then using DIFF backups is probably enough to give you extra restore capabilities.
Note, I don't think I've ever used bulk logged recovery since SQL Server 6.5