Technical Article

spEasyBackup

,

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.

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!

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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating