Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Urgent!! Moving System Database in MsSql Server 2005 Cannot start Server NOW!!

Posted on 2007-10-03
11
Medium Priority
?
1,602 Views
Last Modified: 2013-12-05
Hi,
i want to move my System database
i had been using these two pages as refrence:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q224071
http://deepakinsql.blogspot.com/2007/08/moving-system-databases-in-sql-2005.html

i started with moving the Model database first.

These are the steps i followed:

1. In SQL Server Enterprise Manager, right-click the server name, and then click Properties.
2. On the General tab, click Startup Parameters.
3. Add the following new parameter:
;-T3608

Once this is done do the following:
1. Stop and then restart SQL Server.
2. Detach the model database by using the following commands:
use [master]
go
sp_detach_db 'model'
go
3. Move the Model.mdf and Modellog.ldf files from the
C drive location folders to the respective folder

4. Reattach the model database by using the following commands
use [master]
go
sp_attach_db 'model', D:\MSSQL\Data\model.mdf','E:\modellog.ldf'
go

However when i went to reattach the model it would not allow me to and it disabled the sql server i can not start it
when i try to start it from command promt using Net Start MSSQLSERVER (with out the trace flag) it gives
me the following error;

A service specific error occured 1814

When  i try to connect through SSMS i get this error:
an error had occurred while establishing a connection to the server. When connecting to SQL Server 2005. this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider:Named Pipes Provider,error: 40- Could not open a connection to SQL Server)

How can i retract my steps. I have removed the trace flage from the lookup parameter and this still doesn't work
please help? and any suggestions how to get it to work

Thank you
Putoch
0
Comment
Question by:Putoch
  • 6
  • 3
10 Comments
 
LVL 27

Expert Comment

by:ptjcb
ID: 20005971
I am a little confused.

You say in the title that you want to move SQL 2005 databases. The examples that you give us are for moving SQL 2005 databases, but your first step is:

1. In SQL Server Enterprise Manager, right-click the server name, and then click Properties.
2. On the General tab, click Startup Parameters.
3. Add the following new parameter:
;-T3608


Here are the issues
1 - There is no Enterprise Manager in SQL 2005.

3 - The new parameter is c-m-T3608 (not ;-T3608)

As for 1814 - this link has information.
http://www.developerfood.com/what-causes-service-specific-error-1814-0x716/microsoft-public-sqlserver-server/f91c5cb1-f0a6-42f9-8417-2866823d4b56/article.aspx
0
 

Author Comment

by:Putoch
ID: 20006079
Hi sorry for my mis guiding information.

i was trying to move the System databases in my sql server
model,msdb,master adn tempdb

i started off by moving model first and was following these steps;
Because i can't not just detatch the model database i had to flag it using teh -T3608 in the lookup parameters in the SQL Server properties Advanced tab on the Configuration Manager.

Then i detached the database.
then i stoped and started the server again and this was ok.
then i moved my files from the c drive (only the model mdf and log files) to the d drive
then i went to go into the gui to attach the database again and it would not let me the server seemed to have stoped.

I got the errors in the gui saying :
an error had occurred while establishing a connection to the server. When connecting to SQL Server 2005. this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider:Named Pipes Provider,error: 40- Could not open a connection to SQL Server)

When i try to restart the server on the configuration manager it tells me
''The request failed or the esrvice did not respond in a timely fahion.Consult the even log''

When i try to start it from command promt using Net Start MSSQLSERVER (with out the trace flag) it gives me the following error;
''A service specific error occured 1814''

When i check the even log it tells me :
  Could not find database ID 3. Database may not be activated yet or may be in transition. Reissue the query once the database is available. If you do not think this error is due to a database that is transitioning its state and this error continues to occur, contact your primary support provider. Please have available for review the Microsoft SQL Server error log and any additional information relevant to the circumstances when the error occurred.

SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

I really don't know what to do please help.
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 20006200
Do you have your SQL Server installation disk handy? There is a good chance that you will be re-installing SQL Server.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Putoch
ID: 20006222
What?seriously?why is this
i followed the steps in the MS manual can i not back track or something?
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 20006321
Seriously.

The model database is missing. You cannot add the model database to SQL Server. You cannot start SQL Server. I would reinstall SQL Server.

Maybe someone else has other advice?



0
 

Author Comment

by:Putoch
ID: 20006350
i have a back up of the model database but i can't even restart sql server to restart.
I can't imagine that this is the only resolve to this problem? i hope not anyways has anyone else any advice or had you the same problem and what did you do?
0
 

Author Comment

by:Putoch
ID: 20006553
Is there anyway to attach the model databse again while the sql service is stopped
0
 

Accepted Solution

by:
Putoch earned 0 total points
ID: 20007290
Ok I have fixed it.
For anyone who has this problem maybe try this.
I didn't have to Install my server again.

My server could not see the model database and so stoped and would not start.
Because i had moved the log files and mdf files to another drive from the C drive i First moved them back to the c drive and removed the look up parameter ;-T3608.

When i tried to start it on single user mode this worked, but be aware. In my case i have many other servers hitting my box and so if i did not run my re-attach query quick enough they would have me logged out and i would have to try and start the single user mode again
run this on your cmd:sqlservr.exe -c -m.
Then have this ready in your GUI to run :
use [master]
go
sp_attach_db 'model', 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\model.mdf','C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\modellog.ldf'
go
[Rem to put in where ever your own destination path is here]

now that i had re attahced the modle database i was able to restart the sqlserver
NET START MSSQLSERVER.

this enabled me to start the server thank God!
i am not going to move my system files untill i talk with microsoft or use my test server (which is currently being built) i hope this helps anyone who might of had the same problem.

thanks
Putoch
0
 

Author Comment

by:Putoch
ID: 20035443
Because i answered this Question my self and added the solution how do i take off the points and close this question.
0
 
LVL 1

Expert Comment

by:Vee_Mod
ID: 20077093
Closed, 350 points refunded.
Vee_Mod
Community Support Moderator
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question