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.
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'
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
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
Only databases using the FULL or Bulk-Logged recovery models get transaction log backups. Is your copy of AdventureWorks using the SIMPLE recovery model?
@KenJ, not only in FULL, but also in BULK recovery models the transaction log is being backed up.
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.
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).
