'sysobjects' deleted by accident

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

General SQL Server Forums

New to SQL Server Programming

'sysobjects' deleted by accident

Author

Topic

sundaram_r_1984
Starting Member

11 Posts

Posted - 2008-01-21 : 07:04:38

HI friends,

i have deleted the 'sysobjects' table by accident

. then follows the problem.
1. default value constraint not working
2. auto increment constraint not working

is that because of this sysobjects problem.
help me

out to fix this problem...

hav fun, njoy
sundaram

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-21 : 07:13:56

Restore the latest backup and use POINT-IN-TIME option possible.
The real question is, why do you manipulate system tables like this?

E 12°55'05.25"
N 56°04'39.16"

sundaram_r_1984
Starting Member

11 Posts

Posted - 2008-01-21 : 07:24:22

thanks for giving a solution... Mr.Peso

ya the db restoration process is in progress.... (like, its a remote server.. the server people are lazy.. they will eat more time).
It all happened while instead of deleting an ordinary table... my stupid brain commanded me to type 'sysobjects' instead of that table name.
Also i dont know about point-in-time option... let me check that in google...

one more thing... is this problem serious one ??... this is the first time im encountering this prob...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-21 : 07:28:52

Yes, this is serious.

E 12°55'05.25"
N 56°04'39.16"

sundaram_r_1984
Starting Member

11 Posts

Posted - 2008-01-21 : 07:36:06

is it possible to create the 'sysobjects' table by our own.. any ways...?? is it worth to transfer the 'sysobjects' table for this database in my local machine to the same db in the server. Or any other solutions??

sundaram_r_1984
Starting Member

11 Posts

Posted - 2008-01-21 : 07:38:55

First of all i thank Mr. Peso for considering me for a reply... Also i request more people, more solutions... coz tomorrow i have to show the demo of this web application to my client... sorry for bugging once again..

georgev
Posting Yak Master

122 Posts

Posted - 2008-01-21 : 07:47:55

Have you considered scripting out the database into a new one? Hopefully you've not touched the model database and everything should go alreet :)

George
<3Engaged!

sundaram_r_1984
Starting Member

11 Posts

Posted - 2008-01-21 : 07:57:25

thanks Mr.geo for helping me..

ya i have generated the sql script for the whole database and then i executed all those things manually. Every thing is perfect except the following problem.
1. default value constraint not working(default values for certain fields at the time of inserting a new record are not working)
2. auto increment constraint not working(auto incrementing a primary key field is also not functioning while at the time of inserting a new record into the table).
I think all these things are because of the blunber that i have done by deleting the 'sysobjects' table

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-21 : 08:20:42

script the database on your development server that is untouched and recreate the database on your presentation server.
i think that's what georgev is suggesting.

provided that your db on the dev server is ok you should be able to recreate the db with no problem.
if you have any data on your presentation server you can extract the data into flat files with BCP and then import it back in with ease.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-21 : 15:10:18

Scripting db doesn't work in this case. First, sql doesn't script system objects. Second, it doesn't solve the problem even you recreate that table since data are still missing.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-21 : 15:23:41

oh...he dropped the table itself?
i understood that as that he deleted the data in it.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2008-01-21 : 15:46:27

HOW???

You CAN'T ACCIDENTALLY drop sysobjects.

because 1st you have to allow direct updates to system catalogs. even then i'm not sure u can do it.

USE Master;
GO
create database rrr;
GO
use rrr;
go
drop table sysobjects;
GO

Server: Msg 3708, Level 16, State 1, Line 1
Cannot drop the table 'sysobjects' because it is a system table.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-21 : 15:59:23

exactly.
beacuse if you recreate the db from scripts the sysobjects should repopulate.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

sundaram_r_1984
Starting Member

11 Posts

Posted - 2008-01-21 : 23:52:09

just i typed this query....
"delete from sysobjects"
no more sysobjects in my database after that....

sundaram_r_1984
Starting Member

11 Posts

Posted - 2008-01-22 : 01:34:06

i really thank all you guys for helping me. my problem is sorted out. we restored the backup file in the server. i dont know how, but the 'sysobjects' table automatically got created. now everything is fine and working... thanks Mr. spirit, mr. russell, mr.rmiao, mr.georgev, mr.peso. thank u all and the sqlteam team.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-22 : 09:12:11

quote:
Originally posted by sundaram_r_1984

my problem is sorted out. we restored the backup file in the server. i dont know how, but the 'sysobjects' table automatically got created.

When you backup the database, sysobjects is also being backup. So when you restored the database from your backup copy, it will be there.

KH
[spoiler]Time is always against us[/spoiler]

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2008-01-22 : 10:55:23

u should disallow updates to system tables at this point, to hopefully prevent future incidents

EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO

then restart sql service.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources