June 24, 2009 at 8:27 am
Did a stupid thing when I ran a Create query which did not have the correct error handling code...as a result, when createdb failed, the query went about its merry way creating about twenty empty tables in the SQL 2008 Master database with all kinds of keys, views, indexes, etc. Is there any easy or not so easy way to unwind this mess?
Thanks for any advice...
Bob
June 24, 2009 at 9:18 am
I don't have a full solution. However, I would start with a query to determine what objects have been added to the master database.
This statement will show all objects created in the last 90 days.
SELECT name, type, type_desc, create_date, DATEDIFF(d,create_date, GETDATE())
FROM master.sys.objects
WHERE DATEDIFF(d,create_date, GETDATE()) <= 90
I hope it helps.
June 24, 2009 at 1:17 pm
Carlton...thanks for reply...query does indeed list everything (all 509 objects) added...the question remains as to how do I get rid of them in some reasonable fashion...for me time is not really a constraint(no pun intended) if there is some sort of a logical approach...can I just work my way backwards thru the Create query and delete everything in reverse order while manually updating the various master tables/views. That almost looks to be a hopeless job unless one knows how master maps the info from the tables added...
Thoughts?
Thanks again....Bob
June 24, 2009 at 1:55 pm
Oops, I would step through the create script and just change it to reverse things out. You will learn your lesson for next time!!! Good luck 🙂
June 24, 2009 at 2:10 pm
Steve Newton (6/24/2009)
Oops, I would step through the create script and just change it to reverse things out. You will learn your lesson for next time!!! Good luck 🙂
Yep. I would also do the same thing.
SQL DBA.
June 24, 2009 at 2:20 pm
Thanks to both for the reply....I have one more question....will 'master' update itself if I manually delete in SMS or do I have to delete using a query(tho I think I know the answer)?
Bob
June 24, 2009 at 2:32 pm
If you use SSMS, a query is issued in the background.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 24, 2009 at 4:09 pm
Basically you could create something like this.
SELECT command = CASE
WHEN OBJECTPROPERTY(object_id, 'IsTable') = 1 THEN 'DROP TABLE '
WHEN OBJECTPROPERTY(object_id, 'IsView') = 1 THEN 'DROP VIEW '
WHEN OBJECTPROPERTY(object_id, 'IsProcedure') = 1 THEN 'DROP PROCEDURE '
ELSE '-- Unhandled Object Type '
END + name + ';'
FROM master.sys.objects
WHERE DATEDIFF(d,create_date, GETDATE()) <= 90
If you run this statement, it would create a script that should drop tables, views and procedures. It is very basic and needs refinement but it should give you a good start.
June 24, 2009 at 4:22 pm
This might get you a little closer for the list of objects.
SELECT *
FROM master.sys.objects o
WHERE NOT EXISTS (SELECT 1 FROM master.sys.system_objects so WHERE so.object_id = o.object_id) /* Exclude system objects */
AND USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) 'sys' /* Exclude items owned by sys */
AND type not in ('SQ', 'IT') /* Exclued type Service Queue and Internal Table */
June 24, 2009 at 6:42 pm
This is one reason that I bear the pain of having IMPLICIT_TRANSACTIONs turned on in my SSMS settings.
It is nice to be able to issue a ROLLBACK after something like this.
If you are more disciplined than I am, you could try remembering to BEGIN TRAN before making changes.
Yes I know it's easy to say this after the event 🙂
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 26, 2009 at 5:12 pm
Thanks to all for the rapid responses....basically, took a little from a number of them and cobbled together a solution....everything is back to normal...
Thanks again....
Bob
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply