SQL won't start after moving Model DB

[eastbr]

I used the code below to move the Model database and Model log file to new drive locations.

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path/os_file_name' )

After running the commands I stopped the SQL server and moved the physical files to the correct location.

Now I am unable to start SQL server, I get error 17204 Could not open file:Access is denied

I think the problem maybe that for FILENAME=new_path I put "J:\SQL Server\MSSQL.1\MSSQL\Data"
but did not include model.mdf in the file path.

How do I correct the path now that SQL service won't start?

The SQL error log is below.

Thanks for your help


2007-09-11 13:19:31.82 Server Microsoft SQL Server 2005 - 9.00.3042.00 (X64)
Feb 10 2007 00:59:02
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

2007-09-11 13:19:31.82 Server (c) 2005 Microsoft Corporation.
2007-09-11 13:19:31.82 Server All rights reserved.
2007-09-11 13:19:31.82 Server Server process ID is 3264.
2007-09-11 13:19:31.82 Server Authentication mode is MIXED.
2007-09-11 13:19:31.82 Server Logging SQL Server messages in file 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2007-09-11 13:19:31.82 Server This instance of SQL Server last reported using a process ID of 2648 at 9/11/2007 1:10:02 PM (local) 9/11/2007 8:10:02 PM (UTC). This is an informational message only; no user action is required.
2007-09-11 13:19:31.82 Server Registry startup parameters:
2007-09-11 13:19:31.82 Server -d D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2007-09-11 13:19:31.82 Server -e D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2007-09-11 13:19:31.82 Server -l D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2007-09-11 13:19:31.84 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2007-09-11 13:19:31.84 Server Detected 4 CPUs. This is an informational message; no user action is required.
2007-09-11 13:19:31.96 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2007-09-11 13:19:31.98 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2007-09-11 13:19:32.00 Server Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2007-09-11 13:19:32.00 Server Database mirroring has been enabled on this instance of SQL Server.
2007-09-11 13:19:32.00 spid5s Starting up database 'master'.
2007-09-11 13:19:32.09 spid5s SQL Trace ID 1 was started by login "sa".
2007-09-11 13:19:32.10 spid5s Starting up database 'mssqlsystemresource'.
2007-09-11 13:19:32.10 spid5s The resource database build version is 9.00.3042. This is an informational message only. No user action is required.
2007-09-11 13:19:32.26 spid5s Server name is 'SS02'. This is an informational message only. No user action is required.
2007-09-11 13:19:32.26 spid9s Starting up database 'model'.
2007-09-11 13:19:32.26 spid9s Error: 17207, Severity: 16, State: 1.
2007-09-11 13:19:32.26 spid9s FCB::Open: Operating system error 5(Access is denied.) occurred while creating or opening file 'J:\SQL Server\MSSQL.1\MSSQL\Data'. Diagnose and correct the operating system error, and retry the operation.
2007-09-11 13:19:32.26 spid9s Error: 17204, Severity: 16, State: 1.
2007-09-11 13:19:32.26 spid9s FCB::Open failed: Could not open file J:\SQL Server\MSSQL.1\MSSQL\Data for file number 1. OS error: 5(Access is denied.).
2007-09-11 13:19:32.26 spid9s Error: 5120, Severity: 16, State: 101.
2007-09-11 13:19:32.26 spid9s Unable to open the physical file "J:\SQL Server\MSSQL.1\MSSQL\Data". Operating system error 5: "5(Access is denied.)".
2007-09-11 13:19:32.26 spid9s Error: 17207, Severity: 16, State: 1.
2007-09-11 13:19:32.26 spid9s FCB::Open: Operating system error 5(Access is denied.) occurred while creating or opening file 'I:\SQL Server\MSSQL.1\MSSQL\Data'. Diagnose and correct the operating system error, and retry the operation.
2007-09-11 13:19:32.26 spid9s Error: 17204, Severity: 16, State: 1.
2007-09-11 13:19:32.26 spid9s FCB::Open failed: Could not open file I:\SQL Server\MSSQL.1\MSSQL\Data for file number 2. OS error: 5(Access is denied.).
2007-09-11 13:19:32.26 spid9s Error: 5120, Severity: 16, State: 101.
2007-09-11 13:19:32.26 spid9s Unable to open the physical file "I:\SQL Server\MSSQL.1\MSSQL\Data". Operating system error 5: "5(Access is denied.)".
2007-09-11 13:19:32.26 spid9s File activation failure. The physical file name "I:\SQL Server\MSSQL.1\MSSQL\Data" may be incorrect.
2007-09-11 13:19:32.26 spid9s Error: 945, Severity: 14, State: 2.
2007-09-11 13:19:32.26 spid9s Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
2007-09-11 13:19:32.26 spid9s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
2007-09-11 13:19:32.26 spid9s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

 

[mrdenny]

Try starting the SQL Server from the command line with the -m and -c switches. This will start the SQL Server in Single user mode. This should allow it to start. You will then be able to log in with a single connection (so use sqlcmd or osql) and alter the model database again.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[MissTipps]

Hi

I was just thinking that trying to start the service even in single user mode would probably be bad because it would still fail when trying to bring model online. There is a switch -T3608 which starts SQL Server with minimal configuration and brings only master online, but as its SQL 2005 you wouldn't be able to resolve the problem by updating the sysdatabases table. I'm actually amazed you got away with the ALTER DATABASE statement in the first place!!

My suggestion would be to use the switch I quoted above to start the server service and restore the master database from a backup (hope you have one).

For future reference, if you want to move the model or msdb databases, again, start the server with that -T3608 switch then detach the databases, move the files and then reattach them again.

Hope this all works out for you

MissTipps

CISSP, CEH, CEI, MCT, MCDBA, MCSE 2K3, CTT+, ECSA, Security+

 

[eastbr]

I restarted as you suggested, corrected the file path and ran my tsql statement again and it is working fine now.

Thanks,
Brent