Sql-server – Are non ‘copy-only’ full backups safe to recover from

backupsql server

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?

Best Answer

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