June 26, 2007 at 2:37 pm
Hello All
If I open SQL Ent Manager, I select a database to back from the right click menu, and to all tasks. Once the backup database opens, I am not able to select "Transaction Logs" or "File and FileGroup" they are both inactive. What would be causing this? The database is set as 'Simple' Recovery Mode.
Thanks
Andrew
June 26, 2007 at 2:45 pm
In the simple recovery mode entries to the log are not recorded. So backing up the transaction log won't really work. As for file/filegroup backups I would assume that if you're not using multiple filegroups you wouldn't have that option either.
I don't have a copy of enterprise manager in front of me so I can't check, but that's what I'd assume...
June 26, 2007 at 5:54 pm
In SIMPLE recovery mode, the transaction log gets checkpointed and SQL Server will 're-use' the space based on the checkpointing.
-SQLBill
June 26, 2007 at 11:45 pm
Andrew,
You will need to ru your database in bulk-logged or full recovery model to backup the transactional log file. With simple model it will not be possible as the transaction is commited and checkpoint issued once it completes.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 27, 2007 at 3:21 am
Just checked the file or filegroup backup is enabled only with bulk-logged or full recovery model.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 27, 2007 at 7:50 am
And you should understand that any database in SIMPLE recovery mode can only be recovered back to the last full backup. So if you did a full backup on Sunday, and something goes terribly wrong with it today, you restore Sunday's backup and lose all the work between Sunday and today.
June 27, 2007 at 10:42 am
Andrew:
Arguments can be made that running a database in Simple Recovery Model is OK, I don't agree with that overall because it limits the recovery techniques that can be applied if things go bad. As has been pointed out by Homebrew, if something bad happens, you only have your last full backup as a recovery point.
I would suggest changing the recovery model to Full, this gives you the ability to back up the transaction log whenever you need or want to. In my setup, depending on the database, I may have transaction log backups every 10 or 15 minutes.
Once you change it, immediately do a full transaction log backup, followed by a full database backup.
Remember that the database backup does a transaction log backup at the end of anything that changed while the backup was running, so that backup file is complete unto itself.
Also remember to back up your master, msdb, and model databases in case you lose the entire server to a catastrophe.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
June 27, 2007 at 12:04 pm
We run many of our "non-critical" databases in SIMPLE mode, but our production databases are definately in FULL mode. Every database has a full backup every day. You should be clear on the differences, and if you can't accept data loss for your SIMPLE databases, then they should be set to full with t-log backups between full backups.
June 27, 2007 at 3:42 pm
Thank You everyone.
Just about the time that I posted this yesterday. I found that. I greatly appreciate everyone getting back to me. Everyone that posted a reply, was absolutely correct, that is exactly what the problem was. I have since modified the database and I can now create a backup of the logs.
Thanks
Andrew SQLDBA
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply