Recovery Model + Database Maintenance Plan

[Laeg]

Could somebody explain the concepts of these two and the difference between. If you have a recovery model set why would you need a Database Maintenance Plan? For example if I set the recovery model to simple why would I have a database maintenance plan that writes a bak file to an archive directory?

Thanks in advance for replies

 

[JayKusch]

Because the backup is of the database's data file.

If your database were to become corrupt, you could restore the last full backup.

Here is the deal.

If you have the DB in FULL and you do TLog backups every hour, if you were to have a DB go bad, you could restore the FULL backup and then restore the hourly TLogs you have to get you closer to the point in time when the DB went south.

So if you do a FULL at 2am and you DB dies at 8p that evening, you could restore the 2am FULL and the TLog backups from 3a thru 7p.

You would now have only lost 1 hours worth of transactions.

IF your DB was in Simple mode and the same senerio as above happened, you would restore the 2am FULL and nothing more ... all transaction from 2a until the 8p crash are lost.

Hope that helps

Thanks

J. Kusch

 

[SQLWilts]

J.
Actually, you can restore right up to a transaction with point in time recovery using the stopat syntax.
Have a google, and practice it - its a useful skill to learn.

 

[SQLWilts]

J.
Actually, you can restore right up to a transaction with point in time recovery using the stopat syntax.
Have a google, and practice it - it will come in useful - I promise