Menu
header photo

SQL-Talk

Talks all about SQL Server and Azure

SQL Server Backups History

Step by Step backups demo
 
--step1: Full backup
backup database AdventureWorks2014 to disk='D:\MSSQL\Backups\Adv2014.bak'
with stats=10,init
restore headeronly from disk='D:\MSSQL\Backups\Adv2014.bak'
restore filelistonly from disk='D:\MSSQL\Backups\Adv2014.bak'
restore verifyonly from disk='D:\MSSQL\Backups\Adv2014.bak'
--Step2: Differential backup 
backup database AdventureWorks2014 to disk='D:\MSSQL\Backups\Adv2014.bak'
with differential,stats=10
restore headeronly from disk='D:\MSSQL\Backups\Adv2014.bak'
--Step3: T.Log backup
backup log AdventureWorks2014 to disk='D:\MSSQL\Backups\Adv2014.bak'
with stats=10
restore headeronly from disk='D:\MSSQL\Backups\Adv2014.bak'
 
--Script to check the backups history of given database
 
SELECT s.database_name, 
m.physical_device_name, 
cast(s.backup_size/1000000 as varchar(14))+' '+'MB' as bkSize, 
CAST (DATEDIFF(second,s.backup_start_date , s.backup_finish_date)AS VARCHAR(4))+' '+'Seconds' TimeTaken,
s.backup_start_date, 
CASE s.[type] 
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as BackupType, 
s.server_name, s.recovery_model
FROM msdb.dbo.backupset s 
inner join msdb.dbo.backupmediafamily m
ON s.media_set_id = m.media_set_id
WHERE s.database_name = 'Sales' --Change db name
ORDER BY database_name, backup_start_date, backup_finish_date