SQL Server 2005 database in Read-only state= True, can't set to False
I have a SQL Server 2005 database moved from one machine to another and when moved Read-only = True; I can't set Read-Only to false via the interface, or by using either of the below:
ALTER DATABASE ASSCAP SET READ_WRITE,
or
EXEC sp_dboption 'ASSCAP', 'read only', false
Thanks in advance for any assistance.
Jim
ALTER DATABASE ASSCAP SET READ_WRITE,
or
EXEC sp_dboption 'ASSCAP', 'read only', false
Thanks in advance for any assistance.
Jim
ASKER
copy/paste wont pull over from rdp:
status = online, updatability = read_only, useraccess=multi_user, recovery = full
compatibility =90
status = online, updatability = read_only, useraccess=multi_user, recovery = full
compatibility =90
Sidenote: It will if under 'Local Resources' you click on 'Clipboard'.
Anyway....
What if you try:
ALTER DATABASE ASSCAP SET READ_WRITE WITH ROLLBACK_IMMEDIATE
??
If that fails, run "sp_who2" and see who's in that DB, kick them out and try again. Other suggestions, check the physical files to ensure the account running SQL has full permission to them.
HTH
Anyway....
What if you try:
ALTER DATABASE ASSCAP SET READ_WRITE WITH ROLLBACK_IMMEDIATE
??
If that fails, run "sp_who2" and see who's in that DB, kick them out and try again. Other suggestions, check the physical files to ensure the account running SQL has full permission to them.
HTH
ASKER
the ALTER gave me incorrect syntax near ROLLBACK_IMMEDIATE. User was kicked out, my account is dbo
Doh. Try taking out the _
File permissions all OK, No-one in the database, including you ?
File permissions all OK, No-one in the database, including you ?
ASKER
correctamundo
What error does it give when it fails?
ASKER
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "D:\SQL2005\ASSCAP.mdf". Operating system error 5: "5(Access is denied.)".
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "D:\SQL2005\ASSCAP_log.ldf
File activation failure. The physical file name "D:\SQL2005\ASSCAP_log.ldf
Msg 945, Level 14, State 2, Line 1
Database 'ASSCAP' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "D:\SQL2005\ASSCAP.mdf". Operating system error 5: "5(Access is denied.)".
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "D:\SQL2005\ASSCAP_log.ldf
File activation failure. The physical file name "D:\SQL2005\ASSCAP_log.ldf
Msg 945, Level 14, State 2, Line 1
Database 'ASSCAP' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
ASKER
I can attach and detach on the other machine at will no problem, and no space problem
>>Msg 5120, Level 16, State 101, Line 1
>>Unable to open the physical file "D:\SQL2005\ASSCAP.mdf". Operating system error 5: "5(Access is denied.)".
I'm still thinking physical file security and/or the file is marked as read only.
>>Unable to open the physical file "D:\SQL2005\ASSCAP.mdf". Operating system error 5: "5(Access is denied.)".
I'm still thinking physical file security and/or the file is marked as read only.
ASKER
files not set to read only. i'm dbo...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for hanging with me on this.
No Worries :o)
Can you copy/paste in the status and compatability level
Cheers