• Article Topics

  • Become a member of the SQLDBASupport community and receive notifications of new posts by email

    Join 82 other followers

  • Are you writer? Become an Author! Mail to

    amitvaid81 @ gmail dot com

  • Founder

    This is journey of a SQL DBA. I now work as a Sr. Database Architect and Solution Consultant, generally in performance tuning, clustering or high availability. The opinions expressed herein are my own personal opinions through real time experience.

    Amit Vaid

  • Advertisements

Routine Backup & Restore Queries

The following is the query for recent 100 records for database backups.
you get results by changing backup type below

D= Full backup
L= T-Log backup
I=Incremental or Differential

SELECT TOP 100 bs.database_name,
FROM   msdb..backupset bs
       JOIN msdb..backupmediafamily bm
         ON bs.media_set_id = bm.media_set_id
WHERE  bs.TYPE = 'D'
ORDER  BY bs.backup_start_date DESC,


Another query to get Backup start and end time with total backup size.

    a.database_name AS dbname,
    AS start_time,
    AS end_time,
    a.backup_size/1024/1024 AS [backup_Size_MB]
    msdb.dbo.backupset AS a
    msdb.dbo.backupset AS b
    a.database_name = b.database_name
    AND a.backup_start_date >
    (SELECT MAX(backup_start_date) FROM
msdb.dbo.backupset)  1
    AND b.backup_start_date = DATEADD(week, 1,
    (SELECT MAX(backup_start_date)
FROM msdb.dbo.backupset)  1)

/////////////////////////////////////////////////////////// RESTORE Query

Another beautiful query here to result restore history

SET @dbname = 'db_name' /* Replace with your dbname*/
destination_database_name AS 'Database Name',
[user_name] AS 'Username',
CASE restore_type
WHEN 'D' THEN 'Database'
WHEN 'F' THEN 'File'
WHEN 'G' THEN 'Filegroup'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Log File'
WHEN 'V' THEN 'Verifyonly'
WHEN 'R' THEN 'Revert'
END AS 'Restore Type',
CASE [replace]
END AS 'Database Replaced',
restore_date AS 'Date Restored'
FROM msdb..restorehistory
WHERE destination_database_name = CASE
 WHEN @dbname IS NOT NULL THEN @dbname
ELSE destination_database_name END
ORDER BY restore_date DESC

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

%d bloggers like this: