Sql-server – Maintenance Plan produces BAK files I cannot use

sql server

I have a maintenance plan that does the following:

  1. Check Database Integrity
  2. Shrink Database
  3. Reorganize Index
  4. Rebuild Index
  5. Update Statistics
  6. Clean Up History
  7. Backup Database
  8. Maintenance Cleanup Task

It runs on schedule once a day and I have now tried to re-install a BAK on my local machine and I get the following error message:

The Log or differential backup cannot be restored because no files are
ready to rollforward.

I barely understand the concept of how Transactional Logs are handle by SQL. In my mind, when a transaction runs and completes, not sure what "trace" leaves on the log file but in min mind that should just be a trace, not operational data that the MDF depends on.

From what I read …it seems I'm wrong 🙂

But going back to my initial problem, I need to create a daily LOG that is USABLE.

I thought using a Maintenance Plan was the way.

Obviously not !!!

=========
EDITED:
I removed the unnecessary blocks …but does that solve my original problem?

Best Answer

Maintenance plans are certainly one way to perform database backups and a multitude of customers use them with success each day. My guess here, looking at the inclusion of the shrink job and the reorganize followed by rebuild that the maintenance plan was just quickly created without understanding the tasks.

When you say you have a "backup database" task - what type of backup is it? My assumption in this answer is that you are taking Full Backups and then Taking Differentials and possibly Log backups.

The problem likely isn't your backup, per se (though I suggest you spend some time understanding recovery models and the backup command at books online as you are likely not creating your backups in the most optimal order for your recovery intent).

I believe you are likely having an issue because you are restoring in this order:

1.) Restore the full database backup (.bak) first keeping all the defaults 2.) Attempt to restore the differential or the log backup.

Or you are just attempting to restore the log or differential on top of an existing database.

If this is the case - the issue is understanding how recovery models work and the proper restore sequence. (One more link to read - about transaction log restores) but in a nutshell:

If you plan on restoring any differentials or log backups, you have to first restore the full backup AND you must specify "WITH NORECOVERY" when restoring that full bakup. If you keep the default (WITH RECOVERY) then SQL Server performs the necessary recovery processes to make your database operational (basically rolls forward transactions that were done and consistent at the time of the backup, rolls back ones that weren't). If you want to restore additional backups (only the latest differential or each log file between the last full or differential backup and the time you want to restore to) then you must specify WITH NORECOVERY for each restore until the last one where you specify WITH RECOVERY finally.

I'd definitely spend some significant time with the Microsoft Books Online links I sent here and click on the links they refer to as well to best understand backup and recovery.

Related Question