Cleaning User Stuff from the master Database

This is an upgrade to a script I wrote a long time ago. It's handy when you find a system whose master database is somehow full of user objects. (I know that none of us ever make that mistake...)

The biggest improvement is that this script now only generates DROP statement, it doesn't execute them. You have to do that yourself, and you should be sure to back up your master database (you do that on a regular basis anyway, right?), and be darn sure to read all the comments, and of course examine the generated T-SQL code, before executing it. Use at your own risk!

-------------------------------------------------------------------------------
-- Drops all user objects that have been accidentally created in 'master'.     
-- This script erases everything in the master database that is not Microsoft- 
-- shipped.  HOWEVER, be careful: some user objects BELONG in the master       
-- database (database triggers, security objects, for example). Backing up the 
-- master database first would be a prudent step.  Use at your own risk.       
-- NOTE: This script only generates the T-SQL code required.  You have to copy 
-- it and do the actual execution yourself.  You may have to run this script   
-- several times due to dependencies; you may also get error messages about    
-- objects that cannot be found, also due to dependencies.                     
-------------------------------------------------------------------------------

USE master

DECLARE @Sql            NVARCHAR(MAX)       SET @Sql            = ''
DECLARE @ObjectName     sysname             SET @ObjectName     = ''
DECLARE @ConstraintName sysname             SET @ConstraintName = ''
DECLARE @TableName      sysname             SET @TableName      = ''
DECLARE @TypeDesc       NVARCHAR(4000)      SET @TypeDesc       = ''
DECLARE @Type           NVARCHAR(1024)      SET @Type           = ''
DECLARE @Score          INT                 SET @Score          = 0
DECLARE @ObjectID       INT                 SET @ObjectID       = 0

DECLARE @ENumber        INT                 
DECLARE @ESeverity      INT                 
DECLARE @EState         INT                 
DECLARE @EProcedure     NVARCHAR(126)       
DECLARE @ELine          INT                 
DECLARE @EMessageRecv   NVARCHAR(2048)      
DECLARE @EMessageSent   NVARCHAR(440)       

RAISERROR('-- Execute this code only *AFTER* you have examined it throughly!', 10, 1) WITH NOWAIT
RAISERROR('USE master', 10, 1) WITH NOWAIT


-------------------------------------------------------------------------------
-- Most objects follow this pattern.                                           
-------------------------------------------------------------------------------

DECLARE cur CURSOR DYNAMIC FOR
    SELECT name,
           type_desc
      FROM sys.objects
     WHERE is_ms_shipped = 0
     ORDER BY type_desc, name

OPEN cur
FETCH FIRST FROM cur INTO @ObjectName, @TypeDesc

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        IF @TypeDesc = 'VIEW'
        BEGIN
            SET @Sql = 'DROP VIEW ' + QUOTENAME(@ObjectName)
            RAISERROR('%s', 10, 1, @Sql) WITH NOWAIT    
        END
        ELSE IF @TypeDesc NOT LIKE '%_CONSTRAINT' ESCAPE '$'
        BEGIN
            SET @Type  = REVERSE(@TypeDesc)
            SET @Score = CHARINDEX('_', @Type)
            SET @Type  = SUBSTRING(@Type, 0, @Score)
            SET @Type  = REVERSE(@Type)
            SET @Sql   = 'DROP ' + @Type + ' ' + QUOTENAME(@ObjectName)
            RAISERROR('%s', 10, 1, @Sql) WITH NOWAIT    
        END
    END TRY
    BEGIN CATCH
        SET @ENumber      = ISNULL(ERROR_NUMBER(),          -1)
        SET @ESeverity    = ISNULL(ERROR_SEVERITY(),        -1)
        SET @EState       = ISNULL(ERROR_STATE(),            0)    IF @EState = 0 SET @EState = 42
        SET @EProcedure   = ISNULL(ERROR_PROCEDURE(), N'{N/A}')
        SET @ELine        = ISNULL(ERROR_LINE(),            -1)
        SET @EMessageRecv = ISNULL(ERROR_MESSAGE(),        N'')
        SET @EMessageSent = N''

        IF ERROR_PROCEDURE() IS NOT NULL   SET @EMessageSent = N'Error %d, Level %d, State %d, Procedure %s, Line %d, Message: '
        SET @EMessageSent = @EMessageSent + ERROR_MESSAGE()
        RAISERROR(@EMessageSent, 10 /* @ESeverity */ , @EState, @ENumber, @ESeverity, @EState, @EProcedure, @ELine) WITH LOG
    END CATCH

    FETCH NEXT FROM cur INTO @ObjectName, @TypeDesc
END

CLOSE cur
DEALLOCATE cur


-------------------------------------------------------------------------------
-- Constraints are the only exception to the pattern above.                    
-------------------------------------------------------------------------------

DECLARE cur CURSOR DYNAMIC FOR
    SELECT name,
           type_desc,
           OBJECT_NAME(parent_object_id)
      FROM sys.objects
     WHERE is_ms_shipped = 0
       AND type_desc LIKE '%_CONSTRAINT' ESCAPE '$'
  ORDER BY type_desc, name

OPEN cur    
FETCH FIRST FROM cur INTO @ConstraintName, @TypeDesc, @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        SET @Sql = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' DROP CONSTRAINT ' + QUOTENAME(@ConstraintName)
        RAISERROR('%s', 10, 1, @Sql) WITH NOWAIT    
    END TRY
    BEGIN CATCH
        SET @ENumber      = ISNULL(ERROR_NUMBER(),          -1)
        SET @ESeverity    = ISNULL(ERROR_SEVERITY(),        -1)
        SET @EState       = ISNULL(ERROR_STATE(),            0)    IF @EState = 0 SET @EState = 42
        SET @EProcedure   = ISNULL(ERROR_PROCEDURE(), N'{N/A}')
        SET @ELine        = ISNULL(ERROR_LINE(),            -1)
        SET @EMessageRecv = ISNULL(ERROR_MESSAGE(),        N'')
        SET @EMessageSent = N''

        IF ERROR_PROCEDURE() IS NOT NULL   SET @EMessageSent = N'Error %d, Level %d, State %d, Procedure %s, Line %d, Message: '
        SET @EMessageSent = @EMessageSent + ERROR_MESSAGE()
        RAISERROR(@EMessageSent, 10 /* @ESeverity */ , @EState, @ENumber, @ESeverity, @EState, @EProcedure, @ELine) WITH LOG
    END CATCH

    FETCH NEXT FROM cur INTO @ConstraintName, @TypeDesc, @TableName
END

CLOSE cur
DEALLOCATE cur


-------------------------------------------------------------------------------
-- Done.                                                                       
-------------------------------------------------------------------------------

RAISERROR('-- Execute this code only *AFTER* you have examined it throughly!', 10, 1) WITH NOWAIT

Deleting User Objects in Master Database

This script erases everything in the master database that is not Microsoft-shipped. Be careful: some user objects belong in the master database (database triggers, security objects, for example). Backing up the master database first would be a prudent step.

-- Drops all user objects that have been accidentally created in the master database.
USE master

DECLARE @sName     sysname
DECLARE @sTypeDesc NVARCHAR(1024)
DECLARE @sType     NVARCHAR(1024)
DECLARE @nScore    INT
DECLARE @sSql      NVARCHAR(1024)

DECLARE TheCursor CURSOR FOR
 SELECT name, type_desc
   FROM sys.objects
  WHERE is_ms_shipped = 0

OPEN TheCursor

FETCH NEXT FROM TheCursor
 INTO @sName, @sTypeDesc

WHILE @@FETCH_STATUS = 0
BEGIN
 SET @sType  = REVERSE(@sTypeDesc)
 SET @nScore = CHARINDEX('_', @sType)
 SET @sType  = SUBSTRING(@sType, 0, @nScore)
 SET @sType  = REVERSE(@sType)

 SET @sSql = 'DROP ' + @sType + ' ' + @sName
 RAISERROR('%s', 10, 1, @sSql) WITH NOWAIT 
 EXEC sp_sqlexec @sSql
 
 FETCH NEXT FROM TheCursor
  INTO @sName, @sTypeDesc
END

CLOSE TheCursor
DEALLOCATE TheCursor

PRINT 'Done.'

(Practically) Fool-Proof Backups

A script and batch file that practically anyone can run, no matter how non-technical. The batch file prompts you for backup path, server, and instance, and then calls the SQL script.

: -----------------------------------------------------------------------------
: Prompts user for info and calls the SQL script to do the backup.             
: -----------------------------------------------------------------------------
: Copyright 2010  Larry Leonard, Definitive Solutions Inc.                     
:                 http://www.DefinitiveSolutions.com                           
:                                                                              
: Copying and distribution of this file, with or without modification, are     
: permitted in any medium without royalty provided the copyright notice and    
: this notice are preserved.  This file is offered as-is, without any          
: warranty.                                                                    
: -----------------------------------------------------------------------------

@echo off
prompt $g
@echo.
@echo %date% %time%
@echo.


: -----------------------------------------------------------------------------
: Display free disk space.
: -----------------------------------------------------------------------------

for /f "tokens=3" %%a in ('dir ^ find /i "bytes free"') do set freeMB=%%a
set freeMB=%freeMB:~0,-8%
@echo Disk Space Free: %freeMB% MB
@echo.


: -----------------------------------------------------------------------------
: Prompt the user.
: -----------------------------------------------------------------------------

set /p   TargetServerAndInstance=Server and Instance  ("Enter" for default)?
if     "%TargetServerAndInstance%"==""  set TargetServerAndInstance="UNICORN\SQL2008EXPRESS"

set /p   BackupFolder            =Backup Folder        ("Enter" for default)?
if     "%BackupFolder%"           ==""  set BackupFolder="C:\TEMP"


: -----------------------------------------------------------------------------
: Build command string.  For information on sqlcmd.exe, see:
:    http://msdn.microsoft.com/en-us/library/ms162773(SQL.90).aspx
:
:    -a    Requests a packet of a different size
:    -S    Server\instance
:    -i    Input script name
:    -o    Output script name
:    -w    Column width
:    -m    All headers are returned with messages
:    -b    sqlcmd exits and returns a DOS ERRORLEVEL value when an error occurs
:    -r    Redirects the error message output to the screen
:    -v    Declare $(variable_name)
:    -V    Lowest severity level sqlcmd reports
:
: -----------------------------------------------------------------------------

set SqlCmdCommand=sqlcmd.exe
if not "%TargetServerAndInstance%"=="" set SqlCmdCommand=sqlcmd.exe -S %TargetServerAndInstance%
set SqlCmdCommand=%SqlCmdCommand% -r 1 -b -m-1 -V 1 -a 32767 -w 2000
set SqlCmdCommand=%SqlCmdCommand% -i BackupAllDatabases.sql
set SqlCmdCommand=%SqlCmdCommand% -o BackupAllDatabases.txt
set SqlCmdCommand=%SqlCmdCommand% -v BackupFolder=%BackupFolder%


: -----------------------------------------------------------------------------
: Call the backup SQL script.
: -----------------------------------------------------------------------------

if not exist "@BackupFolder" mkdir "@BackupFolder"
if errorlevel 1 goto l_error

@echo on
%SqlCmdCommand%
@echo off
@echo.
if errorlevel 1 goto l_error


: -----------------------------------------------------------------------------
: Done.
: -----------------------------------------------------------------------------

goto l_done

:l_error
@echo.
@echo                             *** AN ERROR HAS OCCURRED! ***

:l_done:
@echo.
@echo %date% %time%
@echo.

Notepad.exe BackupAllDatabases.txt

pause

@echo.
prompt $p$g

The SQL script backs up all user databases and their logs, and the master and msdb system databases.

-------------------------------------------------------------------------------
-- Called by BackupAllDatabases.bat to backup all databases and their logs.
-- The path set in @sBackupFolder must exist - this script will not create it.
-------------------------------------------------------------------------------
-- Copyright 2010  Larry Leonard, Definitive Solutions Inc.               
--                 http://www.DefinitiveSolutions.com                     
--                                                                        
-- Copying and distribution of this file, with or without modification, are
-- permitted in any medium without royalty provided the copyright notice and
-- this notice are preserved. This file is offered as-is, without any warranty.
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
-- MUST be set as shown to support indexes on computed columns, indexed views.
SET ANSI_NULLS ON                                 -- Deprecated: leave set ON. 
SET ANSI_PADDING ON                               -- Deprecated: leave set ON. 
SET ANSI_WARNINGS ON                              -- No trailing blanks saved. 
SET ARITHABORT ON                                 -- Math failure not ignored. 
SET CONCAT_NULL_YIELDS_NULL ON                    -- NULL plus string is NULL. 
SET NUMERIC_ROUNDABORT OFF                        -- Allows loss of precision. 
SET QUOTED_IDENTIFIER ON                          -- Allows reserved keywords. 

-- These are not, strictly speaking, required, but are generally good practice.
SET NOCOUNT ON                                    -- Minimize network traffic. 
SET ROWCOUNT 0                                    -- Reset in case it got set. 
SET XACT_ABORT ON                                 -- Make transactions behave. 
-------------------------------------------------------------------------------


-- These are the only variables which you are required to provide a value for. 
DECLARE @sBackupFolder NVARCHAR(260)
SET     @sBackupFolder = 'C:\Temp'

-- Create a file-system friendly date. 
DECLARE @sDate NVARCHAR(50)
SET @sDate = CONVERT(NVARCHAR, CAST(GETDATE() AS SMALLDATETIME), 126)
SET @sDate = REPLACE(@sDate, 'T', '_')
SET @sDate = REPLACE(@sDate, ':', '-')

-- Create the commands. 
DECLARE @sSql NVARCHAR(4000)

SET @sSql =''
 + 'IF  ''?'' NOT IN (''model'', ''tempdb'') '
 + 'AND ''?'' NOT LIKE ''ReportServer$%'' '
 + ''
 + 'BEGIN '
 + ' RAISERROR(''Starting backup of database ?.'', 10, 1) WITH NOWAIT '
 + ''
 + ' BACKUP DATABASE [?] '
 + '   TO DISK = ''' + @sBackupFolder + '\?_dat_' + @sDate + '.bak '''
 + '   WITH '
 + '   NOFORMAT, '
 + '   NOINIT, '
 + '   NAME = ''? - Full Database Backup - ' + @sDate + ''', '
 + '   SKIP, '
 + '   STATS = 10; '
 + ''
 + ' IF ''?'' NOT IN (''master'', ''msdb'') '
 + ' BEGIN '
 + ' BACKUP LOG [?] '
 + '    TO DISK = ''' + @sBackupFolder + '\?_log_' + @sDate + '.bak '''
 + '    WITH '
 + '    NOFORMAT, '
 + '    NOINIT, '
 + '    NAME = ''? - Full Log Backup - ' + @sDate + ''', '
 + '    SKIP, '
 + '    STATS = 10; '
 + ' END '
 + ''
 + 'END; '

-- Run the commands for each database. 
USE master
EXEC sp_MsForEachDb @sSql

-- Done.
GO

Pretty much anyone should be able to run this.

Is the current database a system one?

I finally got tired of accidentally polluting the master database with my tables and stored procedures: it can be very confusing when you're debugging something and you don't realize you're in the master database. I needed a simple check that the current database isn't one of the four system ones. Seems easy enough. But when I tried to find a way to do it in SQL Server 2005, this was the best I could find:

IF DB_NAME() IN ('master', 'tempdb', 'model', 'msdb') RAISERROR('   $$$ SYSTEM DB $$$', 20, 1) WITH NOWAIT, LOG

This is lame. I have to explicitly list them, and by name? I was really expecting something like sys.databases.is_ms_shipped, but no such luck. Seems like this would be a common need. (The error is at level 20 so that the script stops in its tracks, before it can do any damage.)