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

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

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

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)