SQL Server Backups

SQL Server Backups

Full backup
backup database AdventureWorks2014 to disk='d:\backups1\Advw.bak'
with stats=10
restore headeronly from disk='d:\backups1\Advw.bak'
restore filelistonly from disk='d:\backups1\Advw.bak'
restore verifyonly from disk='d:\backups1\Advw.bak'

Differential backups
backup database AdventureWorks2014 to disk='d:\backups1\Advw.bak'
with differential,stats=10
restore headeronly from disk='d:\backups1\Advw.bak'

T.Log backup
backup log AdventureWorks2014 to disk='d:\backups1\Advw.bak'
with stats=10
restore headeronly from disk='d:\backups1\Advw.bak'

To check backup history

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 = 'AdventureWorks2014' 
ORDER BY database_name, backup_start_date, backup_finish_date