Friday, April 9, 2010

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.

Adding a Clustered Index - Carefully

This script is useful if you need to add a Primary Key / clustered index on a table, but cannot know if the table already has a clustered index on another column, or if the column you're trying to cluster already has a non-clustered index on it.

-------------------------------------------------------------------------------
-- Constraint_Add_PK_Clustered.sql - Very careful way to add a Primary Key     
--                 backed by a clustered index.  Checks for an existing        
--                 clustered index that was created by a PRIMARY KEY or UNIQUE 
--                 constraint. Checks whether the column is already in use in  
--                 an existing index.                                          
--                                                                             
-- Note: In SSMS, press Ctrl-Shift-M to pop a dialog box for entering values.  
-------------------------------------------------------------------------------
-- 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.
-------------------------------------------------------------------------------

DECLARE @sIndexNameToDrop  sysname
DECLARE @sSql              NVARCHAR(4000)
DECLARE @sMsg              NVARCHAR(440)

-- Check for any existing clustered index that was created by a PRIMARY KEY
-- or UNIQUE constraint.  Must use ALTER TABLE to remove these - can't use
-- DROP INDEX on constraints.

SET @sIndexNameToDrop = NULL

SELECT @sIndexNameToDrop = name
  FROM sys.indexes
 WHERE object_id = OBJECT_ID('<schema_name, sysname, dbo>.<table_name_prefix, sysname, tbl><table_name, sysname, ThisTable>')
   AND type_desc = 'CLUSTERED'
   AND (is_unique_constraint = 1 OR is_primary_key = 1)

IF @sIndexNameToDrop IS NOT NULL
BEGIN
    RAISERROR('Dropping existing PRIMARY KEY or UNIQUE constraint %s on table <table_name_prefix, sysname, tbl><table_name, sysname, ThisTable>', 10, 1, @sIndexNameToDrop ) WITH NOWAIT, LOG

    SET @sSql = 'ALTER TABLE <schema_name, sysname, dbo>.<table_name_prefix, sysname, tbl><table_name, sysname, ThisTable> DROP CONSTRAINT ' + @sIndexNameToDrop
    PRINT @sSql
    EXEC(@sSql)
END

-- If there's a non-clustered index, or a clustered index that was created by 
-- an ADD INDEX, and the column we are wanting to cluster is the *only* column 
-- in the index, we want to drop it - *unless* it's a clustered one, and is 
-- *only* on the column we are wanting to cluster.  Since we're only looking 
-- at indexes with only one column, we don't have to check that the column is 
-- not 'included'.

SET @sIndexNameToDrop = NULL

SELECT @sIndexNameToDrop = si.name
  FROM sys.indexes                    AS si
  JOIN sys.index_columns              AS sic
    ON si.object_id  = sic.object_id
   AND si.index_id   = sic.index_id
  JOIN sys.columns                    AS sc
    ON sic.column_id = sc.column_id
   AND sic.object_id = sc.object_id
 WHERE si.object_id  = OBJECT_ID('<schema_name, sysname, dbo>.<table_name_prefix, sysname, tbl><table_name, sysname, ThisTable>')
   AND NOT (sc.name IN ('<column_name, sysname, Column>') AND si.type_desc = 'CLUSTERED')
   AND (si.is_unique_constraint = 0 AND si.is_primary_key = 0)
   AND 1 =
          (SELECT COUNT(*)
             FROM sys.index_columns
            WHERE object_id    = OBJECT_ID('<schema_name, sysname, dbo>.<table_name_prefix, sysname, tbl><table_name, sysname, ThisTable>')
              AND key_ordinal <> 0
              AND column_id   <> 0)

IF @sIndexNameToDrop IS NOT NULL
BEGIN
    RAISERROR('Dropping existing index on table <schema_name, sysname, dbo>.<table_name_prefix, sysname, tbl><table_name, sysname, ThisTable>, column <column_name, sysname, Column>', 10, 1) WITH NOWAIT, LOG

    SET @sSql = 'DROP INDEX ' + @sIndexNameToDrop + ' ON <schema_name, sysname, dbo>.<table_name_prefix, sysname, tbl><table_name, sysname, ThisTable>'
    PRINT @sSql
    EXEC(@sSql)
END

-- Now add the clustered index.
RAISERROR('Adding <set_as_primary_key, NVARCHAR, PRIMARY KEY> clustered index to <database_name, sysname, MyDB>''s <table_name_prefix, sysname, tbl><table_name, sysname, ThisTable> table', 10, 1) WITH NOWAIT, LOG

ALTER TABLE <schema_name, sysname, dbo>.<table_name_prefix, sysname, tbl><table_name, sysname, ThisTable> WITH CHECK
  ADD CONSTRAINT PK_<table_name, sysname, ThisTable> <set_as_primary_key, NVARCHAR, PRIMARY KEY> CLUSTERED (<column_name, sysname, Column>)
 WITH (PAD_INDEX = ON, FILLFACTOR = <fill_factor, INT, 70>)

See BOL for info on how to create and use templates.

Wednesday, March 24, 2010

Fix for 'USE dbname' Failing Randomly

Have you ever gotten an error message that you just couldn't figure out? A while back, and for about a year, a certain T-SQL script was giving me this error message:

Cannot find database 'SALES' in system tables.

This error message occured randomly every month or so, and with no cause-and-effect pattern that I could see. I couldn't reproduce it. It didn't fail when I ran it manually - but every time it happenned, it usually happened two or three times in a row. This gave me a series of tiny "windows of opportunity" in which to debug the problem.

After about a year (on and off), I had it narrowed down to one line of code - if I removed this line, the script ran correctly every time:

USE [Sales]

Now, the 'Sales' database was quite demonstrably there. Googling for the error message turned up one (unhelpful) hit. So, I started by dropping and re-creating the database. No joy. Next, I re-installed SQL Server. No joy. I re-imaged the machine, to re-install the operating system. I re-imaged the machine using a different operating system. I tried all of this again on a different machine. No joy. I was stymied.

I had long ago removed all the T-SQL code from this script that wasn't absolutely necessary, so now I started to re-write the script, line by line, using different keywords, constant values, and variable names. (Yes, by now I was on a mission.) Finally, I discovered the cause: it only happened when the script contained the SYSNAME datatype: changing it to NVARCHAR(128) fixed it! Hurray!

But wait: that doesn't make any sense. As BOL points out:

SQL Server 2005 Books Online (November 2008)
... sysname is used for table columns, variables, and stored procedure parameters that store object names. The exact definition of sysname is related to the rules for identifiers; therefore, it can vary between instances of SQL Server. sysname is functionally equivalent to nvarchar(128). (Emphasis added.)

If sysname and nvarchar(128) are "functionally equivalent", why would changing from one to the other fix the error message? Well, a little farther down in BOL, there's this:

Important:
In databases that are case-sensitive, or that have a binary collation, sysname is recognized as a SQL Server system data type only if it appears in lowercase.

So, that was the problem: I had used SYSNAME instead of sysname.

Actually, the root cause was having collation specifiers spread all higgledy-piggledy on our databases, tables, and columns, sometime specified, sometimes not. Somewhere, one of them had a case-sensitive collation, and that's what caused the whole problem.