question

Rod avatar image
Rod asked

Why aren't all my user databases logs being backed up?

I've got a maintenance job set up in SQL Server to backup all of my user databases and logs. Recently I downloaded and created an instance of the AdventureWorks database. The database is being backed up, but the logs aren't. Why is that? I would have thought that, because it is a user database, it would automatically be picked up, but it doesn't look that way.
sql-server-2005backup
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
Please check to see what recovery model is set for the database. I am guessing it will be set to SIMPLE, meaning no transaction log backups can be created (uncomment the last line of code to find out just about the Adventureworks database: SELECT name, recovery_model_desc FROM sys.databases D -- WHERE name = 'Adventureworks'
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

But, do you really need the FULL recovery mode on the sample Adventureworks database? I suggest you to read at least [Choosing the Recovery Model for a Database][1] on MSDN. When you backup a database without log in the SIMPLE recovery mode, it doesn't mean, that the database is not backed up correctly. [1]: http://msdn.microsoft.com/en-us/library/ms175987.aspx
4 Likes 4 ·
I bet, that the Adventureworks is in SIMPLE recovery mode, as it is a default recovery mode, when the Avendureworks database is being created. If if the recovery mode was not altered, here is the next command in the AdventureWorks script after the CREATE DATABASE. ALTER DATABASE [AdventureWorks] SET RECOVERY SIMPLE, ANSI_NULLS ON, ANSI_PADDING ON, ANSI_WARNINGS ON, ARITHABORT ON, CONCAT_NULL_YIELDS_NULL ON, QUOTED_IDENTIFIER ON, NUMERIC_ROUNDABORT OFF, PAGE_VERIFY CHECKSUM, ALLOW_SNAPSHOT_ISOLATION OFF; GO
0 Likes 0 ·
Bingo, that was it. The recovery model was set to Simple. I set it to Full. Thank you.
0 Likes 0 ·
KenJ avatar image
KenJ answered
Only databases using the FULL or Bulk-Logged recovery models get transaction log backups. Is your copy of AdventureWorks using the SIMPLE recovery model?
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@KenJ, not only in FULL, but also in BULK recovery models the transaction log is being backed up.
3 Likes 3 ·
yep. I always overlook that one for some reason. Editing answer accordingly so nobody thinks that's the case.
0 Likes 0 ·
Tim avatar image
Tim answered
Double check the maintenance task and make sure you didn't manually select which DB's and logs to backup. If it is selected all user databases it should grab it, also check the recovery model of adventureworks.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
Grab a copy of this free book http://www.simple-talk.com/books/sql-books/brads-sure-guide-to-sql-server-maintenance-plans/ and you will get a great introduction into using Maintenance Plans (and when not to in some cases).
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Great book. Brad has lots of good information on his blog.
4 Likes 4 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.