October 14, 2008 at 1:04 am
Yeah,
I Created a stored procedure to take backups. Iam In a state of confusion whether I can use this in production or not...any improvements and thoughts would be appreciated
USE [master]
GO
/****** Object: StoredProcedure [dbo].[BackupDatabase] Script Date: 10/10/2008 23:51:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[BackupDatabase]
@DBName VARCHAR(75),
@BackupPath VARCHAR(2000),
@BackupType VARCHAR(4) = 'FULL'
AS
BEGIN
/*
Aim: To be able to take full and log backup of given database on given file path.
*/
DECLARE @BackupName VARCHAR(255), @lz_Backup VARCHAR(255)
SET @lz_Backup = @BackupPath
-- Make sure database exists on server
IF NOT EXISTS (SELECT 1 FROM master.dbo.sysdatabases WHERE name = @DBName)
BEGIN
SELECT 'Database name does not exist'
RETURN(1)
END
-- Make sure one one is trying to take tempdb backup
IF @DBName = 'tempdb'
BEGIN
SELECT 'tempdb cannot be backedup'
RETURN(1)
END
-- Only full and log backups are allowed, other wise do not do any thing.
IF @BackupType NOT IN ('FULL', 'LOG')
BEGIN
PRINT 'Invalid type of Backup selected, only FULL and LOG backup is allowed'
RETURN(1)
END
-- If user did not give the back slash, add one.
IF RIGHT(@BackupPath,1) <> '\'
SET @BackupPath = @BackupPath + '\'
IF @BackupType = 'FULL'
BEGIN
SET @BackupName = @DBName + ' Full Backup'
SET @BackupPath = @BackupPath + @DBName +'_'+ CONVERT(VARCHAR(10),GETDATE(),110)+'.bak'
BACKUP DATABASE @DBName
TO DISK = @BackupPath
WITH NOFORMAT, INIT, NAME = @BackupName,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
DECLARE @backupSetId AS INT
SELECT @backupSetId = position FROM msdb..backupset WHERE database_name= @DBName
AND backup_set_id=(SELECT MAX(backup_set_id) FROM msdb..backupset
WHERE database_name = @DBName )
IF @backupSetId IS NULL
BEGIN
RAISERROR (N'Verify failed for database', 16, 1)
END
RESTORE VERIFYONLY
FROM DISK = @BackupPath
WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
END
IF @BackupType = 'LOG'
BEGIN
DECLARE @Prefix VARCHAR(50)
SELECT @Prefix = CONVERT(VARCHAR(50), GETDATE(),120)
SELECT @Prefix = REPLACE(@Prefix, '-', '')
SELECT @Prefix = REPLACE(@Prefix, ':', '')
SELECT @Prefix = REPLACE(@Prefix, ' ', '')
SET @BackupName = @DBName + ' Log Backup'
SET @BackupPath = @BackupPath + @DBName + '_Log' + @Prefix + '.trn'
BACKUP log @DBName
TO DISK = @BackupPath
WITH NOFORMAT, INIT, NAME = @BackupName,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
END
-- DELETE OLD FULL backup.
IF RIGHT(@lz_backup,1) <> '\'
SET @lz_backup = @lz_backup + '\'
SET @BackupPath ='DEL ' + @lz_Backup + @DBName +'_'+ CONVERT(VARCHAR(10),GETDATE()-2,110)+'.bak'
SELECT @BackupPath
EXECUTE xp_cmdshell @BackupPath
RETURN(0)
END
October 14, 2008 at 2:25 am
Not sure why you want to work that hard to solve something is already solved, wouldn't be easier to schedule a job or two?
Answering your question I think you have to ask yourself...
-- have I tested it?
-- did I attempt to restore from your backup?
-- does point in time recovery works fine?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 14, 2008 at 3:33 am
You may run into trouble running xp_cmdshell on a production server, quite often this feature is disabled on production for security reasons.
Though it would be a lot easier to schedule a job.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply