SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 How to repair SQL SERVER 2005?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

neo_6053
Starting Member

24 Posts

Posted - 12/02/2008 :  21:15:11  Show Profile  Reply with Quote
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

USA
163 Posts

Posted - 12/03/2008 :  09:44:09  Show Profile  Send jholovacs a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/03/2008 :  09:49:28  Show Profile  Reply with Quote
Are you relating to Master Database or something else?
Go to Top of Page

jholovacs
Posting Yak Master

USA
163 Posts

Posted - 12/03/2008 :  12:14:41  Show Profile  Send jholovacs a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/03/2008 :  12:33:55  Show Profile  Reply with Quote
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

USA
163 Posts

Posted - 12/03/2008 :  13:29:43  Show Profile  Send jholovacs a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/03/2008 :  13:37:36  Show Profile  Reply with Quote
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 - 12/10/2008 :  02:33:28  Show Profile  Reply with Quote
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

India
14 Posts

Posted - 07/14/2009 :  05:32:38  Show Profile  Visit Davidpoul's Homepage  Reply with Quote
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

India
183 Posts

Posted - 07/14/2009 :  08:34:06  Show Profile  Reply with Quote
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

United Kingdom
530 Posts

Posted - 07/14/2009 :  09:09:45  Show Profile  Reply with Quote
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

USA
6 Posts

Posted - 04/13/2013 :  06:35:19  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.56 seconds. Powered By: Snitz Forums 2000