March 1, 2008 at 8:01 am
I am trying to move some databases from the E: drive to the F: drive as we are running out of space on E:. In Enterprise Manager (SQL 2000) I've backed up the database, detached the database (both with and without updating statistics, copied the files to F: and reattached them. The database appears read only. I can run a DBCC CHECKDB and no errors are generated but when I try to remove the readonly property I get "Error 5105: Device activation error. The physical file name 'F:\Databases\data\sdbWCQP_Data.MDF' may be incorrect. The physical file name 'F:\Databases\data\sdbWCQP_Log.LDF' may be incorrect. Could not restart the database 'sdbWCQP'. Reverting back to old status."
If I detach and reattach on the E: drive or if I create the database on the F: drive and restore the backup it works fine. Only have the problem with detach from E: and reattach on F:
I can always do the backup and restore but I'm trying to minimize the time the database is unavailable and figure detach, copy and move will be faster.
March 2, 2008 at 10:34 am
I am guessing that the permissions in the filesystem on F are giving you issues. Be sure that the service account for SQL Server has rights to read and write to F and the files themselves are not marked as read only.
March 3, 2008 at 3:52 am
Is the F drive compressed or encrypted?
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
March 3, 2008 at 5:10 am
how did you reattach the db ?
- using EM ? (may bite you in the back because it reads what's stated in
the mdf-file (still pointing to the old location))
- using QA ?
I'd prefer using QA because then you'll have to type the correct path and names.
e.g.
exec sp_attach_db 'yourdb'
,'X:\Mssql\Data\yourdb.mdf'
,'X:\Mssql\Data\yourdb.ndf'
,'V:\mssql\Log\yourdb_Log.ldf'
You may generate it as a preparation:
set nocount on
--select 'select '''+name+''' as dbnaam , filename from '+ name + '..sysfiles '+ char(13) + ' union all '
select 'select case when filename like ''%.mdf%'' '
+ char(13) + ' then char(13) + ''go'' + char(13) + ''exec sp_attach_db ''''' + name + ''''''' '
+ char(13) + ' else '' '' end '
+ char(13) + ' + '' , '' + N''N'''''' + rtrim(filename) + '''''''' as SQL '
+ char(13) + 'from '+ name + '..sysfiles '+ char(13) + ' union all '
from master..sysdatabases
where name not in ('master', 'msdb', 'tempdb', 'model')
order by name
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 3, 2008 at 8:41 am
No, permissions are not the issue. I can create new DBs in that directory without a problem.
March 3, 2008 at 8:42 am
Good suggestion, but no, there is no encryption or compression.
March 5, 2008 at 3:58 am
Something else to check. Are either the mdf file or ldf file marked as readonly? (Check the file properties)
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
March 5, 2008 at 5:10 am
>"Something else to check. Are either the mdf file or ldf file marked as readonly? (Check the file properties) "
No.
March 5, 2008 at 5:12 am
>"how did you reattach the db ?"
with EM. Why would doing it with QA be any better? More likely to make typing mistakes.
March 5, 2008 at 5:34 am
Dave Callaghan (3/5/2008)...Why would doing it with QA be any better? More likely to make typing mistakes.
because EM will present you the old file-locations for all non-mdf files.
(if gets them from the mdf-file content)
You will have to modify the file-locations !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 8, 2008 at 9:46 pm
Go to Database properties, Options, Database Read-Only, change it to False, it worked for me.
March 14, 2008 at 12:21 pm
Tried removing the read only flag early on. The properties box closes without any warnings but upon reopening, the flag is still set.
October 8, 2009 at 4:53 pm
Hey guys,
One of the things you need in order to attach a .mdf sql server 2005 file with read-write permissions on the database is adding the user NETWORK SERVICE for the folder where the mdl and log files are located.
this did it for me.
September 27, 2016 at 12:45 am
Log in to SSMS as windows user and attach the DB that should resolve Read Only Marked DB issue if you are moving mdf and ldf files from other machines
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply