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

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.

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