Restore to read-only mode

Hi all,

EM has the following restore option:

"Leave database read-only and able to restore additional transaction logs."

Using this, I can restore to a named transaction, then run a SELECT statement that outputs the current state of my data to a file while the db is still in load mode.

What is the T-SQL version of this EM property, if it exists. I'm using:

restore log MyDatabase from MyDiskDevice with file=1, norecovery,
stopatmark = 'mymark'

I can't find a read-only attribute in BOL though to use with the RESTORE statement. Can I do this in script?

Thanks,

BObALTER DATABASE <dbname> SET READ_ONLY WITH ROLLBACK IMMEDIATE|||Thanks Brett. I'll give it a try.

Bob

Restore Question

Hi,
When I restore the database, there is an option "Leave database read-only
and able to restore additional transaction logs" I understand exactly what
it means but What is that undo file for?
How and when I use it?
Thanks
Ed"Ed" schrieb:
> Hi,
> When I restore the database, there is an option "Leave database read-onl
y
> and able to restore additional transaction logs" I understand exactly wha
t
> it means but What is that undo file for?
> How and when I use it?
> Thanks
> Ed
For the uncommitted transactions in the moments of TA log backups.
A TA log backup will backup the uncommitted transactions as well as the
committed ones. The ends will usually find their beginnings at the time of
restore/recovery. As a standby server needs to somewhat recover after every
new TA log backup (otherwise it would not be READ-only), it would have to
roll back all open transactions and loose them. To avoid this data loss they
are written into the undo file, kept there until the next TA log backup is
delivered to complete those transactions whose beginning where in the last
backup. The undo file will be overwritten each time a TA log backup is
delivered ...|||Or to phrase it differently, the undo file is there so that SQL Server can u
ndo the UNDO phase of
recovery (performed during restore).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Christian Donner" <ChristianDonner@.discussions.microsoft.com> wrote in mess
age
news:99A35889-166D-46B9-A930-D0A2096D287D@.microsoft.com...
> "Ed" schrieb:
> For the uncommitted transactions in the moments of TA log backups.
> A TA log backup will backup the uncommitted transactions as well as the
> committed ones. The ends will usually find their beginnings at the time of
> restore/recovery. As a standby server needs to somewhat recover after ever
y
> new TA log backup (otherwise it would not be READ-only), it would have to
> roll back all open transactions and loose them. To avoid this data loss th
ey
> are written into the undo file, kept there until the next TA log backup is
> delivered to complete those transactions whose beginning where in the last
> backup. The undo file will be overwritten each time a TA log backup is
> delivered ...