June 13, 2010 at 8:01 am
i want to change the recovery model from FULL to BULK LOG recovey model for bulk operation and then again i want to change to FULL recovery model.
what are the steps i have to take to prevent data loss for the above process.
June 13, 2010 at 8:36 am
There's no chance of data loss from changing recovery models.
What you probably want to do is to take a log backup right after going back to full recovery, as tail-log backups are not permitted when there are bulk operations in the log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 13, 2010 at 11:54 am
Gail is absolutely correct. There is no chance of data loss.
June 14, 2010 at 5:10 am
vijay82 (6/13/2010)
i want to change the recovery model from FULL to BULK LOG recovey model for bulk operation and then again i want to change to FULL recovery model.what are the steps i have to take to prevent data loss for the above process.
Backup the log immediately before switching from FULL to BULK_LOGGED.
Backup the log again immediately after switching from BULK_LOGGED to FULL.
This gives you the best chance of recovery if bad things happen at any point.
Bear in mind that point-in-time restore is not available from the time you switch to the BULK_LOGGED recovery model, until the log backup after the switch back to FULL has completed.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 14, 2010 at 5:18 am
There are no chances of the data loss provided you take a log backup every time you switch the recovery model ( from Full to Bulk Logged and Vice Versa).
Fore more security, After you change the recovery model from Bulk logged to Full, Take a Full Backup to be on the SAFEST Side.
June 14, 2010 at 5:34 am
chetanr.jain (6/14/2010)
Fore more security, After you change the recovery model from Bulk logged to Full, Take a Full Backup to be on the SAFEST Side.
There's no special reason to take a full database backup after switching from BULK_LOGGED to FULL.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply