Sunday, February 12, 2012

Changing database status from Read Only

Ive attached a database from 2 MDF files I copied from a production
database. The attach works fine, except it always attaches in Read Only
mode.
I notice when attaching that, despite me specifying sa as the database
owner, it always makes my domain account the database owner. When I try to
make the owner sa using sp_changedbowner, I get error messages about the
database read only.
How can I make the database turn back to normal status?
ThanksElliot,
try ALTER DATABASE dbname SET READ_WRITE
HTH,
Paul Ibison|||Paul:
Thanks for the reply.
Running this command returns the following errors:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name
'F:\sqldata\DOCUMENTS_Data.MDF' may be incorrect.
There are 2 primary files, along with a single log file. Ive confirmed their
locations and all hae their read/write attributes set properly. The only
other thing I can think of is permissions, which should happen because I am
logged into QA and EM as sa.
Any other thoughts?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23tRADqASEHA.3016@.tk2msftngp13.phx.gbl...
> Elliot,
> try ALTER DATABASE dbname SET READ_WRITE
> HTH,
> Paul Ibison
>|||Your files are probably in 'Read Only' mode before you
even restore the database (Usually if it was taken out of
the CD). Change the file properties first by checking OFF
the 'read-only' option before restoring the
database......
'F:\sqldata\DOCUMENTS_Data.MDF' may be incorrect.
error is stating that the server you are restoring it to
does not have that path (It is the old server's path).
Change that in the 'Options'--> 'Restore AS' part.
>--Original Message--
>Paul:
>Thanks for the reply.
>Running this command returns the following errors:
>Server: Msg 5105, Level 16, State 4, Line 1
>Device activation error. The physical file name
>'F:\sqldata\DOCUMENTS_Data.MDF' may be incorrect.
>There are 2 primary files, along with a single log file.
Ive confirmed their
>locations and all hae their read/write attributes set
properly. The only
>other thing I can think of is permissions, which should
happen because I am
>logged into QA and EM as sa.
>Any other thoughts?
>
>
>"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
>news:%23tRADqASEHA.3016@.tk2msftngp13.phx.gbl...
>> Elliot,
>> try ALTER DATABASE dbname SET READ_WRITE
>> HTH,
>> Paul Ibison
>>
>
>.
>|||Jack:
I'm doing an attach from the MDF, NDF, and LDF files. I'm not restoring from
a backup.
"Jack" <anonymous@.discussions.microsoft.com> wrote in message
news:16aec01c44812$477d4620$a501280a@.phx.gbl...
> Your files are probably in 'Read Only' mode before you
> even restore the database (Usually if it was taken out of
> the CD). Change the file properties first by checking OFF
> the 'read-only' option before restoring the
> database......
> 'F:\sqldata\DOCUMENTS_Data.MDF' may be incorrect.
> error is stating that the server you are restoring it to
> does not have that path (It is the old server's path).
> Change that in the 'Options'--> 'Restore AS' part.
>
> >--Original Message--
> >Paul:
> >
> >Thanks for the reply.
> >
> >Running this command returns the following errors:
> >Server: Msg 5105, Level 16, State 4, Line 1
> >Device activation error. The physical file name
> >'F:\sqldata\DOCUMENTS_Data.MDF' may be incorrect.
> >
> >There are 2 primary files, along with a single log file.
> Ive confirmed their
> >locations and all hae their read/write attributes set
> properly. The only
> >other thing I can think of is permissions, which should
> happen because I am
> >logged into QA and EM as sa.
> >
> >Any other thoughts?
> >
> >
> >
> >
> >"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> >news:%23tRADqASEHA.3016@.tk2msftngp13.phx.gbl...
> >> Elliot,
> >> try ALTER DATABASE dbname SET READ_WRITE
> >> HTH,
> >> Paul Ibison
> >>
> >>
> >
> >
> >.
> >|||Check those MDF, NDF and LDF files by right clicking on
them and choosing Properties -- General tab. See if
the 'Read Only' attribute is checked ON. If it is, check
it OFF , then restore the database........
>--Original Message--
>Jack:
>I'm doing an attach from the MDF, NDF, and LDF files. I'm
not restoring from
>a backup.
>
>"Jack" <anonymous@.discussions.microsoft.com> wrote in
message
>news:16aec01c44812$477d4620$a501280a@.phx.gbl...
>> Your files are probably in 'Read Only' mode before you
>> even restore the database (Usually if it was taken out
of
>> the CD). Change the file properties first by checking
OFF
>> the 'read-only' option before restoring the
>> database......
>> 'F:\sqldata\DOCUMENTS_Data.MDF' may be incorrect.
>> error is stating that the server you are restoring it to
>> does not have that path (It is the old server's path).
>> Change that in the 'Options'--> 'Restore AS' part.
>>
>> >--Original Message--
>> >Paul:
>> >
>> >Thanks for the reply.
>> >
>> >Running this command returns the following errors:
>> >Server: Msg 5105, Level 16, State 4, Line 1
>> >Device activation error. The physical file name
>> >'F:\sqldata\DOCUMENTS_Data.MDF' may be incorrect.
>> >
>> >There are 2 primary files, along with a single log
file.
>> Ive confirmed their
>> >locations and all hae their read/write attributes set
>> properly. The only
>> >other thing I can think of is permissions, which should
>> happen because I am
>> >logged into QA and EM as sa.
>> >
>> >Any other thoughts?
>> >
>> >
>> >
>> >
>> >"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in
message
>> >news:%23tRADqASEHA.3016@.tk2msftngp13.phx.gbl...
>> >> Elliot,
>> >> try ALTER DATABASE dbname SET READ_WRITE
>> >> HTH,
>> >> Paul Ibison
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||I see what you mean now.....
Maybe the database was in a 'Read Only' status at the time
it was detached ?
>--Original Message--
>Jack:
>I'm doing an attach from the MDF, NDF, and LDF files. I'm
not restoring from
>a backup.
>
>"Jack" <anonymous@.discussions.microsoft.com> wrote in
message
>news:16aec01c44812$477d4620$a501280a@.phx.gbl...
>> Your files are probably in 'Read Only' mode before you
>> even restore the database (Usually if it was taken out
of
>> the CD). Change the file properties first by checking
OFF
>> the 'read-only' option before restoring the
>> database......
>> 'F:\sqldata\DOCUMENTS_Data.MDF' may be incorrect.
>> error is stating that the server you are restoring it to
>> does not have that path (It is the old server's path).
>> Change that in the 'Options'--> 'Restore AS' part.
>>
>> >--Original Message--
>> >Paul:
>> >
>> >Thanks for the reply.
>> >
>> >Running this command returns the following errors:
>> >Server: Msg 5105, Level 16, State 4, Line 1
>> >Device activation error. The physical file name
>> >'F:\sqldata\DOCUMENTS_Data.MDF' may be incorrect.
>> >
>> >There are 2 primary files, along with a single log
file.
>> Ive confirmed their
>> >locations and all hae their read/write attributes set
>> properly. The only
>> >other thing I can think of is permissions, which should
>> happen because I am
>> >logged into QA and EM as sa.
>> >
>> >Any other thoughts?
>> >
>> >
>> >
>> >
>> >"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in
message
>> >news:%23tRADqASEHA.3016@.tk2msftngp13.phx.gbl...
>> >> Elliot,
>> >> try ALTER DATABASE dbname SET READ_WRITE
>> >> HTH,
>> >> Paul Ibison
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>

No comments:

Post a Comment