March 17, 2008 at 2:12 pm
I successfully installed SQL Server 2005 and then used the ALTER DATATBASE statement to move around my data and log files for Model, MSDB and TEMPDB. I stopped SQL SERVER Services and physically moved the files to their new locations.. now the service won't start? I get this in the SQL Server ERROLOG:
Starting up database 'model'.
2008-03-17 15:57:01.29 spid9s Error: 17207, Severity: 16, State: 1.
2008-03-17 15:57:01.29 spid9s FCB::Open: Operating system error 5(Access is denied.) occurred while creating or opening file 'E:\MSSQL\AML\DataFiles'. Diagnose and correct the operating system error, and retry the operation.
2008-03-17 15:57:01.29 spid9s Error: 17204, Severity: 16, State: 1.
2008-03-17 15:57:01.29 spid9s FCB::Open failed: Could not open file E:\MSSQL\AML\DataFiles for file number 1. OS error: 5(Access is denied.).
2008-03-17 15:57:01.29 spid4s Server name is 'KOCSQLDEV01\AML'. This is an informational message only. No user action is required.
2008-03-17 15:57:01.29 spid9s Error: 5120, Severity: 16, State: 101.
2008-03-17 15:57:01.29 spid9s Unable to open the physical file "E:\MSSQL\AML\DataFiles". Operating system error 5: "5(Access is denied.)".
2008-03-17 15:57:01.29 spid9s Error: 17207, Severity: 16, State: 1.
2008-03-17 15:57:01.29 spid9s FCB::Open: Operating system error 5(Access is denied.) occurred while creating or opening file 'F:\MSSQL\AML\LogFiles'. Diagnose and correct the operating system error, and retry the operation.
2008-03-17 15:57:01.29 spid9s Error: 17204, Severity: 16, State: 1.
I can log on to the Server with the account that starts the services and open the .mdf and .ldf files in Notepad.. not sure what's going on.. can someone help?
March 17, 2008 at 2:20 pm
The service account under which sql server is running just doesn't have enough writes on the directories to which you moved the files.
If you just give the service account full rights on the directories to which you moved the files it will be fine
hth
David
March 17, 2008 at 2:31 pm
I gave my domain account that starts the services Full control on each of the Volumes that I moved my files to. I checked down to the file level and the permissions propogated down to the file level... same error when i try and start the SQL Server service..
March 17, 2008 at 2:33 pm
are the data files read only by any chance?
March 17, 2008 at 2:49 pm
- did you follow the KB for moving systemdatabases around ?
- can you doublecheck the sqlserver service startup parameters ?
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 17, 2008 at 2:56 pm
I followed this article:
http://msdn2.microsoft.com/en-us/library/ms345408.aspx
It seemed easy. alter Database to modify file locations.
move physical files and restart the Services..
I checked the started parameters for the Service.. seems ok.
This is the code I used to relocate the files:
ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBData , FILENAME = 'E:\MSSQL\AML\DataFiles' )
ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBLog , FILENAME = 'F:\MSSQL\AML\LogFiles' )
ALTER DATABASE Model MODIFY FILE ( NAME = ModelDEV , FILENAME = 'E:\MSSQL\AML\DataFiles' )
ALTER DATABASE Model MODIFY FILE ( NAME = ModelLog , FILENAME = 'F:\MSSQL\AML\LogFiles' )
ALTER DATABASE TempDB MODIFY FILE ( NAME = TempDEV , FILENAME = 'G:\MSSQL\AML\TEMPDB' )
ALTER DATABASE TempDB MODIFY FILE ( NAME = TempLOG , FILENAME = 'G:\MSSQL\AML\TEMPDB' )
Files are not read-only ????
March 17, 2008 at 3:51 pm
now I'm really confused. I was able to start in recovery mode from a command prompt
net start mssql$instancename /f /t3608
it did not create the tempdb files
March 17, 2008 at 5:34 pm
Sorry i may be being silly but is that exactly the code you used?
Shouldn't it be e.g depending on the filenames of the files moved in the case of msdb and model
ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBData , FILENAME= 'E:\MSSQL\AML\DataFiles\msdbdata.mdf' )
ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBLog , FILENAME = 'F:\MSSQL\AML\LogFiles\msdblog.ldf' )
ALTER DATABASE Model MODIFY FILE ( NAME = ModelDEV , FILENAME = 'E:\MSSQL\AML\DataFiles\model.mdf' )
ALTER DATABASE Model MODIFY FILE ( NAME = ModelLog , FILENAME = 'F:\MSSQL\AML\LogFiles\model.ldf' )
ALTER DATABASE TempDB MODIFY FILE ( NAME = TempDEV , FILENAME = 'G:\MSSQL\AML\TEMPDB\tempdev.mdf' )
ALTER DATABASE TempDB MODIFY FILE ( NAME = TempLOG , FILENAME = 'G:\MSSQL\AML\TEMPDB\templog.ldf' )
hth
David
March 17, 2008 at 5:57 pm
I thought I did it correctly...
I put the .MDF files for Model and MSDB on E:
the .LDF files for Model and MSDB on F:
and the TEMPDB .MDF and .LDF files on G:
Then I stopped SQL Server and moved the files around...
am I missing something? I'm hoping it's just something stupid I'm missing because I really need to get this up.
March 17, 2008 at 6:11 pm
Well in the script you posted you did not specify the actual filenames just the logical filenames and the directories in which they would reside force sql server to start as you did earlier and run
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'msdb');
and you should get something like
name current location
MSDBData E:\MSSQL\AML\DataFiles\MSDBData.mdf
MSDBLog F:\MSSQL\AML\DataFiles\MSDBLog.ldf
but looking at your script this is not what will be returned
hth
David
March 17, 2008 at 7:25 pm
How dumb of me... I didn't specify the filenames in the Alter database statements!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! any ideas?
March 17, 2008 at 7:30 pm
You're right!!!how dumb of me.. I didn't specify the actual filenames!!
any idea how I can fix this?
March 17, 2008 at 7:37 pm
I tried moving the files back to their original locationbut I get the same error starting the service.. is there a way to start a session and rerun the code and specify the file locations???
March 17, 2008 at 8:17 pm
Well for a lack of better terms you messed your new instance up. You have to put the SQL 2005 install disk in and rebuild the system databases.
This link should help:
http://www.mshelpzone.com/Forum/viewtopic.php?f=13&t=6&sid=3eb9554da0db1513727741443fedb8d3
March 18, 2008 at 2:31 am
Thank you so much!!!!!!!!!!!!!! I really did mess up!!!!!!
Do you think it would be easier just to re-install? or will that make a bigger mess?
I'm not sure if our infrastructure guys have the actual CD. I'm pretty sure it was just downloaded from Microsoft. I tried running from the command prompt and specifying the path to the folder (on the server) where the disk1 files are and that didn't work. it didn't give an error, just came straight back to a command prompt.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply