Missing MSDB and Model database 1

[lilsamp]

I was in the process of moving my model and msdb database files using the detach db stored procedure. I got an error during the attach db process which led me to change a start up parameter for the server. The instructions said stop and restart the server and without thinking I did. The problem is that I forgot to attach the model and msdb databases. Now, the server will start,but I can't connect to the server(a broken connection error). Is there anyway I can attach the model and msdb database and fix this problem.

Note: I have tried attaching the databases using Query Analyzer and I was unable to complete that task(broken connection error).

 

[zhavic]

a) with Query Analyzer

1. stop your SQL server if it is runnig
2. run command prompt (click start then run, type cmd then click OK)
3. type next command, it runs your SQL server in minimal configuration
c:\path_to_the_server_executable\sqlservr.exe -f

DO NOT CLOSE THAT WINDOW !!!
4. run Query Analyzer
5. if it asks you 'Do you with to re-connet now?', click on 'No'
6. type sp_attach_db 'model', 'c:\path_to_model_mdf_file\model.mdf', 'c:\path_to_model_ldf_file\modellog.ldf' and run it
7. type sp_attach_db 'msdb', 'c:\path_to_msdb_mdf_file\msdbdata.mdf', 'c:\path_to_msdb_ldf_file\msdblog.ldf' and run it
8. switch to window with SQL server running ( that from step 3 )
9. press CTRL + C or CTRL + BREAK, it asks you to stop the server, type 'Y'
10. start your SQL server by service manager or by Enterprise Manager or by your favourite way
11. do not forgot remove -T3608 startup parameter by Enterprise Manager, if it is still there
12. close command prompt window
13. take a beer


b) without Query Analyzer

1. stop your SQL server if it is runnig
2. run command prompt (click start then run, type cmd then click OK)
3. type next command, it runs your SQL server in minimal configuration
c:\path_to_the_server_executable\sqlservr.exe -f

DO NOT CLOSE THAT WINDOW !!!

5. run another command prompt
6. use osql utility to attach databases ( location of that utility is based on instalation, standard is C:\Program Files\Microsoft SQL Server\80\Tools\Binn\ )
osql -S server_name -U sa -Q "sp_attach_db 'model', 'c:\path_to_model_mdf_file\model.mdf', 'c:\path_to_model_ldf_file\modellog.ldf'"

it asks you for password for user 'sa'
if you are using thrusted connections, than use
osql -S server_name -E -Q "sp_attach_db 'model', 'c:\path_to_model_mdf_file\model.mdf', 'c:\path_to_model_ldf_file\modellog.ldf'"

7. do the same for 'msdb' database
8. switch to window with SQL server running ( that from step 3 )
9. press CTRL + C or CTRL + BREAK, it asks you to stop the server, type 'Y'
10. start your SQL server by service manager or by Enterprise Manager or by your favourite way
11. do not forgot remove -T3608 startup parameter by Enterprise Manager, if it is still there
12. close that 2 command prompt windows
13. take a beer


Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[zhavic]

Ohh, I forgot step 4. in the method b)

So 4. get a cigar

Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[lilsamp]

It worked, thanks

 

[MDXer]

zhavic,

Not that I have the same problem but great solution with easy to follow steps. Maybe you should make it into a faq.


"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck