moving model

Question

moving model

torpkev

SSCarpal Tunnel

Points: 4819
More actions
September 11, 2008 at 7:30 am

#192581

Hi all,
Hope someone here can help me out.. I'm using SQL Server 2005 SP2 (I 99% sure its SP2) on Windows Server 2003
I'm trying to move my system databases using the processes laid out in http://msdn.microsoft.com/en-us/library/ms345408.aspx
This is more of a test than anything.. I'm doing it on a test server, I just want to know the process works.
I was able to move tempdb and msdb without any problems (I'm moving each one seperately.. so shutting down and starting the instance up between each database), but whenever I try and move model, the database won't restart.. here's my steps:
/* Run the following SQL to get the current locations and logical names */
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'model');
/* Move the datafile */
ALTER DATABASE model MODIFY FILE ( NAME = modeldev, FILENAME = 'E:\MSSQL Data\model.mdf' )
/* Move the logfile */
ALTER DATABASE model MODIFY FILE ( NAME = modellog, FILENAME = 'D:\MSSQL Logs\modellog.ldf' )
I then shutdown the SQL Server instance using the SQL Server Configuration Manager
I use Windows Explorer to physically move the .mdf and .ldf to the correct paths
Then I go back into SQL Server Configuration Manager and try to restart the database, but get the following error:
"The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details."
So I tried going to a command prompt to start the service
H:\>NET START MSSQLSERVER
The SQL Server (MSSQLSERVER) service is starting..
The SQL Server (MSSQLSERVER) service could not be started.
A service specific error occurred: 1814.
More help is available by typing NET HELPMSG 3547.
H:\>NET HELPMSG 3547
A service specific error occurred: ***.
EXPLANATION
A service-specific error occurred.
ACTION
Refer to the Help or documentation for that service to determine the problem.
So now I'm stumped
Any help would be greatly appreciated.
Thanks!
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Carl Rimmer SSCarpal Tunnel Points: 4068 More actions · Answer 1

Hi,

Take a look at the following link it explains that you need to detach and re-attach the model database after starting sql server in minimal mode:

http://support.microsoft.com/kb/224071.

I have found it easier to do via the command line using startup options:

net stop mssqlserver

to stop sql server service followed by

net start mssqlserver /f /t3608

to start sqlserver service in minimal configuration.

see link : http://msdn.microsoft.com/en-us/library/ms190737.aspx

Basically you start sql server in minimal mode, detach the database, copy the .mdf and .ldf to new locations, re-attach the database using the new locations for the mdf and ldf, stop sql server and then restart in FULL mode.

You will need to use sqlcmd to do it from the command line.

Hope this makes sense.

Carl

torpkev SSCarpal Tunnel Points: 4819 More actions · Answer 2

Thanks Carl..

still not sure why the microsoft link i had didn't work as expected, but I'll try the detach method... AFTER i reinstall sql sever.

thanks again!

--------------------------

I long for a job where my databases dont have any pesky users accessing them 🙂

humbleDBA Hall of Fame Points: 3490 More actions · Answer 3

Hi,

Sorry this is a bit late, but thought I would share...

I came up against the same problem receiving the following Error Message: ‘A service specific error occurred: 1814,’ when I moved sys tlogs to a separate drive and tried to re-start the SQL service.

The answer to my issue, allowing me to use the ALTER DATABASE... method for moving the tlogs, was to give the following SQL Server-created account 'full control' folder permissions to the root of the path where I moved the file to (eg, say L:\):

[ServerName]\SQLServer2005MSSQLUser$[ServerName]$[InstanceName]

(where [ServerName] is your server name and [InstanceName] is either

MSSQLSERVER for default instance or the Instance name)

Once I did this it worked perfectly.

HTH

Jpotucek SSCoach Points: 18583 More actions · Answer 4

I have the same problem. I moved my MODEL and MSDB Data and log files to different drives and it worked fine. when I tried to move them back, I received the "A service specific error occurred: 1814." error.

I then gave the following user FULL access to the drive that I moved the data and log files to

Server\SQLServer2005MSSQLUser$Server$MSSQLSERVER

and I am still getting this error: A service specific error occurred: 1814.

Any ideas?

Ashok Teotia SSCrazy Points: 2407 More actions · Answer 5

Ashok Teotia

SSCrazy

Points: 2407

July 27, 2012 at 12:51 am

#1518313

Hi,

OK.