July 30, 2008 at 9:31 am
There are times I hate Microsoft Management Studio. Especially when connecting to a server, and the oh you want to be in your default database (no I don't).
Well my fellow DBA was following our standard procedure to create a database for a new customer based upon the structure of another customer. So the script is up in a query window in MS SQL Server Management studio and the connection had defaulted back to their default database of Master. they run the script and realize that they just created 300+ tables, views, users, etc in master and they need to clean it out.
In cleaning out the database, they inadvertently dropped the following 10 tables that are suppossed to be system tables, but apparently are defined as system table by Microsoft. Ooops!
The tables are from Master:
dbo.MSreplication_options
dbo.spt_datatype_info
dbo.spt_datatype_info_ext
dbo.spt_fallback_db
dbo.spt_fallback_dev
dbo.spt_fallback_usg
dbo.spt_provider_types
dbo.spt_server_info
dbo.spt_values
Well we try our good old redgate SQL Compare (V5 and V6) and it doesn't see the system tables.
We try Enterprise Manager, but it can't connect.
So we manually script from Management Studio the tables and create the tables, but now they show up as user tables. The systems do start working, and I also copy in the data to be safe.
The big questions now is What will happen when we-restart the system (it's production so we have to be careful) is there a better way to repair this?
We do have backup of master from just before the delete, bu tI know restoring master is more complicated, and definitely causes a downtime, but if that is what is needed, I'll do it.
Any thoughts, experience, etc?
Thanks in advance,
Brian
July 30, 2008 at 10:32 am
Do you have backup of master database?
If so, could you restore as other database and export/import data from there?
July 30, 2008 at 10:54 am
I don't know whether this will work or not:
Can you try changing the table type from User to System by making use of following stored procedure:
In Sql Server 2000--master.dbo.sp_MS_upd_sysobj_category
In Sql Server 2005--sys.sp_MS_marksystemobject
Manu
July 30, 2008 at 11:44 am
MANU (7/30/2008)
I don't know whether this will work or not:Can you try changing the table type from User to System by making use of following stored procedure:
In Sql Server 2000--master.dbo.sp_MS_upd_sysobj_category
In Sql Server 2005--sys.sp_MS_marksystemobject
Manu
By Using this command the with the paramenter
@pSeqMode = 1 The tables I created were System
and then I re-ran with @pSeqMode = 2 to return to normal operation.
July 30, 2008 at 12:39 pm
FYI, It appears to have worked and I was able to insert the missing data from a restored to a different database copy of the master tables.
It probably wouldn't have been hard to do the restore, but I like avoiding downtimes.
Brian
August 6, 2008 at 10:04 am
Thank you Brian for your feedback. I thought it might work but I had not tried any time. It is very glad to know it works.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply