Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 How to repair SQL SERVER 2005?

Author  Topic 

neo_6053
Starting Member

24 Posts

Posted - 2008-12-02 : 21:15:11
i accidentally delete some data in master table, and now lots of problems occurred. 1 of the problem is i cant right click and open the properties page anymore.

So, i want to repair it. But how can i do that? i run the setup again but no repair button. Besides, if i really repaired the SQL, will i lost my service pack too?

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-12-03 : 09:44:09
ouch... yeah be careful in the master db.

My first instinct would be to reinstall SQL Server and reapply the Service packs. It may not be necessary, but I would not trust that server again until I had a fresh install.

That being said, there's usually a bunch of good reasons not to do that, especially if you can't afford to have the server down for so long.

You can recreate the master database but it's a pain. My understanding (and keep in mind I've never done this before) is you shut down the server, move your old master db and log files someplace out of the way, and start a command line interface. Navigate to the directory with the setup.exe file that installs SQL Server, and type:

start /wait setup.exe /qn INSTANCENAME="<your instance here - default is MSSQLSERVER, named would look like MSSQL$INSTANCENAME>" REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD="<put your sa password here>"

This SHOULD rebuild your master database. You will need to create all your default settings, and reattach your user databases.

...and BACK EVERYTHING UP REGULARLY. Restoring a database (even master) is much easier.

I shamelessly summarized from the following article:

http://sql-articles.com/index.php?page=articles/rebuildsysdb.html


___________________________
Geek At Large
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-03 : 09:49:28
Are you relating to Master Database or something else?
Go to Top of Page

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-12-03 : 12:14:41
My assumption is that he is talking about the master database... to date I do not know of a "master" table in SQL Server.

___________________________
Geek At Large
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-03 : 12:33:55
quote:
Originally posted by jholovacs

My assumption is that he is talking about the master database... to date I do not know of a "master" table in SQL Server.

___________________________
Geek At Large


I asked just to make sure because OP said ' I accidentally delete some data in master table'. You can't accidently delete it. you have change configuration before you do that in SQL 2005.I am not sure even delete is supported. I can blindly answer without knowing real issue.
Go to Top of Page

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-12-03 : 13:29:43
I'll take your word for it; I have never tried to delete data in the master database so I don't know how difficult it is.

___________________________
SELECT TOP 1 w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-03 : 13:37:36
quote:
Originally posted by jholovacs

I'll take your word for it; I have never tried to delete data in the master database so I don't know how difficult it is.

___________________________
SELECT TOP 1 w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC




.You need to put more indexes on it to make faster.
Go to Top of Page

neo_6053
Starting Member

24 Posts

Posted - 2008-12-10 : 02:33:28
actually i;m facing the same problem as this post .. http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=404496&SiteID=1

so, here's the solution

The cause is that the system table spt_values has been deleted. But don't know who did it and why that could happen.
Solution: run u_tables.sql from your instance's install directory. e.g: C:\Program Files\Microsoft SQL Server\MSSQL$SQL2005\MSSQL.1\MSSQL\Install\u_tables.sql
Go to Top of Page

Davidpoul
Starting Member

14 Posts

Posted - 2009-07-14 : 05:32:38
If you want to repair your sql database and recover your deleted data then you need the help of third party sql recovery software. These software use powerful algorithm to scan corrupt database and repair them. You can download it from http://www.mssqldatabaserecovery.com/

David Poul
Go to Top of Page

ahmad.osama
Posting Yak Master

183 Posts

Posted - 2009-07-14 : 08:34:06
quote:
Originally posted by sodeep

quote:
Originally posted by jholovacs

My assumption is that he is talking about the master database... to date I do not know of a "master" table in SQL Server.

___________________________
Geek At Large


I asked just to make sure because OP said ' I accidentally delete some data in master table'. You can't accidently delete it. you have change configuration before you do that in SQL 2005.I am not sure even delete is supported. I can blindly answer without knowing real issue.



well.. there are certain tables in master database on which delete works.. eg..spt_values,spt_monitor
Go to Top of Page

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-07-14 : 09:09:45
SO if you can't restore the database i would look are reloading the master database on from the setup cd.
Go to Top of Page

drdesouza
Starting Member

6 Posts

Posted - 2013-04-13 : 06:35:19
If you accidently delete some data from your table or received error messages when you open your SQL table, in that situation you can use third party tool that can recover your deleted tables. I think this software will help you as much as possible and recover your deleted tables which are not overwritten. Get more information from here: unspammed
Go to Top of Page
   

- Advertisement -