Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
trusted4u
Posting Yak Master
109 Posts |
 Posted - 2005-01-27 : 04:02:19
|
| Hi,How to take transaction log backup when the db recovery model is set to simple. When I trying to take backup it gives me the foll error : "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE."Thanks- Marjo. |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2005-01-27 : 04:27:58
|
| BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.Did you think it was lying ?Damian |
 |
|
|
trusted4u
Posting Yak Master
109 Posts |
Posted - 2005-01-27 : 09:15:30
|
| Thanks Merkin,Its my mistake, I should have wrote down my actual doubt. After taking the db backup when I try to insert bulk records it gives me an error something like this "log file full. Backup log file to free some space". So I tried using Backup log but it failed with the recovery model error. Then I changed the recovery model to full and then took the transaction log backup and everything went fine. But still I have some doubts i.e. First - if I don't change the recovery model from simple to full and take database complete backup from EM then does it free transaction log or it is kept intact ?Second question - do I have to always keep changing the recovery model whenever I want to free the log file ?Thanks- Marjo. |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2005-01-27 : 11:47:03
|
| In the simple backup mode, the log gets truncated after each transaction. The log still gets used, however. This meaqns that a very large transaction (e.g., Bulk Insert) can exhaust the log file size within ine transaction. Does that sound like what's happening?If so, you can increase the size of the log file manually or allow the log file to grow automatically. It is possible, also, that there is not enough physical space left on the drive(s) that the log file exists on.HTH=================================================================Our elections are free, it's in the results where eventually we pay. -Bill Stern, sports announcer (1907-1971) |
|
|
|
|
|
|
Backing Up Transaction Log