RSS

list down all SQL Server backups

10 Mar

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

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s