spEasyBackup

IF EXISTS(SELECT * FROM sysobjects WHERE NAME = 'spEasyBackup') DROP PROCEDURE spEasyBackup
GO

CREATE PROCEDURE [dbo].[spEasyBackup] 
@file_path varchar(255) = null, --(Optional) File path for backup file. If null bak file stored in same location as main data (.mdf) file.
@overwrite bit = 0, --(Optional) When on the current bak file is overwritten
@RetainDays int = 0, --(Optional) Specify the number of retain days for bak file.
@net_send_users varchar(255) = null, --(Optional) Valid net send recipient list.
@device varchar(255) = null, --(Optional) Backup device name.
@database varchar(255) = null, --(Optional) Database to back up. If null current DB is used.
@backup_set_name varchar(255) = null, --(Optional) Backup set name.
@date_stamp_file bit = 0 --(Optional) When on backup file name changed to include date.
AS
SET NOCOUNT ON
--**************************************
--     
-- Name: spEasyBackup
-- Description:Database backup made simple. You dont even have to pass any parameters if desired. It 
--will all be handled for you.
--Capable of Net Send status notifications and writes status message TO Windows event log.
--
-- By: Shane Lively
--
-- Side Effects:When not passing any @database params the the backup is done of the database where the 
--proc resides. That means if you put this in your master db as a system proc and call it without a
--      @database parameter, you will be backing up the master database!
--
--**************************************

/* Set Defaults */IF @database IS NOT NULL OR @database <> ''
BEGIN
IF DB_ID(@database) IS NULL 
BEGIN
RAISERROR('Invalid database name.',16,1)
RETURN
END
END
ELSE SET @database = DB_NAME(DB_ID()) 

IF @device IS NULL OR @device = ''
SET @device = 'Backup' + @database
IF @database IS NULL OR @database = ''
SET @database = @database
IF @backup_set_name IS NULL OR @backup_set_name = ''
SET @backup_set_name = @database + ' Database Backup'

IF @file_path IS NULL OR @file_path = ''
BEGIN
SELECT @file_path = LTRIM(RTRIM(filename))
FROM sysfiles 
WHERE fileid = 1 
AND groupid = 1

SET @file_path = LEFT(@file_path,(LEN(@file_path) - PATINDEX('%\%',REVERSE(@file_path))+1)) + @database + '_Backup.bak'
END

IF @date_stamp_file = 1
BEGIN
SET @file_path = REPLACE( REPLACE(@file_path, '.bak', '_' + REPLACE( CONVERT(varchar,GETDATE(),101),'/','_') + '.bak'), SPACE(1),'_')
END

/* Check for Device */DECLARE @sql varchar(1024),
@error int

SET @error = 0

IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE NAME = '#device')
BEGIN
DROP TABLE #device
END

CREATE TABLE #device(device_name sysname, physical_name nvarchar(255), description nvarchar(255), 
status int, cntrltype smallint, size int)
INSERT INTO #device EXEC sp_helpdevice
exec(@sql)

/* Create\Validate Device */IF EXISTS(SELECT * FROM #device WHERE device_name = @device)
BEGIN
IF NOT EXISTS(SELECT * FROM #device WHERE device_name = @device AND physical_name = @file_path)
BEGIN
EXEC sp_dropdevice @device
EXEC sp_addumpdevice 'disk', @device, @file_path
END 
END
ELSE 
BEGIN
IF NOT EXISTS(SELECT * FROM #device WHERE physical_name = @file_path)
BEGIN
EXEC sp_addumpdevice 'disk', @device, @file_path 
END
ELSE
BEGIN
EXEC sp_dropdevice @device
EXEC sp_addumpdevice 'disk', @device, @file_path
END
END

/* Backup Database */IF @overwrite = 1
BEGIN
--Overwrite backup file
IF @RetainDays IS NOT NULL AND @RetainDays > 0
BEGIN
BACKUP DATABASE @database TO @device WITH RETAINDAYS= @RetainDays, SKIP, NAME = @backup_set_name, INIT
SET @error = @error + @@ERROR
END
ELSE
BEGIN
BACKUP DATABASE @database TO @device WITH NAME = @backup_set_name, SKIP, INIT
SET @error = @error + @@ERROR
END
END
ELSE
BEGIN
--Append to backup file
SET @error = 0 
BACKUP DATABASE @database TO @device WITH  NAME = @backup_set_name, NOINIT
SET @error = @error + @@ERROR
END

/* Log Status Message */DECLARE @cmd varchar(1024)
IF @error <> 0 
BEGIN
SET @cmd = 'Database backup failed for ' + @database + ' on ' + CONVERT(varchar,GETDATE(),1) + ' to ' + @file_path + ' .'
EXEC master.dbo.xp_logevent 51501,@cmd,'ERROR'
END
ELSE
BEGIN
SET @cmd = 'Database backup successful for ' + @database + ' on ' + CONVERT(varchar,GETDATE(),1) + ' to ' + @file_path + ' .'
EXEC master.dbo.xp_logevent 51501,@cmd,'INFORMATIONAL'
END

/* Send Status Message */IF @net_send_users IS NOT NULL AND @net_send_users <> ''
BEGIN
SET @cmd = 'NET SEND ' + @net_send_users + ' ' + @cmd
EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT
END
SET NOCOUNT OFF
GO