by mistake one Script on Master

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » by mistake one Script on Master

by mistake one Script on Master

Rate Topic

Display Mode

Topic Options

Author

Message

mayank jarsaniya

Posted Monday, June 01, 2009 1:35 AM

Old Hand

Group: General Forum Members
Last Login: Monday, December 26, 2011 1:28 PM
Points: 346, Visits: 401

I have maintained 20 database on one server.

To update all these 20 database I have created one script.

scripts contains create table and Stored Procedures statements.

By mistake I have run this script on Master Database.

Any way to easily DROP all this un neccesary objects from master database.

Post #726452

Randolph West

Posted Monday, June 01, 2009 2:06 AM

Old Hand

Group: General Forum Members
Last Login: Monday, June 10, 2013 3:03 AM
Points: 317, Visits: 368

View > Object Explorer Details - select the objects you want to delete, right-click, and delete.

In future, make sure you have the right execution context before running a script. Next time it might be a DROP TABLE and you're running on Production.

Post #726458

Mauve

Posted Tuesday, June 02, 2009 11:10 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:16 AM
Points: 1,150, Visits: 1,859

Put a safety check at the beginning of your script. We have to do the same thing (i.e., upgrade 100+ databases with a new release). To avoid accidental schema creation or schema upgrade into a "system" database the following is at the start of my script:

SELECT CAST('Target Database is: ' + DB_NAME() AS VARCHAR(70));
GO
IF UPPER(DB_NAME()) IN ('MASTER', 'MODEL', 'MSDB')
  RAISERROR('Invalid database for schema creation.', 16, 1);
GO

(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.

Post #727599

Randolph West

Posted Tuesday, June 02, 2009 12:58 PM

Old Hand

Group: General Forum Members
Last Login: Monday, June 10, 2013 3:03 AM
Points: 317, Visits: 368

Nice tip, John! I'm going to use it myself Smile

Post #727661

Mauve

Posted Tuesday, June 02, 2009 1:09 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:16 AM
Points: 1,150, Visits: 1,859

Randolph Potter (6/2/2009)

Nice tip, John! I'm going to use it myself Smile

You're welcome. It is one of numerous defensive measures that I've implemented. Usually resulting from the school of "hard knocks".

Just make sure that you use the -b (on error batch abort) switch when you run your script using SQLCMD.

(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.

Post #727669

« Prev Topic | Next Topic »

Permissions