Backup and Restore pt 2

use master

-- copy only backup

declare @ts varchar(20) = convert(varchar, getdate(), 112) + '_' + right('00' + cast(datepart(hh, getdate()) as varchar), 2) + cast(datepart(mi, getdate()) as varchar)
declare @fn varchar(255) = 'c:\backups\ReportBuilderTraining_' + @ts + '.bak'

backup database ReportBuilderTraining 
	TO DISK=@fn WITH COPY_ONLY

go


-- full backup
declare @ts varchar(20) = convert(varchar, getdate(), 112) + '_' + right('00' + cast(datepart(hh, getdate()) as varchar), 2) + cast(datepart(mi, getdate()) as varchar)
declare @fn varchar(255) = 'c:\backups\ReportBuilderTraining_' + @ts + '.dat'

backup database ReportBuilderTraining 
	TO DISK=@fn

go

-- transaction log backup

declare @ts varchar(20) = convert(varchar, getdate(), 112) + '_' + right('00' + cast(datepart(hh, getdate()) as varchar), 2) + cast(datepart(mi, getdate()) as varchar)
declare @fn varchar(255) = 'c:\backups\ReportBuilderTraining_' + @ts + '.trn'

backup log ReportBuilderTraining 
	TO DISK=@fn

go


-- differential backup (all changes since last full backup, not since last differential)

declare @ts varchar(20) = convert(varchar, getdate(), 112) + '_' + right('00' + cast(datepart(hh, getdate()) as varchar), 2) + cast(datepart(mi, getdate()) as varchar)
declare @fn varchar(255) = 'c:\backups\ReportBuilderTraining_' + @ts + '.dif'

backup database ReportBuilderTraining 
	TO DISK=@fn WITH DIFFERENTIAL