Sql-server – Restoring .trn files on a regular basis to a backed-up database

log-shippingsql servertransaction-log

We have been given a .bak file of a database from our vendor on a completely different network. They are placing their .trn files every 15 minutes on a sftp for us to pickup and write to the .bak file they sent us. This way we will have data up-to-date every 15 minutes or so.

What I have done is restored their backup WITH NORECOVERY. Then I restored all 800 .trn files WITH NORECOVERY, setting the last restore to WITH RECOVERY. Now that it is out of NO RECOVERY we can read from the DB.

Now that we are up-to-date, what is the process for restoring the new .trn files that they send us?

I apologize for the ignorance, I am new to the DBA game.

Best Answer

You should only issue WITH RECOVERY when it's the last backup to restore or when you are sure that is the point where you want to go. After that there's no way to restore any more backups.

Now you will need to restore everything again to keep the same log chain.

If you need to "open" the db without break the log chain, you must use WITH STANDBY = 'path/file'. This will open the DB in READ ONLY mode and when you need to restore more files, SQL Server will use the 'path/file' you create to allow the DB to continue restore the log chain.

You may take a look to this option here: https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-arguments-transact-sql?view=sql-server-2017

Related Solutions

Sql-server – Error restoring SQL differential database files

To check the LSN number from the backup file do:

restore headeronly from disk='g:\backups\name_of_backup_file.bak'

The result set will have a column which will show you the last LSN number. As gbn said, to restore from a diff backup you need a full + last diff backup from the same backup set.

Sql-server – Sql Server 2008 R2: Simple recovery model with transaction log backups

Seeing that the recovery model is set to simple and msdn states that the simple recovery model does not support point-in-time recovery - Does this mean that I won't be able to use my transaction log backups to restore the database in a disaster to an hour before it happened?

Even taking a transaction log backup is not supported for databases using the SIMPLE recovery model. This is a restriction of the database engine based on how this recovery model works, and the recovery features it doesn't support, as you mentioned.

A transaction log backup maintenance plan task automatically skips databases in SIMPLE recovery to avoid causing errors.

Which backup should be done first, the database backup or the transaction log backups? Articles that I'm busy reading say I should do the database backup first and then the transaction log backup else I will get maintenance plan errors, but I'm currently first backing up my transaction logs and then data databases and I'm not getting any errors.

For the reasons I mentioned above, it won't matter for databases using SIMPLE recovery, as they will be skipped by the transaction log backup task.

For databases in the other two recovery models, a full backup must exist before you start taking transaction log backups (just the first time), or you will get an error -- this is probably what the articles refer to.

Point-in-time recovery ability is normally driven by business need -- in other words, you determine how critical the data is and how much you can afford to lose, then set the appropriate recovery model to meet those needs, and finally create a backup solution.

Even though SIMPLE recovery does not support point-in-time recovery, if an hour of data loss is okay, perhaps a differential backup solution could work for you. (There are far too many variables that go into developing this kind of solution to give you a complete picture with what was provided in the question.)

Best Answer

Related Solutions

Sql-server – Error restoring SQL differential database files

Sql-server – Sql Server 2008 R2: Simple recovery model with transaction log backups

Related Question