SQL 2000

Dangerous · 25 Jul 2007 at 21:55

Okay problem - MSDB is suspect in Enterprise manager so I hear you can rebuild it but i'm failing on running the rebuild sql file.

Anyone got a easy guide to follow?

teaboy5 · 25 Jul 2007 at 22:07

Ok got this from google

1.. In SQL Server Enterprise Manager, right-click the server name and
click Properties.
2.. On the General tab, click Startup Parameters.
3.. Add a new parameter as "-T3608" (without the quotation marks).
After you add trace flag 3608, follow these steps:
1.. Stop, and then restart SQL Server.
2.. Make sure that the SQL Server Agent service is not currently running.
3.. Detach the msdb database as follows:
use master
go
sp_detach_db 'msdb'
go
4.. Delete or rename the msdb mdf and ldf files.
5.. Run the INSTMSDB.SQL script
6.. Remove the -T3608 trace flag from the startup parameters box in
Enterprise Manager
7.. Stop, and then restart SQL Server.

And this from experts exchange

1. Create a new database with the same name or a different name. You will have to use a different physical file name, which is fine.
2. Stop SQL Server.
3. Rename the new data file that was created to something else (ex: add.bak to the end)
4. Rename the old data file that you want to restore to the name of the newly created file (the same name as the file you changed in the step above)
5. Start SQL Server
Now the db will still be suspect but you now have a log file.
6. Switch to emergency mode on the database as homeri14 suggested.
7. Stop and restart SQL Server.
8. If database is still in emergency mode, run sp_resetstatus '', restart SQL Server.

Also came across this

http://support.microsoft.com/default.aspx?scid=kb;EN-US;224071

optimus · 25 Jul 2007 at 22:08

Anything on Technet? http://search.support.microsoft.com??

teaboy5 · 25 Jul 2007 at 22:11

Any joy?

Dangerous · 25 Jul 2007 at 22:22

teaboy5 said:
Ok got this from google

1.. In SQL Server Enterprise Manager, right-click the server name and
click Properties.
2.. On the General tab, click Startup Parameters.
3.. Add a new parameter as "-T3608" (without the quotation marks).
After you add trace flag 3608, follow these steps:
1.. Stop, and then restart SQL Server.
2.. Make sure that the SQL Server Agent service is not currently running.
3.. Detach the msdb database as follows:
use master
go
sp_detach_db 'msdb'
go
4.. Delete or rename the msdb mdf and ldf files.
5.. Run the INSTMSDB.SQL script
6.. Remove the -T3608 trace flag from the startup parameters box in
Enterprise Manager
7.. Stop, and then restart SQL Server.

And this from experts exchange

1. Create a new database with the same name or a different name. You will have to use a different physical file name, which is fine.
2. Stop SQL Server.
3. Rename the new data file that was created to something else (ex: add.bak to the end)
4. Rename the old data file that you want to restore to the name of the newly created file (the same name as the file you changed in the step above)
5. Start SQL Server
Now the db will still be suspect but you now have a log file.
6. Switch to emergency mode on the database as homeri14 suggested.
7. Stop and restart SQL Server.
8. If database is still in emergency mode, run sp_resetstatus '', restart SQL Server.

Also came across this

http://support.microsoft.com/default.aspx?scid=kb;EN-US;224071

Theres for that - Found that my self but what im stuck on is number 3 of the following:

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/06/619304.aspx

optimus · 25 Jul 2007 at 22:25

Those intructions are for SQL 2005 (SQL Express), not 2000

teaboy5 · 25 Jul 2007 at 22:35

Dangerous said:
Theres for that - Found that my self but what im stuck on is number 3 of the following:

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/06/619304.aspx

What's it saying?

teaboy5 · 25 Jul 2007 at 22:37

Whats your e-mail addy, i want to send you something

Dangerous · 25 Jul 2007 at 22:40

Hmm tis I've managed to sort that problem how but now got to remake:

Event Type: Error
Event Source: MSSQL$MICROSOFT##SSEE
Event Category: (2)
Event ID: 17113
Date: 25/07/2007
Time: 22:38:10
User: N/A
Computer: VRSSVRTEST
Description:
The description for Event ID ( 17113 ) in Source ( MSSQL$MICROSOFT##SSEE ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: 2(error not found), C:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\MSSQL\DATA\master.mdf.
Data:
0000: d9 42 00 00 10 00 00 00 ÙB......
0008: 1b 00 00 00 56 00 52 00 ....V.R.
0010: 53 00 53 00 56 00 52 00 S.S.V.R.
0018: 54 00 45 00 53 00 54 00 T.E.S.T.
0020: 5c 00 4d 00 49 00 43 00 \.M.I.C.
0028: 52 00 4f 00 53 00 4f 00 R.O.S.O.
0030: 46 00 54 00 23 00 23 00 F.T.#.#.
0038: 53 00 53 00 45 00 45 00 S.S.E.E.
0040: 00 00 00 00 00 00 ......

What i'm doing is recovering a test server but it hasnt restored the Databases not this isnt a problem because I've got backups of these but I need working SQL/Sharepoint to be able to restore them

teaboy5 · 25 Jul 2007 at 22:42

Well i cant help there, i am never the best at fixing issues over the phone or web. I always need to be at the site.

If the server is only hosting sharepoint, can you not completely remove sql and sharepoint and quickly start again?

Dangerous · 25 Jul 2007 at 22:43

teaboy5 said:
Well i cant help there, i am never the best at fixing issues over the phone or web. I always need to be at the site.

If the server is only hosting sharepoint, can you not completely remove sql and sharepoint and quickly start again?

Because i'm testing my DR plans so need to know how to fix these things if the worse happen

teaboy5 · 25 Jul 2007 at 22:45

Dangerous said:
Because i'm testing my DR plans so need to know how to fix these things if the worse happen

Ahhhh, sorry. Well we stopped using share point in work, so i cant be of any more help.

Not that i helped in the first place

Dangerous · 25 Jul 2007 at 22:47

teaboy5 said:
Ahhhh, sorry. Well we stopped using share point in work, so i cant be of any more help.

Not that i helped in the first place

Fault would be server has been reovered into Windows but the SQL/Sharepoint DBs have not been restored correctly. Although the SQL/SP are backed up to other places getting them both working together is the hard part

teaboy5 · 25 Jul 2007 at 22:56

Dangerous said:
Fault would be server has been reovered into Windows but the SQL/Sharepoint DBs have not been restored correctly. Although the SQL/SP are backed up to other places getting them both working together is the hard part

All the best with it. Mind you, you are keen doing this at 11pm

Dangerous · 25 Jul 2007 at 23:11

teaboy5 said:
All the best with it. Mind you, you are keen doing this at 11pm

Aye but having to make room for Top Gear in HD/Heros in HD and my tea as well