SQL Server Database Backup Script

Looking for a quick database backup script with a datetimestamp?  Replace the [DBName] with your database name.  Note the [FolderPath] needs to be updated as well.

SQL Server Database Backup Script with datetimestamp:



--db name   [DBName]

--use master db for backups
use master

--variables
declare @year varchar(4)set @year=datepart(yy,getdate())
declare @month varchar(2)set @month=case when datepart(mm,getdate())<10 then '0'+cast(datepart(mm,getdate()) as varchar(1)) else cast(datepart(mm,getdate())as varchar) end
declare @day varchar(2)set @day=case when datepart(dd,getdate())<10 then '0'+cast(datepart(dd,getdate()) as varchar(1)) else cast(datepart(dd,getdate())as varchar) end
declare @hour varchar(2) set @hour=case when datepart(hh,getdate())<10 then '0'+cast(datepart(hh,getdate()) as varchar(1)) else cast(datepart(hh,getdate())as varchar) end
declare @minute varchar(2)set @minute=case when datepart(mi,getdate())<10 then '0'+cast(datepart(mi,getdate()) as varchar(1)) else cast(datepart(mi,getdate())as varchar) end

--create bkp device
declare @sqlscript varchar(2000)
set @sqlscript='exec sp_addumpdevice ''disk'', ''[DBName]_networkdevice'', ''[FolderPath]\[DBNAME]'+@year+''+@month+''+@day+''+@hour+''+@minute+'.BKP'''
exec (@sqlscript)

--bkp db
BACKUP DATABASE [DBName] TO [DBName_networkdevice] WITH  INIT ,  NOUNLOAD ,  RETAINDAYS = 5,  NAME = N'[DBName backup',  NOSKIP ,  STATS = 10,  DESCRIPTION = N'[DBName] backup',  NOFORMAT DECLARE @i INT
select @i = position from msdb..backupset where database_name='[DBName]'and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='[DBName]')
RESTORE VERIFYONLY FROM  [[DBName]_networkdevice]  WITH FILE = @i

--drop bkp device
exec sp_dropdevice '[DBName]_networkdevice'