RSS

Monthly Archives: March 2012

list down all SQL Server backups

Dwenload Script : http://hotfile.com/dl/149375389/c747113/List_Dowen_SQL_Server_Backups.rar.html

/*——————————————————————————————————————————-

— Description    :     This SQL Script will list down all SQL Server backups which were not backup in last 2 days

— Copyright 2012 – DBATAG

— Author        :    DBATAG

— Created on    :    02/06/2012

— Version       :    1.1

— Dependencies  :

— Compatibility  : This will work on SQL Server 2000, 2005, 2008, 2008 R2 and even in SQL Server 2012

—————————————————————————————————————————-*/

SELECT

   CONVERT(CHAR(100), SERVERPROPERTY(‘Servername’)) AS Server,

   bs.database_name,

   MAX(bs.backup_finish_date) AS last_db_backup_date,

   DATEDIFF(hh, MAX(bs.backup_finish_date), GETDATE()) AS [Backup Age (Hours)]

FROM    msdb.dbo.backupset bs

WHERE     bs.type = ‘D’

GROUP BY bs.database_name

HAVING      (MAX(bs.backup_finish_date) < DATEADD(hh, – 48, 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 bs

       ON master.dbo.sysdatabases.name  = bs.database_name

WHERE bs.database_name IS NULL AND master.dbo.sysdatabases.name <> ‘tempdb’

ORDER By bs.database_name

————————————————————————————————————————————————

/*——————————————————————————————————————————-

Description    :     This SQL Script will list down all SQL Server backups which were taken in last 10 days with details

— Copyright 2012 – DBATAG

— Author        :    DBATAG

— Created on    :    02/06/2012

— Version       :    1.0

— Dependencies  :

— Compatibility  : This will work on SQL Server 2000, 2005, 2008, 2008 R2 and even in SQL Server 2012

—————————————————————————————————————————-*/

SELECT

   CONVERT(CHAR(100), SERVERPROPERTY(‘Servername’)) AS Server,

   bs.database_name,

   bs.backup_start_date,

   bs.backup_finish_date,

   bs.expiration_date,

   CASE bs.type

       WHEN ‘D’ THEN ‘Full Database’

       WHEN ‘L’ THEN ‘Log’

       WHEN ‘I’ THEN ‘Differential’

       WHEN ‘F’ THEN ‘File Level’

       WHEN ‘G’ THEN ‘File Level Differential’

       WHEN ‘P’ THEN ‘Partial’

       WHEN ‘Q’ THEN ‘Differential partial’

   END AS backup_type,

   convert(varchar,cast(bs.backup_size/1024/1024 as money),10) as ‘Backup Size in MB’,

   bmf.logical_device_name,

   bmf.physical_device_name,

   bs.name AS backupset_name,

   bs.description

FROM   msdb.dbo.backupmediafamily  bmf

   INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id

WHERE  (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() – 10)

ORDER BY

   bs.database_name,bs.backup_finish_date

 
Leave a comment

Posted by on March 10, 2012 in backups, backups