Help getting rid of sql default tables
-
Ryan,
I agree whole heartedly with John. While what you did is highly unusual I've certainly seen and done some strange things as well.
Just as a quick overview for you SQL Server stores everything in its own tables. For example the Master database contains the general overview of all of the other databases and system settings. The sys tables in each database contain the information on every object in that database. The database MSDB contains job information (among other things).
Unfortunatly the only thing you are going to be able to do at this point is to restore a backup and hope that the sys tables that are there can be used in your orriginal database.
Ken
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
-
I'm just curious here. What script did you run that deleted those tables???
I tried reproducing the issue on a test DB and I couldn't drop the System tables. The best I could do was delete the data. In which case it mwans you only need a restore of the db, then reinsert the data into the tables. The make sure system updates are FORBIDDEN. That way a mess like this won't be able to happen again.
USE Pubs
GO
DROP TABLE dbo.SysComments
--Cannot drop the table 'dbo.SysComments' because it is a system table.
GO
DELETE dbo.SysComments
--Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.
GO
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO
DROP TABLE dbo.SysComments
--Cannot drop the table 'dbo.SysComments' because it is a system table.
GO
DELETE dbo.SysComments
--113 rows affected
GO
EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO
-
Before jumping into doing a restore, are you doing log shipping or do you have a standby that you may be able to recover the tables from? Please check to see if you have in fact deleted system tables. Knowing these bits of information may give you some options...
-
Adam,
I'm just curious here but why would he need to re-install SQL Server if he's only effected a user table? I can see if he were to have deleted his Master DB, but I don't recall this happening. Can you clarify your advice please?
-
Of course you could get out of your way and change the xtype of the system table... then you would be able to drop them... but I would assume that the other guy would not have scripted something like this unless it was a virus/bomb/joke of some sort.
I'd still like to see that script you ran though.
-
I would hardly call that advice.
My gut is also telling me that this is likely a joke, but this solution could certainly help someone someday, so I'll play along and take this seriously. This is also a great opportunity to solve a brand new problem (for me anyways).
-
I really can’t believe that the original post is anything but a troll, and not a very good one.
I doubt that anyone who was really that clueless could actually delete system tables, since that requires a certain level of knowledge to be able to do.
It wasn’t a bad touch calling himself a Senior DBA working for a government agency, but a good troll has to be believable on the face of it to really suck people in, and it just didn’t have that believability.
I give it a 3 out of 10.
-
I agree Remi, the likeliness of this being a joke is quite high and Adam’s advice is not advice at all. I guess I just don’t agree with jumping all over a guy who may be in need of help. I think your ‘play along’ stance is the best to take in this case unless it is blatantly obvious that it is a joke. This may help someone else, it not the OP. Getting on someone’s case for posting what may seem silly to the rest of us is not very professional and it seems like it has been going on a lot lately, even from many posters who’s opinions and advice I value greatly.
Thanks for helping out here with constructive posts as you always do!
-
HTH. I've been on both sides of the fence in my career... it's greener here
. -
If this wasn’t a troll, I can't see a possible constructive response, other than to say, “Stop, don’t do anything else! Find someone who knows what they are doing to help you.”
-
Agreed, this reeks of troll. All the same, I usually try to be helpful (with varying degrees of effectiveness); I'll take down my sarcastic comments from the other thread in the name of professionalism.
-
In regards to that option, what do you think a user needeing help woul dbe doing on a forum like this one?
-
In the spirit of belief in my fellow man and the idea that any help given is never wasted, I'd still like to see the script run by the OP so the extent of the damage can be made evident.
In the best of all possible worlds, he's just done this to MODEL and he can re-install it from install scripts or a backup.
He should probably not re-start the server, since I'd bet the automatic recovery will fail and he'll end up with a suspect db.
And then again, I might be wrong ...
David Webb -
Be glad your name is not Ryan Moats...
A.J.
DBA with an attitude -
My point being that if the situation is as he described, he shouldn't be doing anything more, and certainly not trying to apply any advice from posts on this forum, because he simply does not have the knowledge to be anything but dangerous.
Find someone else to do whatever they are trying to do who has the necessary skills to take it on.
Viewing 15 posts - 16 through 30 (of 61 total)
You must be logged in to reply to this topic. Login to reply