Sql-server – Applying Transaction Logs to an existing Database

restoresql servertransaction-log

I am wondering if this possible, I can not seem to apply another transaction log.

I have restored the Full database backup, the differential backup and my last Transaction Log WITH RECOVERY. If later on I need to apply the next Transaction Log in sequence to it, what do I need to do?

I tried applying the Transaction Log and it states:

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

For each time I need to add a new Transaction Log, do I have to go through the steps of restoring the backup database, restoring the differential database and all transaction logs again?

Best Answer

You have to start over and restore full and diffs with no recovery and then the tlogs with stand by option. This will allow you to restore tlogs later as well.

You can configure logshipping with option of stand by, so you can read from the secondary database.

There will be a .tuf (transaction undo file) file created when you restore tlogs with standby option.

For manually restoring tlogs use restore gene or you can just use logshipping for automated restores.

Related Solutions

Sql-server – Information on a Database that was restored with NO RECOVERY

I'm not sure if I fully understand your question. First you say you want to find the last restore made to a certain database. You can find all relevant information (databases, files, lsns, times) on these restores through

SELECT *
FROM [msdb].[dbo].[restorehistory] r
INNER JOIN [msdb].[dbo].[backupset] b 
ON b.backup_set_id = r.backup_set_id

But I'm not sure what you mean with "finding the rest of the needed backups". If you want to look at the backups made in an instance you can do this through

SELECT * FROM [msdb].[dbo].[backupset]

Hope that helps.

Sql-server – The purpose of transaction log in Restore First

Restoring a full + differential backup restores you to the point in time that the differential backup started. If you just took the differential this second and nothing has happened in the database since it started, then yes, that is the latest state. But it's quite unlikely that you will have a disaster the minute you take a differential backup and that nothing has happened in the meantime.

Which means you can't restore to the point in time right before the disaster - everything that happened between the differential and <now> is gone. If you take a differential every hour, this means that disaster could strike at hh:59:59.9999999 and then you'd have lost almost an hour's worth of data.

This is the whole point of the full recovery model - to minimize data loss. If you're not going to bother taking log backups, then switch to simple.

If you take transaction log backups in between differentials (or just don't bother with differentials, since all they do is reduce the number of transaction log backups you need to restore in the event of a disaster), then your maximum potential data loss is your log backup schedule. Because with log backups, you can apply the following during a restore:

restore latest full backup b
restore latest differential backup d taken after b
restore n log backups taken after d up to point in time t

You need to decide what your tolerance is for data loss in order to determine how often you should be running transaction log backups. If you don't know what your tolerance is, you need to discuss this with management / stakeholders.

Could you just take a differential every minute? Or take full backups every five minutes? Sure. But buy some bigger disks.

Best Answer

Related Solutions

Sql-server – Information on a Database that was restored with NO RECOVERY

Sql-server – The purpose of transaction log in Restore First

Related Question