Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
3  
Just to check. Are you reorganizing then rebuilding the same indexes then updating all statistics? If so that is a waste of time –  Martin Smith Nov 12 '11 at 18:11
4  
And you definitely should not be including the shrink database task at all as part of a standard maintenance plan. –  Martin Smith Nov 12 '11 at 18:55
2  
Agree with @MartinSmith and his points. A bit of a history of why though --> 1.) Reorganizing and Rebuilding indexes are redundant tasks - if you are rebuilding an index, no need to reorganize it.. 2.) Shrinking a database is generally a bad practice to do on production databases (more on why - straightpathsql.com/archives/2009/01/…) I suggest looking into Maintenance Plans more on SQL Server Books Online (The built in help with SQL Server or online - msdn.microsoft.com/en-us/library/ms187658.aspx) –  Mike Walsh Nov 13 '11 at 0:48
2  
Time to step back from the keyboard and pick up a book. You're causing more harm than good by clicking through a wizard without understanding the implications of what you're doing. –  Mark Storey-Smith Nov 13 '11 at 0:49
add comment

migrated from stackoverflow.com Nov 12 '11 at 18:32

This question came from our site for professional and enthusiast programmers.

1 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.