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.

Sunday, May 20, 2012

vwBackupHistory

Here is a view that JOINs the pertinent system tables in msdb to present a concise view of backup history.

This view has three dependencies which are easy to install:

        udfFormatInteger
        udfGetFilenameOnly
        udfGetPathOnly

USE Admin
GO

IF object_id('vwBackupHistory') Is Not Null
      DROP VIEW dbo.vwBackupHistory
GO

CREATE VIEW dbo.vwBackupHistory
AS
/*    DATE        AUTHOR            REMARKS
      5/20/12           PPaiva            Initial creation.
     
      -- Last 10 backups per DB per Type per Path  
      SELECT *
      FROM vwBackupHistory
      WHERE Num <= 10
        AND DB Not In ('master', 'model', 'msdb')
      ORDER BY 1, 2, 3, 4, 5, 6

      SELECT *
      FROM vwBackupHistory
      ORDER BY DateStarted desc

      -- DBs backed up
      SELECT Machine, SqlInstance, DB, BackupType, RecoveryModel, Path, SoftwareName, Count(*) Qty
      FROM vwBackupHistory
      GROUP BY Machine, SqlInstance, DB, BackupType, RecoveryModel, Path, SoftwareName
      ORDER BY Machine, SqlInstance, DB, BackupType, RecoveryModel, Path, SoftwareName

      SELECT *
      FROM vwBackupHistory
      WHERE BackupType <> 'Log'
      ORDER BY DateFinished desc,   DB


      SELECT Machine, SqlInstance, DB, BackupType, RecoveryModel, Path, SoftwareName, Count(*) Qty
      FROM vwBackupHistory
      GROUP BY Machine, SqlInstance, DB, BackupType, RecoveryModel, Path, SoftwareName
      ORDER BY Machine, SqlInstance, DB, BackupType, RecoveryModel, Path, SoftwareName

*/
SELECT  b.machine_name Machine,
            b.server_name SqlInstance,
            b.database_name DB, 
            CASE b.type 
                  WHEN 'D' THEN 'Database' 
                  WHEN 'L' THEN 'Log' 
                  WHEN 'I' THEN 'Differential Database'
                  WHEN 'F' THEN 'File or FileGroup'
                  WHEN 'G' THEN 'Differential File'
                  WHEN 'P' THEN 'Partial'
                  WHEN 'Q' THEN 'Differential Partial'
                  WHEN Null THEN 'Null'
                  ELSE 'Need to define in view'
                        END AS BackupType,
            b.recovery_model RecoveryModel,
            ROW_NUMBER() OVER (PARTITION BY b.Machine_name, b.server_name, b.database_name, b.Type, b.Recovery_Model ORDER BY backup_start_date desc) Num,
            b.backup_start_date DateStarted, 
                  DATEDIFF(second, b.backup_start_date, b.backup_finish_date) BackupSecs,
                  DATEDIFF(minute, b.backup_start_date, b.backup_finish_date) BackupMins,
                  dbo.udfFormatInteger((b.backup_size /1000000.)) SizeMBFmt,
                  dbo.udfGetFilenameOnly(f.physical_device_name) Filename,
                  dbo.udfGetPathOnly(f.physical_device_name) Path,
            bms.is_compressed IsCompressed,             -- add this column for 2008 and higher
            b.compressed_backup_size CompressedSizeBytes,      -- add this column for 2008 and higher
            b.backup_size SizeBytes, 
            b.is_copy_only IsCopy,
            b.is_damaged IsDamaged,
            b.description,
            b.expiration_date ExpDate,
            f.physical_device_name PathFilename,  
            b.name AS BackupSetName,
            f.logical_device_name LogicalDeviceName,
            fg.name FileGroup,
            bms.software_name SoftwareName,
            b.backup_finish_date DateFinished
FROM msdb.dbo.backupmediafamily  f
JOIN msdb.dbo.backupset b
      ON f.media_set_id = b.media_set_id 
JOIN msdb.dbo.backupmediaset bms
      ON bms.media_set_id = b.media_set_id
JOIN msdb.dbo.backupfilegroup fg
      ON fg.backup_set_id = b.backup_set_id

GO




Wednesday, May 16, 2012

Attach Database without Log File


If you only have the data file for a database, it is possible to attach it with the “ATTACH_REBUILD_LOG” option which will create a log file.

I found this was a necessary method when attempting to install the AdventureWorks databases for SQL 2012.  The .mdf files can be downloaded from the CodePlex site, but no log files are given.  

CREATE DATABASE AdventureWorks2012
ON (FILENAME = 'C:\Paul\Sql\Data\AdventureWorks2012_Data.mdf')
FOR ATTACH_REBUILD_LOG

CREATE DATABASE AdventureWorksDW2012
ON (FILENAME = 'C:\Paul\Sql\Data\AdventureWorksDW2012_Data.mdf')
FOR ATTACH_REBUILD_LOG

Thanks to Dave Lassiter for this tip.


Wednesday, May 2, 2012

Pagination Example


SQL Server’s TOP clause allows you to SELECT n rows from a result set. 

Unfortunately, these rows are always from the top.  If for example you have a large set of rows, and you have a front-end interface that allows you to show a given page, SQL Server does not have a native method for scrolling down p pages and retrieving only the rows for that page.

But, thanks to the  ROW_NUMBER()  built-in function in combination with a Common Table Expression (CTE), you can create a proc that does exactly what you need.

USE Admin

IF Object_id('dbo.PaginationExample') Is Not Null
      DROP PROC dbo.PaginationExample
GO

CREATE PROC dbo.PaginationExample
      @PageNum int,
      @RowsPerPage int
AS
/*    DATE        AUTHOR            REMARKS
      5/1/12            PPaiva            Initial creation.
     
      DESCRIPTION
            Selects a set of rows for a given page.
            To use, simply swap your table/view name for
                  vwServerDBFile, and change the 
                  ORDER BY clause in the CTE.
           
      USAGE
            Exec PaginationExample 2, 5
            Exec PaginationExample 3, 10
           
      DEBUG
            SELECT *
            FROM vwServerDBFIle
            ORDER BY Server, DB, Usage desc, Filename

*/
SET NOCOUNT ON

DECLARE @StartRow int,
            @EndRow int,
            @Msg varchar(300)


SET @StartRow = (@RowsPerPage * (@PageNum - 1) + 1)
SET @EndRow = @StartRow + (@RowsPerPage - 1)


; WITH cteOutput
AS ( 
      SELECT ROW_NUMBER() OVER (ORDER BY Server, DB, Usage desc, Filename) AS RowNum,
                  *
      FROM vwServerDBFIle
)

SELECT *
FROM cteOutput
WHERE RowNum BETWEEN @StartRow AND @EndRow
ORDER BY RowNum