How to delete a row from master.dbo.sysdatabases? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2008 Forums

SQL Server Administration (2008)

How to delete a row from master.dbo.sysdatabases?

Author

Topic

Martyn123
Starting Member

29 Posts

Posted - 2011-01-27 : 05:36:46

Hi,

I have one requirement , I need to delete a row by the name 'msdb' from master.dbo.sysdatabases.I executed the following code:

SP_CONFIGURE 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

The above query is getting executed perfectly but when I try to delete a row from from master.dbo.sysdatabases I am getting the following error:

Msg 259, Level 16, State 1, Line 2
Ad hoc updates to system catalogs are not allowed.

Please help me out in achieving this requirement.

Thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-27 : 07:33:58

You don't delete rows from the system tables. Good way to really break a SQL server permanently. If you did somehow manage to delete the MSDB entry you'd probably have to reinstall the SQL instance after that. MSDB is a system DB and essential for SQL Agent (and a few other things)

Now, why do you feel that you want to do this?

--
Gail Shaw
SQL Server MVP

Martyn123
Starting Member

29 Posts

Posted - 2011-01-27 : 07:54:55

Hi GilaMonster,

Actually I was trying to recreate msdb database so I am running the script "instmsdb.sql". This script checks the master.dbo.sysdatabases system table for the entry "msdb", and in master.dbo.sysdatabases there was an entry for msdb even though I deleted msdb's mdf and ldf files.

So I was trying to manually delete msdb entry from master.dbo.sysdatabases.

Thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-27 : 07:59:53

I don't think that deleting the files without doing anything with the database is the correct way to rebuild MSDB. If I recall you're supposed to start SQL with certain flags then delete MSDB from within SQL. Please check books Online or whereever that script came from for the correct way to proceed.

Again, deleting from the system tables is the fastest way to damage a SQL instance beyond repair. Additionally, sysdatabases isn't even a table.

I'm guessing you don't have a backup of master or msdb?

--
Gail Shaw
SQL Server MVP

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-27 : 08:05:59

Oh wow.

Hopefully you have a good backup of everything, including a non suspect version of msdb from before your efforts. Not sure what you can do here, but I did find this http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/06/619304.aspx. I am not sure it will work in your case...but you may have already broken the entire installation.

Poor planning on your part does not constitute an emergency on my part.

Martyn123
Starting Member

29 Posts

Posted - 2011-01-27 : 08:43:31

Thanks everyone for replying so early!!!

I have backups of msdb and master as well I was creating the failure situations on the test server so that I can restore from the backup and by any other method if possible.

I have one more query which is troubling me now:

I want to restore the model database from the latest backup (if model db gets corrupt).I am restarting the sql server using TRACE FLAG 3608 but when I am trying to restore the model from backup I could not and I am getting the following error:

SQLCMD
1> RESTORE DATABASE model FROM DISK='C:\model.bak' WITH REPLACE
2> GO
HResult 0x6D, Level 16, State 1
Shared Memory Provider: The pipe has been ended.

Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Communication link fail
ure.

Please help me in resolving this issue.

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2011-01-27 : 11:28:55

You should not be touching System Database as it is very critical...Check for KB Article where they have good explanation for backup/restore of system database.

Peter99
Constraint Violating Yak Guru

498 Posts

Posted - 2011-01-27 : 14:45:18

for DR you should make sure new server has same configuration as previous one (same sql server version, sp, build, drive letters). After installing sql on new server, stop sql agent on new server and restore msdb from your backup. Hope that will work.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources