About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Thursday, December 1, 2011

BackupDB

Handy procedure for making database backups a snap.

All you need to do is specify the database name and the path, and optionally add the date or date/time to the backup filename. 

You can also pass in ALL for @DB to backup all databases on this SQL instance.  Very handy when you need to backup a whole server and then restore the files onto another server, as in creating a development server based on production.  

Making large quantities of backups is much more efficient when done via script, as contrasted with right-clicking each database in the object explorer.

CAVEAT:  Always check to see if a given database is having its transaction logs backed up.  If it is, keep in mind that making a full backup will render the previous transaction log backups useless! 

This proc has a couple of dependencies which are easy to install:
        udfAddBackslashIfNec
        DoesPathExist
       

USE Admin
GO

IF object_id('dbo.BackupDB') Is Not Null
      DROP PROC BackupDB
GO

CREATE PROC dbo.BackupDB
      @DB sysname,
      @Path varchar(Max),
      @DateTimeFormat varchar(2) = Null,
      @Mode smallint = 0
AS
/*    DATE              AUTHOR            REMARKS
      12/1/11                 PPaiva            Initial creation.

      DESCRIPTION
            Makes a backup of the database and saves to @Path.  Backup
                  file is always initialized (WITH INIT) so it always
                  contains no more than one backup set.
            Optionally set @Mode = 1 and simply copy/paste the script from the output pane.

            @DB can be a specific database, or 'ALL' to backkup all DBs.
           
            @Mode - 0 - Script only
                        1 - Execute only (make backup)
                        2 - Script and Execute
                       
            @DateTimeFormat
                        D  - Date
                        DT - DateTime
              all else - No date or time - just database name

      DEBUG
            master..xp_fixeddrives
            master..xp_cmdshell 'dir /ogn C:\Paul\SqlData\Bak'

      DEPENDENCIES
            udfAddBackslashIfNec()
            DoesPathExist
           
      USAGE
            -- Get syntax help by passing in an illegal value for @DateTimeFormat
            Exec BackupDB 'Admin', 'C:\Paul\SqlData\Bak', '?'

            -- Script for backing up ALL databases
            Exec BackupDB 'All', 'C:\Paul\SqlData\Bak', Null
           
            -- Backup a specific database
            Exec BackupDB 'Admin', 'C:\Paul\SqlData\Bak'
            Exec BackupDB 'Admin', 'C:\Paul\SqlData\Bak', 'D'
            Exec BackupDB 'Admin', 'C:\Paul\SqlData\Bak', 'DT'

*/

SET NOCOUNT ON

DECLARE @FilePathName varchar(Max),
            @sDate varchar(50),
            @Sql varchar(Max)

-- Help info
IF IsNull(@DateTimeFormat, '') Not In ('', 'D', 'DT')
      BEGIN
            SET @Sql = 'Only the following values are allowed for @DateTimeFormat:

@DateTimeFormat     EXAMPLE BACKUP FILENAME             DATE/TIME FORMAT
---------------     ---------------------------------   -------------------
    Null              Northwind.bak
     D                Northwind_2011-12-01.bak            YYYY-MM-DD
     DT               Northwind_2011-12-01_150253.bak     YYYY-MM-DD_HHMMSS'

            RaisError (@Sql, 0, 1)
            RETURN

      END

IF @Mode Not In (0, 1, 2)
      BEGIN
            RaisError ('@Mode must be 0, 1, or 2.', 16, 1)
            RETURN
      END

-- Check for valid @Path
DECLARE @Out bit
Exec DoesPathExist @Path, @Out OUTPUT

IF @Out = 0
      BEGIN
            SET @Sql = 'The supplied path <' + IsNull(@Path, '(Null)') + '> is not valid.'
            RaisError (@Sql, 16, 1)
            RETURN
      END


SET @DB = IsNull(@DB, '')


-- Check for valid DB
IF @DB <> ''  AND @DB <> 'All'
      IF NOT EXISTS(SELECT * FROM master.dbo.sysdatabases WHERE Name = @DB )
            BEGIN
                  SET @Sql = 'Database <' + @DB + '> was not found on server ' + Convert(varchar, ServerProperty('ServerName')) + '.'
                  RaisError (@Sql, 16, 1)
                  RETURN
            END




SET @Path = Admin.dbo.udfAddBackslashIfNec(@Path)

IF @DateTimeFormat = 'D'
      -- YYYY-MM-DD
      SET @sDate = '_' + Convert(varchar(10), GetDate(), 120)

ELSE IF @DateTimeFormat = 'DT'
      -- YYYY-MM-DD_HHMMSS.bak'
      SET @sDate = '_' + Convert(varchar(10), GetDate(), 120) + '_' +
                  Substring(Convert(varchar(20), GetDate(), 120), 12, 2) +
                  Substring(Convert(varchar(20), GetDate(), 120), 15, 2) +
                  Substring(Convert(varchar(20), GetDate(), 120), 18, 2)
ELSE
      SET @sDate = ''


-- Determine which DBs
CREATE TABLE #BackupDB (
      Name varchar(100) NOT NULL PRIMARY KEY
      )

IF @DB = 'ALL'
      INSERT INTO #BackupDB
            SELECT Name
            FROM master.dbo.sysdatabases
            WHERE Name Not In ('tempdb')  -- not possible to backup tempdb
ELSE
      INSERT INTO #BackupDB
            SELECT Name
            FROM master.dbo.sysdatabases
            WHERE Name = @DB



DECLARE @MaxDB varchar(100),
            @ThisDB varchar(100)

SELECT  @MaxDB = Max(Name),
            @ThisDB = ''
FROM #BackupDB

WHILE @ThisDB < @MaxDB
      BEGIN
            -- Get next DB
            SELECT @ThisDB = Min(Name)
            FROM #BackupDB
            WHERE Name > @ThisDB

            SET @FilePathName = @Path + @ThisDB + @sDate + '.bak'                        
            SET @Sql = 'BACKUP DATABASE [' + @ThisDB + ']
TO DISK = ''' + @FilePathName + ''' WITH INIT'

     
            -- Script only
            IF @Mode = 0
                  BEGIN
                        Print @Sql
                        Print ''
                  END

            -- Execute only
            IF @Mode = 1           
                  BEGIN
                        Print 'Backup file:  ' + @FilePathName
                        Print ''
                        Exec (@Sql)
                  END

            -- Script and Execute
            IF @Mode = 2
                  BEGIN
                        Print @Sql
                        Print ''
                        Exec (@Sql)
                  END
           


      END



Tuesday, November 22, 2011

udfAddBackslashIfNec

Handy function for ensuring a given path ends in a backslash. 

After being burned enough times calling a path + filename without the necessary backslash at the end of the path, I decided to fortify all my code with this function. 

It simply checks for a backslash at the end.  If not present, it adds one.

USE Admin
GO

CREATE FUNCTION dbo.udfAddBackslashIfNec(
      @In varchar(1000)
      )
RETURNS varchar(1001)
AS
/*    DATE        AUTHOR            REMARKS
      11/22/11    PPaiva            Initial creation.

      USAGE
            SELECT dbo.udfAddBackslashIfNec('C:\blahblah')
            SELECT dbo.udfAddBackslashIfNec('C:\blahblah\')

      DESCRIPTION
            Returns @In with a backslash at the right, if necessary.

*/

BEGIN
      DECLARE @LenIn int,
                  @Out varchar(201)
     
      SET @LenIn = Len(@In)

      IF Right(@In, 1) = '\'  OR   @In = ''
            SET @Out = @In
      ELSE
            SET @Out = @In + '\'
     
     

      RETURN @Out

END





Friday, October 21, 2011

DoesPathExist

Handy procedure for returning whether a given path exists. 

It simply calls out the extended procedure xp_FileExist.  Normally it would be appropriate to have this in a user-defined function (UDF), but since UDFs in SQL don’t permit calling extended procedures, it is done here in a stored procedure.  You can either check the OUTPUT parameter @Out or check for the return value.  A value of 1 indicates the path exists, a 0 indicates it does not exist.

I call this proc from other code, just to make the code fail-safe.   You never know when someone changes a path or directory structure; much better to be prepared.

USE Admin
GO

CREATE PROC dbo.DoesPathExist
      @Path varchar(800),
      @Out bit = Null OUTPUT
AS
/*    DATE              AUTHOR            REMARKS
      10/21/11          PPaiva            Initial creation.

      DESCRIPTION
            Handy proc for returning whether a path exists.  Output    
            parameter @Out returns 1 if it exists, 0 if it does not.

      USAGE
            Check the output parameter:
                  -- This path probably exists
                  DECLARE @Out bit
                  Exec DoesPathExist 'C:\Program Files', @Out OUTPUT   
                  SELECT @Out

                  -- This path doesn't exist
                  DECLARE @Out bit
                  Exec DoesPathExist 'C:\blah', @Out OUTPUT
                  SELECT @Out


            OR, check the return value:
                  -- This path probably exists
                  DECLARE @ReturnVal int
                  Exec @ReturnVal = DoesPathExist 'C:\Program Files'
                  SELECT @ReturnVal

                  -- This path doesn't exist
                  DECLARE @ReturnVal int
                  Exec @ReturnVal = DoesPathExist 'C:\blah'
                  SELECT @ReturnVal


      DEPENDENCY
            xp_FileExist

*/

CREATE TABLE #FileExist
      (FileExists bit,
       DirExists bit,
       ParentDirExists bit
      )

INSERT INTO #FileExist
      Exec master.dbo.xp_FileExist @Path

SELECT @Out = DirExists
FROM #FileExist

RETURN @Out

Sunday, September 18, 2011

CreateVwIndexRaw

Creates a view that shows all indexes for all objects in all databases on this SQL instance.  System databases and objects are excluded by default, but you can optionally include them by setting @ShowSysObjects = 1.  This is helpful in ferreting out exactly what covering indexes exist for a given query, or for identifying duplicate indexes.  

There is no system procedure that shows any included columns for an index.  Note results of this system proc:

sp_helpindex employees



Info about included columns must be obtained from querying these two system views:

SELECT *
FROM sys.indexes

SELECT *
FROM sys.index_columns

After running CreateVwIndexRaw, this query will now show the included columns, for all objects in all databases in this SQL instance.

SELECT *
FROM Admin..vwIndexRaw
WHERE DB = 'Sopa'
  AND ObjName = 'Employees'



Use Admin
go

IF object_id('dbo.CreateVwIndexRaw') Is Not Null
      DROP Proc dbo.CreateVwIndexRaw
go

CREATE PROC dbo.CreateVwIndexRaw
      @ShowSql bit = 0,
      @ShowSysObjects bit = 0
AS
/*    DATE        AUTHOR            REMARKS
      9/18/11           PPaiva            Intial creation.


      DESCRIPTION
            Creates a view in the Admin database to see
                  all indexes for all objects in all databases
                  on this server.
            System objects are excluded by default;
                  use @ShowSysObjects = 1 to show them.
            Coded to use Convert(varchar, ServerProperty('ServerName'))
                  rather than @@ServerName since the latter can be erroneous.

      USAGE
            Exec Admin..CreateVwIndexRaw 1
            Exec Admin..CreateVwIndexRaw 1, 1

      DEBUG
            SELECT *
            FROM Admin.dbo.vwIndexRaw
            ORDER BY Server, DB, SchemaName, ObjName, IndexName, Ord

            SELECT *
            FROM Admin.dbo.vwIndexRaw
            WHERE Include = 1
            ORDER BY DB, ObjName, IndexName, Ord

            SELECT *
            FROM sys.databases

*/
SET NOCOUNT ON

DECLARE @s varchar(max),
            @DB varchar(100),
            @MaxDB varchar(100),
            @i int,
            @sNow varchar(16)

SET @sNow = Convert(varchar(16), GetDate(), 120)


SET @s = 'IF Object_ID(''dbo.vwIndexRaw'') Is Not Null
      DROP VIEW dbo.vwIndexRaw'

IF @ShowSql = 1
      Print @s

Exec(@s)
     
Print ''

SET @s = 'CREATE VIEW vwIndexRaw
AS
/*    DATE        AUTHOR            REMARKS
      ' + Convert(varchar(10), GetDate(), 01) + '     PPaiva            Initial creation.

      DESCRIPTION
            Provides a list of all indexes in all databases
                  on this instance.
            This view is auto-generated on a daily basis via
                  Admin.dbo.CreateVwIndex. 
            If a new database is added this view won''t show it unless
                  you run the refresh code below. 
            If a database is deleted this view will malfunction unless
                  you run the refresh code below. 
                 
      To REFRESH VIEW when new databases are added:
            Exec Admin.dbo.CreateVwIndex
           

      -- Sample of view      
      SELECT TOP 100 *
      FROM Admin.dbo.vwIndexRaw
      ORDER BY Server, dB, SchemaName, ObjName, IndexName, Ord


*/
'

-- Will refer to these system databases more than once,
-- so load into table for easy reference.
CREATE TABLE #SysDB(
      Name varchar(30) NOT NULL
      )
INSERT INTO #SysDB VALUES ('master')
INSERT INTO #SysDB VALUES ('tempdb')
INSERT INTO #SysDB VALUES ('model')
INSERT INTO #SysDB VALUES ('msdb')
INSERT INTO #SysDB VALUES ('Admin')
INSERT INTO #SysDB VALUES ('distribution')


SELECT name
INTO #DBs
FROM sys.databases
WHERE state_desc = 'online'

IF @ShowSysObjects = 0
      DELETE #DBs
      FROM #DBs d
      JOIN #SysDB s
            ON s.Name = d.name

SELECT  @MaxDB = Max(Name),
            @DB = '',
            @i = 0
FROM #DBs


DECLARE @IsSystemDB char(1)

WHILE @DB < @MaxDB
      BEGIN
            SET @i = @i + 1

            SELECT @DB = Min(Name)
            FROM #DBs
            WHERE Name > @DB
           
            IF @DB In (SELECT Name FROM #SysDB)
                  SET @IsSystemDB = '1'
            ELSE
                  SET @IsSystemDB = '0'


            SET @s = @s + '
SELECT  '

            SET @s = @s + 'Convert(varchar, ServerProperty(''ServerName'')) as Server,
            ''' + @DB + ''' as DB,
            s.name COLLATE SQL_Latin1_General_CP1_CI_AS SchemaName,
            o.name COLLATE SQL_Latin1_General_CP1_CI_AS ObjName,
            i.is_primary_key PK,
            i.is_unique Uniq,
            i.name COLLATE SQL_Latin1_General_CP1_CI_AS IndexName,
            ic.key_ordinal Ord,
            c.name COLLATE SQL_Latin1_General_CP1_CI_AS ColName,
            CASE WHEN ic.is_descending_key = 0
                        THEN ''asc''
                        ELSE ''desc''
                  END Sort,
            i.type_desc Type,
            ic.is_included_column Include,
            i.is_unique_constraint UniqCons,
            i.fill_factor FF,
            ic.column_id ColID,
            i.object_id,
            c.column_id,
            o.create_date ObjCreateDate,
            o.modify_date ObjModifyDate,
            ' + @IsSystemDB + ' IsSystemDB,
            ''' + @sNow + ''' ViewCreateDate
FROM [' + @DB + '].sys.indexes i
JOIN [' + @DB + '].sys.index_columns ic
      ON  ic.object_id = i.object_id
      AND ic.index_id = i.index_id
JOIN [' + @DB + '].sys.columns c
      ON  c.object_id = ic.object_id
      AND c.column_id = ic.column_id
JOIN [' + @DB + '].sys.objects o
      ON o.object_id = i.object_id
JOIN [' + @DB + '].sys.schemas s
      ON s.schema_id = o.schema_id
'

IF @ShowSysObjects = 0
      SET @s = @s + 'WHERE o.type <> ''s''
  AND s.name <> ''sys''
'

                                   
            IF @DB <> @MaxDB
                  SET @s = @s + '    UNION ALL '
                 

      END



IF @ShowSql = 1
      BEGIN
            Print @s
      END

Exec (@s)