SQL 2005 Transaction log backup maintenance plan 1

[Daveyd123]

I am running SQL 2005 SP2.

I have successfully setup a maintenance plan to backup a database every day. However, when I add a subplan to backup the transaction logs and go to choose the database...the database is not listed. I was able to choose it when doing a Full back up, but when choosing a transaction log backup, the same database is not listed.

I assume I am doing something wrong?

 

[mrdenny]

Is the database setup for full recovery? If not it may not be shown.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Daveyd123]

That was it. Thanks!

Can you tell me what the difference between a simple recovery model and a full recovery model is. SQL noob here

 

[mrdenny]

Sure.

To cover some basics no matter what recovery level your database is in transactions work the same. A command comes into the SQL Server (an insert for example). That command is written to the transaction log. Every once and a while (usually about every 30-60 seconds, but this can be higher or lower depending on the load of the SQL Server, log space, buffer space, etc.) a checkpoint occures. When a checkpoint occures all the transactions which have been commited to the log but not to the data files are flushed from the transaction log to the data files.

Where the difference happens is what is done with the data in the transaction log after the checkpoing occures.

In Simple recovery mode after the checkpoint flushes the transactions the transactions are removed from the transaction log file.

In full recovery mode the transactions are left in the transaction log until a transaction log backup occures. This allows you to do point to time recovery of the database if you need to restore. Because the transactions are flushed from the log in simple recovery mode transaction log backups are not possible, so point in time restores are not possible.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Daveyd123]

Nice. Makes perfect sense. Thanks again for all your help!

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog