September 18, 2008 at 5:15 pm
Hello,
Iam using this below procedure for Full backup of databases. I created a job to run every day at 12am and every day it should overwrite the previous day's backups. So what is the missing script that I need to add for this procedure. Could you plz help me in that?
USE [master]
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Full_Backup]
@DBName VARCHAR(100),
@BackupPath VARCHAR(2000),
@BackupType VARCHAR(4) = 'FULL'
AS
BEGIN
DECLARE @BackupName VARCHAR(255)
-- 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
RETURN(0)
END
September 18, 2008 at 8:55 pm
If you want to overwrite backups, use the same file name and the WITH INIT clause. However this isn't a great practice. If it fails partially in there, you don't have today's backup, and you've already removed yesterdays.
September 18, 2008 at 9:15 pm
Better bet is to either move the previous days files into another folder prior to backup or add the date on to the end of the new file being generated to avoid the situation that Steve spoke of. If you look on the Scripting Guys website (www.microsoft.com - search scripting guys) you will be able to find some VBScripts that will help with this. If you want to get really fancy you can do some deletes that way as well and even check to make sure that the archive bit shows that it has gone to tape. Again, there are some great examples on the site mentioned earlier.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply