April 9, 2008 at 3:38 am
Hi, i am trying to attach database which i had detached earlier, now i can able to attached the same but it is displayed in ready only state and not allowing me to do anything with it . Even i stopped the relevant services and delete the database and again try to attahced the previous version of the same (as backup is not working because of some issues with OS, i detached the database and keep it as its backup for time being).
Any idea????
April 9, 2008 at 3:48 am
Is the folder that the DB files are in either encrypted or compressed?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 9, 2008 at 4:03 am
No, the folder is same which i was using before. neither it is encrypted or read only. I exprience this when one of friend gave me detached db files of same (after woking on that db, as we were working on same project) to me. When i tried to attached it, it went into ready only mode, at that time i thought it could be problem with his pc and i asked for the scripts for the changes.. but now it happening in my case only..
One more issue.....To solve this problem i uninstalled sql express and again run the setup, now i am not able to find option to start sql server management studio. I read somewhere that with the setup of sql express it is also required to run the setup for s. management studio (UI) also. is this correct?? if yes i am thinking to download setup for the same
April 9, 2008 at 4:07 am
Check if you mdf file is read-only. If the primary data file being attached is read-only, the Database Engine assumes that the database is read-only.
I'm also curious which OS problems prevent you from using backups?
[font="Verdana"]Markus Bohse[/font]
April 9, 2008 at 4:15 am
Did you copy the files off a CD/DVD? If so, they'll be marked readonly (file property) and hence the DB will be read only once attached.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 9, 2008 at 4:18 am
ALTER DATABASE [db_name] SET READ_WRITE
GO
April 9, 2008 at 10:34 pm
No, it was not copied from cd/DVD. I checked the properties, it is not read only. Even i reinstall sql express and follow the same. still giving same problem ... in beging it shows ready only then show recovery/read-only state.
April 9, 2008 at 10:35 pm
alter command does't work as the database is in recovery/read only state
April 9, 2008 at 11:57 pm
Could you please have a look in the SQL error log (In object explorer) it'll be under Management ->SQL Server Logs.
Check to see what's there around the time you tried to attach the DB. It may give you a hint.
You can post the errors here it you're not sure of the meaning.
There's a big difference between read-only and recovering. What does the fllowing query return (run in the master DB)
select name, status_desc from sys.databases where name = < Name of read only db >
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 10, 2008 at 11:10 pm
Hi!
thanks for ur prompt help..
I tried to attach same file to another pc where sql2005 is installed and i could attache it easily. As i had to carry out the task, i took backup of the this attahced db and then restore it to my pc now it is working as it was before (as explained earlier i used to attach/detach the db because it was giving error while taking backup/restore 'operating system error 5 (Acess is denied.). (Microsoft.SqlServer.Express.Smo)' , i resolved this problem and could restore backup successfully), But yes..... still i would like to try to follow the steps you have suggested for attach/detach problem.
April 10, 2008 at 11:52 pm
Sounds like there might be some permissions problems somewhere.
Do you know what account SQL Express is running as? (SQL Server configuration manager will show you that)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 29, 2008 at 8:35 am
It also sound, in reading the full thread, that you encountered an early error that is causing this problem, so that you're looking in the wrong place for the source of the error. Generally, if you resolve the source of the error, you may have a little extra work to do, but you end up with a cleaner final resolution (and usually learn a little more along the way). So if you could elaborate on the 'original' symptoms you received, regarding the backups?
September 29, 2008 at 2:16 pm
I just ran into this and it was a permissions error.
right click on db & log files Properties->Security
I had to add in the sql server instance user account to have access. when pasting in the 2 files all the user accounts did not get set and I had to manually set them.
worked like a charm after adding it in.
if you have existing databases you can examine the file permissions on those to ensure you add in the same with correct permissions.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply