3

I've recently realized the difference between a full backup and a 'Copy-only' full backup and I'm wondering if the backups I'm taking on a weekly and daily basis are safe to recover from.

This article suggests that differential backups can only be traced back to a 'parent' (if that is the correct word) full backup. To me this sounds like a DB with FULL recovery mode allows for a point-in-time recovery to the last full backup?

In any case. I'm currently taking a full backup on a daily basis and no differential backups. Some of the databases being backed up have a recovery mode of simple, some full and some are bulked-logged. However, there is no need to do point in time restore on any of them at this point (These databases are updated once a day via vendor dumps). I'm also not taking any transaction log backups of the full-recovery mode databases for now (because I don't know how to do this automatically yet I'm watching them manually until they start growing faster than they currently do).

Am I right in thinking that a database can be restored from a full-backup at the time that full-backup was taken, regardless of the recovery model and regardless if there is a log backup or not?

Improve this question

1 Answer 1

Reset to default
2

However, there is no need to do point in time restore on any of them at this point.

If this is the case you MUST put database in simple recovery mode and this will take care of the transaction logs. When database is in simple recovery mode you can take two types of backup 1. Full 2. Differential

Depending on how much data you can loose you can take daily full backup and then may be differential backup twice or 4 times a day just to make sure you loose as less data as possible. When you restore from backup you would have to restore the latest full backup and the latest differential backup taken after the latest full backup. You cannot take transaction log backup in simple recovery model SQL Server will itself manage your transaction log truncation.

Please read Understanding Logging and Recovery in SQL Server

Am I right in thinking that a database can be restored from a full-backup at the time that full-backup was taken

Yes you are absolutely correct. Please read Understanding Backups in SQL Server

Improve this answer
5
  • "you MUST put database in simple recovery mode and this will take care of the transaction logs". I'm using the same maintenance task to take full backups of SIMIPLE, BULK_LOGGED and FULL recovery mode databases. Are you saying that I would be unable to restore databases on the FULL recovery model in my case?
    – Zach Smith
    Commented Jan 30, 2017 at 8:51
  • As I mentioned, I'm truncating the transaction logs manually when required via taking log backups
    – Zach Smith
    Commented Jan 30, 2017 at 8:52
  • @ZachSmith Smith I used word must because you do not need point in time recovery, if this is case it is better to put DB in simple recovery and SQL Server will take care or transaction logs or else you would have to. Backup does not depends on recovery model. Recovery model control recovery and what is logged in transaction log. Am I making sense to you ?
    – Shanky
    Commented Jan 30, 2017 at 9:07
  • Yes, thank you. In the future full will be required. But for now the db is on full but only requires a daily restore point
    – Zach Smith
    Commented Jan 30, 2017 at 9:12
  • If your database is in full recovery model you also have to schedule frequent transaction log backup other wise log will grow and occupy space. And if point in time recovery is not required and data loss can is affordable put DB in simple recovery in this case SQL Server will manage transactions logs and you dont need to take log backup as a fact you cannot in simple recovery
    – Shanky
    Commented Jan 30, 2017 at 9:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.