READ ONLY vs. STANDBY - need clarification

[k108]

Hi,

I have been using EM to practice database restores.

One of the options available under "Recovery Completion State" is: "Leave database read-only and able to restore additional transaction logs".

Is this the SQL equivalent of:

RESTORE DATABASE mydb
blah blah
STANDBY = standby name

And if so, how does this differ from just plain old "READ-ONLY"? That is, a standby database is always read-only, but is a read-only database always standby database?

Thanks

 

[SQLBill]

Refer to the BOL for more information, but:

A standby server is a second server which is kept current by applying transaction log backups from the primary server. This server is used as a backup server in case the primary server fails. While it is being used as a standby server, it can ALSO be used for queries (read only), but inserts/updates/deletes can not be done.

Putting a server in read only mode can be done on the primary server. Why would you do this? You are in the middle of restoring a database and your boss says 'We need this report NOW!'. You can put it in read only and make the report, then finish restoring the database. Another use is for a datawarehouse or archive server, you don't want the data to be changed but users need to do reports (selects) from it.

-SQLBill

Posting advice: FAQ481-4875

 

[k108]

Yes, I did some more research on BOL, and it DOES appear that STANDBY is the same thing as READ-ONLY. That is, restoring a standby database makes it read only, so that you can query the db between restores (such as 2 log file restores). Read-only is not a thing in and of itself...

That is, there is no such thing as a READ-ONLY option, per se, in the RESTORE DATABASE syntax.

The next closest thing to read only is NO RECOVERY, but that does NOT allow you to query the db between restore operations (such as 2 log file restores).

 

[SQLBill]

To answer your actual question....

A standby server is read-only.

A read only server may not be a standby server.

Another reason to make your primary server read-only: let's say business rules require you to ensure the state of the data when you restore a database. If you restore it fully, users can change or add data before you can do your checks. Restoring to read only first allows you to check the integrity of the data and then fully restore it.

-SQLBill

Posting advice: FAQ481-4875

 

[k108]

A read only server may not be a standby server.

Yes, you are correct. I just found out how you would do this:

sp_configure read_only

Thanks