Backup History Scripts

Database Backups for all databases For Previous Week :

SELECT
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_start_date,
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   CASE msdb..backupset.type
       WHEN 'D' THEN 'Database'
       WHEN 'L' THEN 'Log'
   END AS backup_type,
   msdb.dbo.backupset.backup_size,
   msdb.dbo.backupmediafamily.logical_device_name,
   msdb.dbo.backupmediafamily.physical_device_name, 
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_finish_date

----24 hour full backup
SELECT database_name, backup_start_date, type, *
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND Type = 'D'
ORDER BY backup_set_id DESC
GO

script 2.
:--------------------------------------------------------------------------------------------
--Most Recent Database Backup for Each Database - Detailed
-------------------------------------------------------------------------------------------
SELECT
   A.[Server],
   B.database_name,
   A.last_db_backup_date,
   B.backup_start_date,
   B.expiration_date,
   B.backup_size,
   B.logical_device_name,
   B.physical_device_name, 
   B.backupset_name,
   B.description
FROM
   (
   SELECT 
       CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
       msdb.dbo.backupset.database_name,
       MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
   FROM    msdb.dbo.backupmediafamily
       INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
   WHERE   msdb..backupset.type = 'D'
   GROUP BY
       msdb.dbo.backupset.database_name
   ) AS A
  
   LEFT JOIN

   (
   SELECT 
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_start_date,
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   msdb.dbo.backupset.backup_size,
   msdb.dbo.backupmediafamily.logical_device_name,
   msdb.dbo.backupmediafamily.physical_device_name, 
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE  msdb..backupset.type = 'D'
   ) AS B
   ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
ORDER BY
   A.database_name



script :-
------------------------------------------------------------------------------------------
--Databases Missing a Data (aka Full) Back-Up Within Past 24 Hours
-------------------------------------------------------------------------------------------
--Databases with data backup over 24 hours old
SELECT
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name,
   MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date,
   DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS [Backup Age (Hours)]
FROM    msdb.dbo.backupset
WHERE     msdb.dbo.backupset.type = 'D'
GROUP BY msdb.dbo.backupset.database_name
HAVING      (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24, GETDATE()))

UNION

--Databases without any backup history
SELECT    
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   master.dbo.sysdatabases.NAME AS database_name,
   NULL AS [Last Data Backup Date],
   9999 AS [Backup Age (Hours)]
FROM
   master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset
       ON master.dbo.sysdatabases.name  = msdb.dbo.backupset.database_name
WHERE msdb.dbo.backupset.database_name IS NULL AND master.dbo.sysdatabases.name <> 'tempdb'
ORDER BY
   msdb.dbo.backupset.database_name



--------------------------------------

Percent....backup completed

SELECT percent_complete, *
FROM sys.dm_exec_requests
WHERE command In ( 'RESTORE DATABASE', 'BACKUP DATABASE' )

-------------------------------------------------------
last backup
SELECT   d.name,
         MAX(b.backup_finish_date) AS backup_finish_date
FROM     master.sys.sysdatabases d
         LEFT OUTER JOIN msdb..backupset b
         ON       b.database_name = d.name
         AND      b.type          = 'L'
GROUP BY d.name
ORDER BY backup_finish_date DESC

What is in Backup file

There is situation where we have a DB Backup file but not able to determine 

1.       Which Backup exists in Backup file whether it is full, Differential or log.

2.       How to identify logical name of data and log file

So identify it we can use below commands 

Restore headeronly :  

e.g Backup headeronly DBNAME from Disk = ‘Path where backup file’

                Result provide the info like Backup type, 1 for full ,2 for log, 5 for Differential

Or use BackupTypeDiscription column to check it

Also it provides the info like DB name , Server name, LSN info, Recovery model 

Restore Filelistonly

It provide the logical name , physical name of DB Backup file.

e.g Backup filelistonly  DBNAME from Disk = ‘Path where backup file’

It really helps when you are restoring multiple backup like full backup and sequence of log backup.

If any log backup sequence is not getting identify by file name you can identify by LSN sequence

SQL server current versions services packs

I am just checking What are the current versions services packs available of SQL server .

I am going to keep it updated if any changes here.

SQL server 2000 SP4

SQL server 2005 SP4

SQL server 2008 sp3

SQL server 2008 R2 SP2

SQL server 2012 sp1

SQL server 2014

For more information http://sqlserverbuilds.blogspot.in/ is helpful

script to find specific information from Error log

SET NOCOUNT ON


-- 1 - Declare variables

DECLARE @numerrorlogfile int

-- 2 - Create temporary table

CREATE TABLE #errorLog

([LogDate] datetime,

[ProcessInfo] nvarchar(20),

[Text] nvarchar(max)

)

-- 3 - Initialize parameters

SET @numerrorlogfile = 0

-- 4 - WHILE loop to process error logs

WHILE @numerrorlogfile < 5

BEGIN TRY

INSERT #errorLog ([LogDate], [ProcessInfo], [Text])

EXEC master.dbo.xp_readerrorlog @numerrorlogfile, 1, N'DBCC', NULL, NULL, NULL, N'desc'



SET @numerrorlogfile = @numerrorlogfile + 1;

END TRY

BEGIN CATCH

SET @numerrorlogfile = @numerrorlogfile + 1;

END CATCH

-- 5 - Final result set

SELECT LogDate,[Text] FROM #errorLog

-- 6 - Clean-up temp table

DROP TABLE #errorlog

GO

Database Page Verify options

Use of Page verify option: 

1.     Page verify option identifies the suspected or damaged page from corrupted disk.

2.     It logs the entry of error 824 in SQL error log as well in application event log.

3.     On the basis of this error SQL server writes a record in msdb.suspect_pages table.

There are there options of Page verify

1.     Checksum

2.     Torn_page_detection

3.     None

Microsoft recommended option for page verify is Checksum, So why it is?

Because the CHECKSUM,

Option advises SQL Server to calculate a checksum over the contents of each data / index page when it is written to disk.

This calculated value is stored in the page header.

When the page is read from the disk, checksum is computed again and compared to the value in page header.

Torn page detection:

Before we go to this option we take one look of what is torn page?

            1. Torn page is the page which is damaged because of not properly got written on disk.

            2. Because of sudden shutdown or power off the data did not get saved on disk and while       recovery of database the page get suspect.

            3. This is nothing but the incomplete I/O operation.

So you can add this page verify option to detect torn pages , it configures the 512 byte sector on each data/index pages and if there is any damage to page it identifies as a suspect page when it get read.

None:  so if you don’t want the above helpful advises to find the damaged pages and like to loss then u can choose this option.

Use Alter database command to set this page verify option.

e.g     Alter database xyz set page_verity Checksum / Torn_page_detection