How to see backup history through the back-end?

By querying msdb.dbo.backupset and msdb.obackupmediafamily one can see backup history like start and end time, type of backup, location of the backup.

select bs.database_name
, bs.type
, bs.backup_start_date
, bs.backup_finish_date
, bmf.physical_device_name
FROM msdb.dbo.backupset bs
, msdb.dbo.backupmediafamily bmf
where bmf.media_set_id = bs.media_set_id
and backup_start_date > '2010-01-22'

testDB D 2010-01-22 00:00:02.000 2010-01-22 05:09:32.000 c:\Backup\testdb_full.bak
testDB I 2010-01-23 02:26:21.000 2010-01-23 02:30:28.000 C:\Backup\testdbdiff20100123_022621.bak

Type – D = Database, I – Differential Database, L – Log

Documentation on backupset:
SQLServer 2008 – http://technet.microsoft.com/en-us/library/ms186299.aspx
SQLServer 2005 – http://technet.microsoft.com/en-us/library/ms186299%28SQL.90%29.aspx
SQLServer 2000 – http://technet.microsoft.com/en-us/library/aa260602%28SQL.80%29.aspx

Documentation on backupmediafamily:
SQLServer 2008 http://technet.microsoft.com/en-us/library/ms190284.aspx
SQLServer 2005 – http://technet.microsoft.com/en-us/library/ms190284%28SQL.90%29.aspx
SQLServer 2000 – http://technet.microsoft.com/en-us/library/aa260605%28SQL.80%29.aspx

How to create differential backup?

Using the following T-SQL one can create differential backup or make it part of scheduled job.

declare @datetimestamp varchar(20)
declare @filename nvarchar(100)
declare @name nvarchar(100)

SELECT @datetimestamp = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20), GETDATE(), 120), '-', ''), ' ', '_'), ':', '')

SELECT @filename = 'C:\testDBBackup\testDBbdiff' + @datetimestamp + '.bak'
SELECT @name = 'testDB Diff Backup ' + @datetimestamp

BACKUP DATABASE testDB
TO DISK = @filename
WITH DIFFERENTIAL,INIT, NAME = @name