November 30, 2004 at 1:15 pm
Hi - I have a job, which I want to schedule daily - to backup my database onto another partition on the server:
Use master
BACKUP DATABASE bookingcentral TO DISK = "E:\SQLServerBackups\fixit.bak"
This just appends the current database to the BAK file - so my BAK fil is just growing and growing each day.
Is there anyway from within the job, to give the BAK file the current date as the filename - eg: so I have lots of files like:
E:\SQLServerBackups\20041105.bak
E:\SQLServerBackups\20041104.bak
E:\SQLServerBackups\20041103.bak
E:\SQLServerBackups\20041102.bak
E:\SQLServerBackups\20041101.bak
Also, it would be useful for the job to delete any BAK files older than say 5 days - could anyone point me in the right direction for doing this?
Thanks for any help,
Mark
November 30, 2004 at 1:54 pm
You can use a database maintenance plan, it create a job for backup, and each back up file is named like, databasename_date.bck..
in the maintenance you can specify how many day to maintain for recovery..
JR
November 30, 2004 at 2:07 pm
Hi - Thank you.
Only reason I was using a Job was because I'm using XP_SMTP_MAIL dll (as I don't have MAPI/Outlook) - and I think this is the only method that the maintenance plans use.
Thanks again though,
Mark
November 30, 2004 at 3:22 pm
Ok, in this case, yes, you still using your actual process,
but you can write some lines for a job to check the run_status in sysjobhistory in msdb for each job and send email for all the jobs failed.
o write code for rename the files for each backup in the actual process...
thnks
JR
November 30, 2004 at 3:53 pm
Here's some code that will include the date and time in your backup name -
DECLARE
@BKdate varchar(20),
@BKExec varchar(200),
@BKDevice varchar(200),
@path varchar(150),
@DBname varchar(30)
SET @path = 'X:\SQL_Backups\'
SET @DBname = 'PUBS'
SELECT @BKdate= CONVERT(varchar(26),getdate(),21)
SELECT @BKdate = REPLACE(@BKdate,':','-')
SELECT @BKdate = REPLACE(@BKdate,' ','_')
SELECT @BKDevice = @path + @DBname + '_' + @BKdate + 'BKP'
SELECT @BKExec = 'BACKUP DATABASE ' + @DBname + ' TO DISK = ' + '''' + @BKDevice + '''' + ' WITH INIT, STATS=25'
print @BKExec
EXEC (@BKExec)
November 30, 2004 at 3:58 pm
Here's a stored procedure that will delete backup files based on the age given in the filename. Note that it gets retention information and the path for the backups from a SQL table called af_DB_Backup_Ctrl. You could incorporate something like this, or modify the procedure to use parameters instead.
-- af_DBA_delete_full_backups_2000
USE afDBA
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
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
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
December 1, 2004 at 10:31 am
I use procedures similar to the ones listed above. Be careful if you use maintenance plans to delete old files. I've found the way they do it to be fairly eratic and will sometimes delete old files and sometimes wait untill the next day. If space is a huge issue its probably safer to script your own retention procedure so you know exactly what is going on.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply