Jump to content
Sburk

Switching from Full to Simple recovery mode. Log Backups?

Recommended Posts

I have a database that is currently running in Full recovery mode and a SQL log file that is 20GB, which is obviously too large.

 

I want to switch the database from Full recovery mode to Simple recovery mode.

 

What steps should I take through EMC retrospect to prepare for this switch? I need the log to commit the changes to the database. I'm a bit concerned that Retrospect does not do this with a 'Full Database Backup' already.

 

Please let me know. Thanks!

Share this post


Link to post
Share on other sites

Retrospect uses standard Microsoft commands for the backup of SQL databases. Retrospect sends these commands to the SQL Server, it is then up to the SQL Server to do the "right thing" with those commands. See screenshot.

Share this post


Link to post
Share on other sites

I understand that EMC sends commands to the SQL server. However, we are currently in full recovery mode and the Full Backup option does not truncate the log. Do you know if this is by design? If you run a 'Full Backup' directly from the SQL Server agent (not through Retrospect), it would truncate the log.

 

So, I'm assuming that I will need to run a Full Backup and then run a Log Backup against each database in order for the log to truncate and commit changes to the database. Is this a correct assumption or is there a workaround?...or is this flat out not working correctly for me?

 

Thanks!

Share this post


Link to post
Share on other sites

Retrospect itself does not truncate logs. Retrospect sends the backup command and it is then up to SQL to handle the logs correctly. If the Logs do not truncate then it is due to an error on the SQL side or a limitation of the specific backup API being used by Microsoft while in the recovery mode..

Share this post


Link to post
Share on other sites

So you are saying that the logs SHOULD truncate if I run a Full Backup from Retrospect? I don't know what backup commands retrospect is sending to the sql agent, so I don't know if it's sending a truncate command or not.

Share this post


Link to post
Share on other sites

I believe full backups normally normally truncate logs but I don't know what SQL does in recovery mode. Once again, Retrospect does not control this, SQL does.

Share this post


Link to post
Share on other sites

In my experience:

 

-- Full backup does NOT truncate the logs

-- Differential does NOT truncate the logs

-- Log backup no truncate does NOT truncate the logs

-- Log backup DOES truncate the logs

 

We run regular LOG backups every hour, and any transactions that are completed (and thus written to the database) are truncated after the backup.

 

We run regular FULL backups every 12 hours. This does NOT truncate any log data; it simply backs up the database.

 

Check out this thread:

 

http://forums.dantz.com/showtopic.php?tid/27109/

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×