Strategy on Standby restore option for hourly log shipping file

Question

I am trying to come up with a strategy of keeping up with daily database update process using "standby" restore mode.

I am getting 24 log shipping files (for the previous day's each hour transaction log files) from a third party's FTP site. I would update these 24 files on a nightly run. I get these files originally in SQB file formats, and then I have a tool and script to convert these SQB files into BAK file format.

Now, I am trying to come up with a strategy of continuous back up plan.

Database does not have to be updated or modified, but just to be read. Do I restore each transactional log file as "standby" all the time and just leave them as "standby" mode?

I am planning to create a separate database to retrieve only necessary data from some tables from this "read-only" database.

I have one more question. If I accidentally run a script to restore this database as "NoRecovery or Recovery" mode, is there way to change the mode back to "Standby" by running a script or do I have to restore full bak file again as "Standby" (do the whole process again)?

Answer 1

Do I restore each transactional log file as "standby" all the time and just leave them as "standby" mode?

Either will work, but it is faster to apply all the logs except the last one using NORECOVERY. The last one applied uses STANDBY, making the database available for read-only access. SQL Server has to do extra work to make the database available for reading in a transactionally-consistent state. It then has to undo that work (using the standby file) when you ask it to apply the next log backup. In short, specifying STANDBY unnecessarily can really slow things down because you end up doing and undoing part of the recovery process (see link by Paul Randal below).

In your case, that would mean applying 23 transaction log backups with NORECOVERY, then the 24th with STANDBY. Next day, you do the same thing.

Note that you will need to ensure that nothing is connected to the database in standby mode when you go to start the next restore sequence. That ought not be an issue in your case, since you control all access to the database. If you do need to forcibly disconnect other users:

ALTER DATABASE DbName 
SET SINGLE_USER 
WITH ROLLBACK IMMEDIATE;

Don't forget to make the database available to other users again later with:

ALTER DATABASE DbName 
SET MULTI_USER;

If I accidentally run a script to restore this database as "NoRecovery or Recovery" mode, is there way to change the mode back to "Standby"

You can change from NORECOVERY to STANDBY and vice-versa e.g.:

-- From norecovery to standby
RESTORE DATABASE DbName
WITH STANDBY = N'C:\StandbyFiles\DbName.sby';

-- From standby to norecovery
RESTORE DATABASE DbName
WITH NORECOVERY;

You do not need to change from STANDBY to NORECOVERY to apply the next set of log backups.

Once the database is recovered (WITH RECOVERY), you cannot go back, and must start the whole restore sequence again. You would never do that in your scenario.

The standby file will be automatically deleted (or reused) by the next restore step, but SQL Server does not lock the file while it is not actively in use by a restore command. Treat the file with the same care you would any other database file - if it is lost, so is the database.

Recommend reading:

• Reporting From a Log Shipping Secondary in STANDBY mode by Kendra Little.
• Log shipping performance problems by Paul Randal