We help IT Professionals succeed at work.

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

1,625 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
Comment
Watch Question

Commented:
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

Author

Commented:
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.

Commented:
Do you have your SQL Server installation disk handy? There is a good chance that you will be re-installing SQL Server.

Author

Commented:
What?seriously?why is this
i followed the steps in the MS manual can i not back track or something?

Commented:
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?



Author

Commented:
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?

Author

Commented:
Is there anyway to attach the model databse again while the sql service is stopped
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Because i answered this Question my self and added the solution how do i take off the points and close this question.

Commented:
Closed, 350 points refunded.
Vee_Mod
Community Support Moderator

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.