SQL Backup Script

-------------------------------------------------------------------------------------------
-- OBJECT NAME : Alan backup script
--
-- AUTHOR : Alan T
--
-- DATE : 2008 06 18
-- 2008 09 18 - Updated parts of the query to clarify messaging
--
-- INPUTS : @dbName - name of the database
-- : @filePath - the file path to wirte the backups to
--
-- OUTPUTS : None
--
-- DEPENDENCIES : msdb.dbo.backupset
-- master.dbo.xp_create_subdir - only avaialable in SQL Server 2005 +
--
-- DESCRIPTION : Used to create a backup in the folder specified. The database will be placed
-- within a folder of the same name as the database. The backup will be verified.
-- Remove the create directory for SQL Server for 2000.

-------------------------------------------------------------------------------------------
declare @dbname nvarchar(50)
declare @filepath nvarchar(64)

SET @dbname = 'database_name';

--Make sure the location of the files ends with the slash as an additional
--folder will be created for the database files
set @filePath = N'directory_to_write_backup_file' + '\' + @dbName

EXECUTE master.dbo.xp_create_subdir @filepath;
declare @fileName nvarchar(128)
declare @fullPath nvarchar(255)

--set the date format yyyymmddhhmm for the file name
declare @date nvarchar(10)
declare @hour nvarchar(4)
declare @minute nvarchar(4)

--ensure hour is 2 digit
set @hour = CAST(datepart(hh, GetDate())AS nvarchar(2))
if len(@hour) = 1
set @hour = '0' + CAST(datepart(hh, GetDate())AS nvarchar(2))

--ensure hour is 2 digit
set @minute = CAST(DATEPART(mi, GetDate())AS nvarchar(2))
if len(@minute) = 1
set @minute = '0' + CAST(DATEPART(mi, GetDate())AS nvarchar(2))

--set the filename
SELECT @filename = @dbName + N'_backup_' + CONVERT( nvarchar(30), GetDate(), 112 ) + @hour + @minute + N'.bak';
SELECT @fullPath = @filePath + '\' + @fileName
print @fileName;

BACKUP DATABASE @dbname TO DISK = @fullpath WITH NOFORMAT, NOINIT, NAME = @fileName, SKIP, REWIND, NOUNLOAD, STATS = 5;

--run the verify
declare @backupSetId as int
select @backupSetId = position from msdb.dbo.backupset where database_name=@dbname and backup_set_id=(SELECT max(backup_set_id) from msdb.dbo.backupset where database_name=@dbname )
if @backupSetId is null
begin
declare @errMsg nvarchar(128)
SELECT @errMsg = N'Verify failed. Backup information for database ' + @dbname + N' not found.'
raiserror(@errMsg, 16, 1);
end;

RESTORE VERIFYONLY FROM DISK = @fullpath WITH FILE = @backupSetId, NOUNLOAD, NOREWIND, STATS = 5;