November 19, 2003 at 1:10 pm
I've been trying to backup the transaction log on my DB and keep getting the following error.
Executed as user: NT AUTHORITY\SYSTEM. The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE. [SQLSTATE 42000] (Error 4208) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). NOTE: The step was retried the requested number of times (1) without succeeding. The step failed.
My understanding of the SIMPLE recovery model is that data can be recovered only to the last basckup. I can't even perform a backup because t says the recovery model is SIMPLE. Any support would be greatly appreciated.
Shane
November 19, 2003 at 1:23 pm
You can't perform log backup in simple recovery mode. Completed transactions in transaction log will be truncated at checkpoint by system automatically. You can only recover the database from last full database or plus the differential backup in simple recovery mode. Set database to full/bulk_logged will allow you to backup the transaction log. See BOL for details.
November 19, 2003 at 1:30 pm
My understanding of the SIMPLE recovery model is that data can be recovered only to the last basckup.
That's partially correct...you are missing one word:
My understanding of the SIMPLE recovery model is that data can be recovered only to the last FULL backup.
-SQLBill
November 24, 2003 at 10:15 am
Use SIMPLE if going back to the last FULL backup is good enough (usually used in test or light usage environments).
Use FULL if you want the security of transaction logs (usually used in Production environments). You should also run trans log backups so you have them in case of disaster
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply