T-SQL script to get Backup Details of a Database and the Sequence it was taken | SansSQL

Sunday, March 12, 2017

T-SQL script to get Backup Details of a Database and the Sequence it was taken

Here is an handy script which give the details of the list of backup(s) taken of a database and the sequence it was taken.
DECLARE @db_name VARCHAR(100)
SELECT @db_name = '<DB Name>'

SELECT 
 BS.server_name AS [Server Name]
,BS.database_name AS [Database Name]
,BS.recovery_model AS [Recovery Model]
,BMF.physical_device_name [Location]
,(CAST(BS.backup_size / 1000000 AS INT)) AS [Size of Backup (MB)]
,CASE BS.[type] WHEN 'D' THEN 'Full'
  WHEN 'I' THEN 'Differential'
  WHEN 'L' THEN 'Transaction Log'
  END AS [Type of Backup]
,BS.backup_start_date AS [Backup Date]
,BS.first_lsn AS [First LSN]
,BS.last_lsn AS [Last LSN]
FROM msdb.dbo.backupset BS
INNER JOIN msdb.dbo.backupmediafamily BMF ON BS.media_set_id = BMF.media_set_id
WHERE BS.database_name = @db_name
ORDER BY backup_start_date DESC , backup_finish_date

Download the script from https://gallery.technet.microsoft.com/T-SQL-script-to-get-Backup-5a9b029b?redir=0

4 comments:

alexmaddy said...
This comment has been removed by the author.
alexmaddy said...
This comment has been removed by the author.
alexmaddy said...

Hello, I work in Engre.co and we often need to make data backups for clients, when I make a data backup I follow this sequence
To properly make a backup with subsequent recovery you need:
1) make a backup in the opencard admin
2) save all opencard files from the server folder
3) to make dump of base mySQL (it is better through update)
To restore:
1) restore the mySQL database from the dump
2) copy all opencard files to the server folder
3) restore the backup from the opencard admin
4) just in case after recovery from the admin to restore the mySQL database from the dump again

Manicopus said...

In a world inundated with subpar writing services, I consider myself fortunate to have stumbled upon a team of consummate professionals. Their ability to translate my ideas into eloquent prose is nothing short of remarkable. From research-intensive articles masterpapers.com to creative endeavors, they tackle each task with precision and finesse. My decision to entrust them with my writing needs has proven to be a stroke of genius

Post a Comment

Ads