November 22, 2004 at 2:23 pm
I'm ready to bite the bullet and abandon Database Maintenance Plans for backups and script them myself. Two reasons: 1) Differential Backups and 2) Scripting Shrinking the Database Logs at an appropriate time so nothing is lost.
I can emulate backing up to a sub-directory and creating a backup name that is based upon the date/time. I created the following Store Procedure to do it.
create procedure usp_Db_Backup
@vcDB varchar(255),
@cType char(4)
as
Declare @vcStmt varchar(500)
DECLARE @vcFile varchar(255)
DECLARE @vcWith varchar(255)
select @vcFile = @vcDB + '\' + @vcDB + '_' + @cType +
'_' + CONVERT(varchar, CURRENT_TIMESTAMP,112) + '-' +
REPLACE(left(CONVERT(varchar, CURRENT_TIMESTAMP,108),5),':','')
-- Check for Differential Backup
if left(upper(@cType),1) = 'D'
begin
SET @vcFile = @vcFile + '.DIF'
SET @vcWith = ' WITH DIFFERENTIAL'
end
else
begin
SET @vcFile = @vcFile + '.FUL'
SET @vcWith = ''
end
SET @vcStmt = 'BACKUP DATABASE ' + @vcDB + ' TO DISK = ' +
'''' + @vcFile + '''' + @vcWith
-- print @vcStmt
exec (@vcStmt)
go
What I can't figure out is how to get rid of the old backup files. Maintenance Plans have an option to remove older files. I know I could write a Perl script but surely there is an easier way.
Any Ideas?
Steve
November 22, 2004 at 4:40 pm
Here's the procedure that I use. It uses a control table to store the path to the backups, so I'm including the script to create that table as well. Perhaps you can get some ideas -
Steve
-- af_DBA_delete_full_backups_2000
USE afDBA
CREATE TABLE [af_DB_Backup_Ctrl] (
[DBName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BU_Type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Path] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Retention] [int] NULL ,
CONSTRAINT [PK_DBName] PRIMARY KEY CLUSTERED
(
[DBName]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
IF EXISTS
(SELECT *
FROM sysobjects
WHERE id = object_id(N'[dbo].[af_DBA_delete_full_backups_2000]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[af_DBA_delete_full_backups_2000]
GO
CREATE PROCEDURE af_DBA_delete_full_backups_2000
AS
SET NOCOUNT ON
/*************************************************************/
--
-- Module Name: af_DBA_delete_full_backups_2000
--
-- FILENAME FORMAT-- servername_yyyy-mm-dd_hh-mm-ss.bkp
--
-- Description:
-- 0 input parm(s).
-- 0 output parm(s).
--
-- Deletes full database backups that are older than the value
-- given in af_db_Backup_Ctrl.
-- This is a special backup procedure for the RPTMGR server. Required because
-- of limited disk space and large size of database.
/******************************************************************************************
*****
***** ***** NOTE! COLLATION CONFLICT ERROR! *****
*****
***** I ran into a problem with this procedure on ONE server. I was getting an error
***** message referencing a COLLATION CONFLICT that could not be resolved. The error
***** pointed to the definition of the temporary table- #file_table, specifically, the
***** calculated column #FileDate. This was caused by the afDBA database being a different
***** collation than the SERVER collation. There are two ways to get around this error, neither
***** of which involve changes to the procedure!
*****
***** 1. Simply execute the procedure from a database OTHER than afDBA (one that has the same
***** collation as tempdb).
*****
***** 2. Execute the following code, which will change the collation of afDBA to match the
***** server collation.
*****
***** DECLARE
***** @Collation varchar(50),
***** @DBA_DBName sysname,
***** @command varchar(100)
*****
***** SET @DBA_DBName = 'afDBA'
***** SELECT @Collation = CAST(serverproperty('Collation') AS varchar(50))
***** SET @command = 'ALTER DATABASE ' + @DBA_DBName + ' COLLATE ' + @Collation
***** PRINT @command
***** EXEC @command
*****
******************************************************************************************/
--
-- Written By: Steve Phelps
--
-- Date: September 15, 2004
--
-- Modified :
-- Date:
--
-- USAGE:
--
-- exec af_DBA_delete_full_backups_2000
--
/*************************************************************/
DECLARE
@File varchar(100),
@DBName sysname,
@FileDate datetime,
@TodaysDate datetime,
@his_cut_date char(10),
@command varchar(128),
@RetentionDays int,
@Path varchar(128),
@counter int,
@asterisks char(113),
@text varchar(128),
@return int
SET @counter = 0
SET @return = 0
SELECT @TodaysDate = GETDATE()
CREATE TABLE #temp_table
(#file_name varchar(100) NULL)
CREATE TABLE #file_table
( #file_name varchar(100) NULL, #dbname AS SUBSTRING(#file_name, 1, (LEN(#file_name) - 28) ), #filedate AS SUBSTRING(#file_name, (LEN(#file_name) - 22), 10) + ' ' + REPLACE(SUBSTRING(#file_name, (LEN(#file_name) - 11), 8), '-', ':') )
SET @asterisks = '****************************************************************************************************************'
PRINT @asterisks
SET @text = 'Current date/time: '
+ CONVERT(varchar(10),GETDATE(),1) + ' '
+ CONVERT(varchar(10),GETDATE(),8)
PRINT @text
SET @text = 'Delete Full Database backups for server = ' + @@servername
PRINT @text
PRINT @asterisks
-- check existence of control record
IF NOT EXISTS (SELECT 1 FROM af_DB_Backup_Ctrl WHERE BU_Type = 'F')
BEGIN
SET @Text = 'Backup control record does not exist. Delete Old Backups job failed. Contact DBA.'
RAISERROR(@Text,16,1)
SET @return = -1
GOTO CLEANUP
END
-- get the path for the backup files from the control record
SELECT
@Path = Path,
@RetentionDays = Retention
FROM af_DB_Backup_Ctrl
WHERE BU_Type = 'F'
IF @RetentionDays = '99'
BEGIN
PRINT 'No files will be deleted. Retention = 99 (permanent retention).'
GOTO CONT
END
ELSE
PRINT 'Delete full backup files which are older than ' + CAST (@RetentionDays AS varchar(2)) + ' days.'
-- Retrieve filenames of all files in the migration folder and store in #migration_table.
SELECT @command = 'master..xp_cmdshell ' + '"' + 'dir ' + @Path + ' /b /A:-D' + '"'
INSERT #temp_table (#file_name)
EXEC (@command)
INSERT #file_table (#file_name)
SELECT #file_name
FROM #temp_table
WHERE ISDATE(SUBSTRING(#file_name, (LEN(#file_name) - 22), 10) + ' ' + REPLACE(SUBSTRING(#file_name, (LEN(#file_name) - 11), 8), '-', ':')) = 1
-------------------------------------------------------------------------------------------------------------------------------------------------
-- step through #file_table, checking age and deleting old files.
DECLARE File_cur CURSOR FOR
SELECT
#file_name,
#dbname,
#filedate
FROM #file_table
WHERE #file_name IS NOT NULL
ORDER BY #file_name
OPEN File_cur
FETCH NEXT FROM File_cur INTO @File, @DBName, @FileDate
IF @@fetch_status = -1
BEGIN
CLOSE File_cur
DEALLOCATE File_cur
END
ELSE
BEGIN
WHILE @@fetch_status <> -1
BEGIN
IF DATEDIFF(dd,@FileDate, @TodaysDate) >= @RetentionDays
BEGIN
-- Delete the file.
SET @command = 'DEL ' + @Path + @File
PRINT @command
EXEC master..xp_cmdshell @command
SET @counter = @counter + 1
END -- If datediff(dd,@FileDate,@TodaysDate) > @RetentionDays
FETCH NEXT FROM File_cur INTO @File, @DBName, @FileDate
END -- WHILE @@fetch_status <> -1 (File_cur)
CLOSE File_cur
DEALLOCATE File_cur
END -- IF @@fetch_status (File_cur)
PRINT CAST(@counter AS varchar(10)) + ' file(s) deleted.'
-------------------------------------------------------------------------------------------------------------------------------------------------
CONT:
-------------------------------------------------------------------------------------------------------------------------------------------------
-- delete backup history older than 3 months.
PRINT @asterisks
-- set @his_cut_date = 3 months ago
SELECT @his_cut_date = CONVERT(CHAR(10), DATEADD(MONTH, -3, GETDATE()), 101)
-- set @his_cut_date to 1st of month
SELECT @his_cut_date = SUBSTRING(@his_cut_date, 1, 3) + '01' + SUBSTRING(@his_cut_date, 6, 5)
-- delete the old history
SELECT @command = 'USE msdb EXEC sp_delete_backuphistory ' + char(39) + @his_cut_date + char(39)
PRINT @command
EXEC (@command)
-------------------------------------------------------------------------------------------------------------------------------------------------
PRINT @asterisks
CLEANUP:
DROP TABLE #temp_table
DROP TABLE #file_table
November 22, 2004 at 5:31 pm
One way that I've used is to include a datetime stamp in the filename and then delete the files based on parsing out the filename from the file then doing a compare. Simply do a xp_cmdshell 'dir filename', store the result set in a temp table and delete the filenames that are older than (current datetime - 1 day).
This has worked fairly well for me.
November 23, 2004 at 12:18 pm
Thank You.
I'm sure I can figure out a way based on your suggestions. Now I have to figure out just how automated I want to make it.
Steve
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply