Database (Standby / Read Only)

Hi all,
This morning when I tried to test my restores, they all went well except for
one Database which when restored went into a "Standby / Read Only" mode, its
greyed out and I can't for the life of me get it to go back to Normal.
I have googled, searched through the two MS SQL 2005 books I have but I cant
seem to get any answer to why it happend or more importantly how to fix the
problem.
The only options I am given when I right click the Dbs is to restore the
transaction log but when I navigate to the my backup (which I restored from)
it wont show any logs. I get a " You must select a restore Source" error
message. But I am pointing to a *.trn file.
Im not a dba by any means more of a babysitter so this could be something
simple.
Could anyone shed some light please.Hello Adrian!
There are three Recovery State when you perform a restore operation.
You can choose one of them while restoring from GUI or T-SQL.
If you want to use GUI (as you said you do not know much about SQL Server,
I'll tell you this way) then go from the following path:
Open SQL Server Management Studio, connect to your instance and right click
on the Databases and click Restore Database.
Type some name to "To database:" or choose one. Click "From device" and go
find your *.bak (backup) file. (Do not forget to check "Restore" checkbox
from the backup sets listbox.
Go to Options and from the Recovery State (which is at the bottom of the
pane) be sure you select the first one (RESTORE WITH RECOVERY).
When you set this option as RESTORE WITH NORECOVERY, then your database will
be inaccessible.
When you set it as RESTORE WITH STANDBY, then your users can use this
database for reporting purposes. But they can not use UPDATE and DELETE,
they can only use SELECT against your database in STANDBY mode.
You can learn more about "Backing Up and Restoring Databases in SQL Server"
from BOL:
http://msdn2.microsoft.com/en-us/library/ms187048.aspx
Ekrem Ã?nsoy
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:88722291-F62F-4A91-96F5-91D9FCDD8EF5@.microsoft.com...
> Hi all,
> This morning when I tried to test my restores, they all went well except
> for
> one Database which when restored went into a "Standby / Read Only" mode,
> its
> greyed out and I can't for the life of me get it to go back to Normal.
> I have googled, searched through the two MS SQL 2005 books I have but I
> cant
> seem to get any answer to why it happend or more importantly how to fix
> the
> problem.
> The only options I am given when I right click the Dbs is to restore the
> transaction log but when I navigate to the my backup (which I restored
> from)
> it wont show any logs. I get a " You must select a restore Source" error
> message. But I am pointing to a *.trn file.
> Im not a dba by any means more of a babysitter so this could be something
> simple.
> Could anyone shed some light please.
>|||Hi Ekrem Ã?nsoy,
You were spot on! Thank you very much, I was getting worried for a while :)
Have a nice day!
"Ekrem Ã?nsoy" wrote:
> Hello Adrian!
>
> There are three Recovery State when you perform a restore operation.
> You can choose one of them while restoring from GUI or T-SQL.
> If you want to use GUI (as you said you do not know much about SQL Server,
> I'll tell you this way) then go from the following path:
> Open SQL Server Management Studio, connect to your instance and right click
> on the Databases and click Restore Database.
> Type some name to "To database:" or choose one. Click "From device" and go
> find your *.bak (backup) file. (Do not forget to check "Restore" checkbox
> from the backup sets listbox.
> Go to Options and from the Recovery State (which is at the bottom of the
> pane) be sure you select the first one (RESTORE WITH RECOVERY).
> When you set this option as RESTORE WITH NORECOVERY, then your database will
> be inaccessible.
> When you set it as RESTORE WITH STANDBY, then your users can use this
> database for reporting purposes. But they can not use UPDATE and DELETE,
> they can only use SELECT against your database in STANDBY mode.
> You can learn more about "Backing Up and Restoring Databases in SQL Server"
> from BOL:
> http://msdn2.microsoft.com/en-us/library/ms187048.aspx
>
> --
> Ekrem Ã?nsoy
>
>
> "Adrian" <Adrian@.discussions.microsoft.com> wrote in message
> news:88722291-F62F-4A91-96F5-91D9FCDD8EF5@.microsoft.com...
> > Hi all,
> >
> > This morning when I tried to test my restores, they all went well except
> > for
> > one Database which when restored went into a "Standby / Read Only" mode,
> > its
> > greyed out and I can't for the life of me get it to go back to Normal.
> >
> > I have googled, searched through the two MS SQL 2005 books I have but I
> > cant
> > seem to get any answer to why it happend or more importantly how to fix
> > the
> > problem.
> >
> > The only options I am given when I right click the Dbs is to restore the
> > transaction log but when I navigate to the my backup (which I restored
> > from)
> > it wont show any logs. I get a " You must select a restore Source" error
> > message. But I am pointing to a *.trn file.
> >
> > Im not a dba by any means more of a babysitter so this could be something
> > simple.
> >
> > Could anyone shed some light please.
> >
>