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,
               bs.backup_start_date,
               bs.backup_finish_date,
               bm.physical_device_name,
               bm.logical_device_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,
          bs.database_name 

///////////////////////////////////////////////////////////////////////////////////////

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


SELECT
    a.database_name AS dbname,
    CONVERT(CHAR(20),a.backup_start_date,100)
    AS start_time,
    CONVERT(CHAR(20),a.backup_finish_date,100)
    AS end_time,
    a.backup_size,
    a.backup_size/1024/1024 AS [backup_Size_MB]
FROM
    msdb.dbo.backupset AS a
    LEFT JOIN
    msdb.dbo.backupset AS b
ON
    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)
ORDER BY
    a.database_name,
    a.backup_start_date

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

Another beautiful query here to result restore history


DECLARE @dbname SYSNAME
SET @dbname = 'db_name' /* Replace with your dbname*/
SELECT
destination_database_name AS 'Database Name',
[user_name] AS 'Username',
CASE restore_type
WHEN NULL THEN 'NULL'
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]
WHEN NULL THEN 'NULL'
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
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
Advertisements

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: