Full database backup

BACKUP DATABASE [BackupDatabase] 
TO  DISK = N'c:\fullbackups\BackupDatabase.bak' 
WITH NOINIT,  
NAME = N'BackupDatabase-Full Database Backup', 
COMPRESSION,  
STATS = 10
GO

Verify that the database backup is valid (not that the data within is valid)

declare @backupSetId as int
select @backupSetId = position 
from msdb..backupset 
where database_name=N'BackupDatabase' 
and backup_set_id=(select max(backup_set_id) 
from msdb..backupset where database_name=N'BackupDatabase' )
if @backupSetId is null 
begin 
raiserror(N'Verify failed. Backup information for database ''BackupDatabase'' not found.', 16, 1) 
end
RESTORE VERIFYONLY 
FROM  
DISK = N'c:\fullbackups\BackupDatabase.bak' 
WITH  FILE = @backupSetId
GO


Use msdb
go

SELECT logical_name,physical_name,file_number,backup_size,file_type, * 
FROM dbo.backupfile
ORDER BY 1  -- Contains one row for each data or log file that is backed up 

SELECT * FROM dbo.backupmediafamily  -- Contains one row for each media family 
SELECT * FROM dbo.backupmediaset -- Contains one row for each backup media set 
SELECT * FROM dbo.backupset  -- Contains a row for each backup set 
SELECT * FROM dbo.backupfilegroup -- Contains one row for each filegroup in a database at the time of backup 


Extended backup script with options

BACKUP DATABASE [Production] 
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\Production.bak' 
WITH 
--NOFORMAT, 
INIT,   --<< Specifies that all backup sets should be overwritten 
NAME = N'Production-Full Database Backup', 
--SKIP, 
--NOREWIND, 
--NOUNLOAD, 
COMPRESSION,        --<< Compresses the database
STATS = 10, CHECKSUM  --<< Specifies that the backup operation will verify each page for checksum and torn page
GO

Verifies that the database backup is valid

declare @backupSetId as int
select @backupSetId = position from msdb..backupset
 where database_name=N'Production' and backup_set_id=(select max(backup_set_id) 
 from msdb..backupset 
 where database_name=N'Production' )
if @backupSetId is null 
begin 
raiserror(N'Verify failed. Backup information for database ''Production'' not found.', 16, 1) 
end
RESTORE VERIFYONLY 
FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\Production.bak' 
WITH  FILE = @backupSetId,  
NOUNLOAD,  
NOREWIND
GO

Validating a backup

Create a database

Create Database Items
Go

Use Items 
Go

Create a table

Create table Products
(ProductID int IDENTITY (1,1) Primary Key,
ProductName varchar (100),
Brand varchar (100))
go


Insert data into table

insert into Products values ('Bike','Genesis')
insert into Products values ('Hat','Nike')
insert into Products values ('Shoe','Payless')
insert into Products values ('Phone','Apple')
insert into Products values ('Book','Green')
insert into Products values ('Cup','Large')

View data

select * from Products

Full backup with 6 products 

BACKUP DATABASE items 
TO  DISK = N'c:\fullbackups\items.bak' 
WITH NOINIT,  
NAME = N'items-Full Database Backup', 
COMPRESSION,  
STATS = 10
GO

--Verify that the database backup is valid (not that the data within the database is valid!!!)

declare @backupSetId as int
select @backupSetId = position 
from msdb..backupset 
where database_name=N'items' 
and backup_set_id=(select max(backup_set_id) 
from msdb..backupset where database_name=N'items' )
if @backupSetId is null 
begin 
raiserror(N'Verify failed. Backup information for database ''items'' not found.', 16, 1) 
end
RESTORE VERIFYONLY 
FROM  
DISK = N'c:\fullbackups\items.bak' 
WITH  FILE = @backupSetId
GO

Insert 2 more rows of data into table

insert into Products values ('Helmit','Shock')
insert into Products values ('Mitts','UnderAmour')

View data

select * from Products

Differential backup
The database must have a full back up in order to take a differential backup; it only backups the changes since last full backup.


BACKUP DATABASE items 
TO  DISK = N'c:\fullbackups\items.bak' 
WITH  DIFFERENTIAL , 
NOINIT, 
 NAME = N'items-Differential Database Backup', 
 COMPRESSION,  
 STATS = 10
GO

The differential backup has captured those TWO additional inserts to the database  (the last two changes since that last full backup)

Verify that the differential backup is valid (not that the data within the database is valid!!!)


declare @backupSetId as int
select @backupSetId = position 
from msdb..backupset 
where database_name=N'items' 
and backup_set_id=(select max(backup_set_id) 
from msdb..backupset where database_name=N'items' )
if @backupSetId is null 
begin 
raiserror(N'Verify failed. Backup information for database ''items'' not found.', 16, 1) 
end
RESTORE VERIFYONLY 
FROM  DISK = N'c:\fullbackups\items.bak' 
WITH  FILE = @backupSetId
GO


Insert 2 more rows of data into table

insert into Products values ('Screen','HP')
insert into Products values ('Ipad','Apple')

View data

select * from Products


Transaction Log backup

You must backup the transaction log, if SQL Server database uses either FULL or BULK-LOGGED recovery model otherwise transaction log is going to full. 

Backing up the transaction log truncates the log and user should be able to restore the database to a specific point in time.

BACKUP LOG items 
TO  DISK = N'c:\fullbackups\items.bak' 
WITH 
NAME = N'items-Transaction Log  Backup', 
COMPRESSION,  
STATS = 10
GO

The transaction log backup has captured those TWO additional inserts to the database  (all modification to the database)

declare @backupSetId as int
select @backupSetId = position 
from msdb..backupset 
where database_name=N'items' and backup_set_id=(select max(backup_set_id) 
from msdb..backupset where database_name=N'items' )
if @backupSetId is null 
begin 
raiserror(N'Verify failed. Backup information for database ''items'' not found.', 16, 1) 
end
RESTORE VERIFYONLY 
FROM  DISK = N'c:\fullbackups\items.bak' 
WITH  FILE = @backupSetId
GO



RESTORE HEADERONLY FROM DISK = N'c:\fullbackups\items.bak'

RESTORE LABELONLY FROM DISK = N'c:\fullbackups\items.bak'

RESTORE FILELISTONLY FROM DISK = N'c:\fullbackups\items.bak'

Backup History
The following commands provide information as to the history of the backups in the MSDB database.

Use msdb
go

SELECT logical_name, * FROM dbo.backupfile order by 1   -- Contains one row for each data or log file that is backed up 

SELECT * FROM dbo.backupset  -- Contains a row for each backup set
SELECT * FROM dbo.backupmediafamily  -- Contains one row for each media family 
SELECT * FROM dbo.backupmediaset -- Contains one row for each backup media set 
SELECT * FROM dbo.backupfilegroup -- Contains one row for each filegroup in a database at the time of backup