October 4, 2013 at 9:58 am
Hi All,
I wonder if anybody could help with this problem.
We are performing a full backup at 12:01 and then we want TL backup every hour until the next full backup, the next day the TL backups want to be overwritten with the new TL backup, is it possible to have a sequential count in the file name (from 1 -23).
Hope i am not confusing the issue:-D
October 7, 2013 at 4:05 am
clucasi (10/4/2013)
the next day the TL backups want to be overwritten with the new TL backup, is it possible to have a sequential count in the file name (from 1 -23).
yes it can be done ( either from Tsql or wizard).
you will find a option for deleting files in maintenance wizard too
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 8, 2013 at 3:38 am
Could anybody supply the TSQL for this please:-)
October 8, 2013 at 4:11 am
READ AND TEST it CAREFULLY :exclamation:
IF EXISTS (SELECT [name] FROM dbo.sysobjects WHERE [name] = 'usp_Admin_Delete_Files_By_Date' AND TYPE = 'P')
DROP PROCEDURE dbo.usp_Admin_Delete_Files_By_Date
GO
CREATE PROCEDURE dbo.usp_Admin_Delete_Files_By_Date (@SourceDir varchar(1024), @SourceFile varchar(512), @DaysToKeep int)
-- EXEC Admin.dbo.usp_Admin_Delete_Files_By_Date @SourceDir = '\\FooServer\BarShare\'
-- , @SourceFile = 'FooFile_*'
-- , @DaysToKeep = 3
AS
/******************************************************************************
**
**Name: usp_Admin_Delete_Files_By_Date.sql
**
**Description: Delete files older than X-days based on path & extension.
**
**Depending on the output from xp_msver, we will execute either a
**Windows 2000 or Windows 2003 specific INSERT INTO #_File_Details_02
**operation as there is a small difference in the FOR output between
**Windows 2000 and 2003 (Operating system versions).
**
**Return values: 0 - Success
**-1 - Error
**
**Author: G. Rayburn
**
**Date: 03/26/2007
**
**Depends on: xp_cmdshell access to @SourceDir via SQLAgent account.
**
*******************************************************************************
**Modification History
*******************************************************************************
**
**Initial Creation: 03/26/2007 G. Rayburn
**
*******************************************************************************
**
******************************************************************************/
SET NOCOUNT ON
DECLARE @CurrentFileDate char(10)
, @OldFileDate char(10)
, @SourceDirFOR varchar(255)
, @FileName varchar(512)
, @DynDelete varchar(512)
, @ProcessName varchar(150)
, @OSVersion decimal(3,1)
, @Error int
SET @ProcessName = 'usp_Admin_Delete_Files_By_Date - [' + @SourceFile + ']'
SET @CurrentFileDate = CONVERT(char(10),getdate(),121)
SET @OldFileDate = CONVERT(char(10),DATEADD(dd,-@DaysToKeep,@CurrentFileDate),121)
SET @SourceDirFOR = 'FOR %I IN ("' + @SourceDir + @SourceFile + '") DO @ECHO %~nxtI'
SET @Error = 0
-- Get Windows OS Version info for proper OSVer statement block exec.
CREATE TABLE #_OSVersion
( [Index] int
, [Name] varchar(255)
, [Internal_Value] varchar(255)
, [Character_Value] varchar(255) )
INSERT INTO #_OSVersion
EXEC master..xp_msver 'WindowsVersion'
SET @OSVersion = (SELECT SUBSTRING([Character_Value],1,3) FROM #_OSVersion)
-- Start temp table population(s).
CREATE TABLE #_File_Details_01
( Ident int IDENTITY(1,1)
, Output varchar(512) )
INSERT INTO #_File_Details_01
EXEC master..xp_cmdshell @SourceDirFOR
CREATE TABLE #_File_Details_02
(Ident int
, [TimeStamp] datetime
, [FileName] varchar(255) )
-- OS Version specifics.
IF @OSVersion = '5.0'
BEGIN -- Exec Windows 2000 version.
INSERT INTO #_File_Details_02
SELECT Ident
, CONVERT(datetime, LEFT(CAST(SUBSTRING([Output],1,8) AS datetime),12)) AS [TimeStamp]
, SUBSTRING([Output],17,255) AS [FileName]
FROM #_File_Details_01
WHERE [Output] IS NOT NULL
ORDER BY Ident
END
IF @OSVersion = '5.2'
BEGIN -- Exec Windows 2003 version.
INSERT INTO #_File_Details_02
SELECT Ident
, CONVERT(char(10), SUBSTRING([Output],1,10), 121) AS [TimeStamp]
, SUBSTRING([Output],21,255) AS [FileName]
FROM #_File_Details_01
WHERE [Output] IS NOT NULL
ORDER BY Ident
END
-- Start delete ops cursor.
DECLARE curDelFile CURSOR
READ_ONLY
FOR
SELECT [FileName]
FROM #_File_Details_02
WHERE [TimeStamp] <= @OldFileDate
OPEN curDelFile
FETCH NEXT FROM curDelFile INTO @FileName
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @DynDelete = 'DEL /Q "' + @SourceDir + @FileName + '"'
EXEC master..xp_cmdshell @DynDelete
END
FETCH NEXT FROM curDelFile INTO @FileName
END
CLOSE curDelFile
DEALLOCATE curDelFile
DROP TABLE #_OSVersion
DROP TABLE #_File_Details_01
DROP TABLE #_File_Details_02
GO
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 10, 2013 at 1:34 pm
You can use View T-SQL option through wizard.
one for Backup and other one for Maintenance cleanup task - copy that script and run as Job .
October 10, 2013 at 4:21 pm
clucasi (10/4/2013)
Hi All,I wonder if anybody could help with this problem.
We are performing a full backup at 12:01 and then we want TL backup every hour until the next full backup, the next day the TL backups want to be overwritten with the new TL backup, is it possible to have a sequential count in the file name (from 1 -23).
Hope i am not confusing the issue:-D
Since each file has a date/time associated with it, I believe that sequentially numbering backups is an overcomplication that should probably be avoided. In other words, keep it simple. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 11, 2013 at 3:58 am
The main reason for backing up sequentially is because we are taking a further backup on a tape drive (TSM), if the backups are date based it backs each file, so we get a new on the tape each day, if it was sequentially it would overwrite yesterdays backup.
October 11, 2013 at 4:34 am
clucasi (10/11/2013)
The main reason for backing up sequentially is because we are taking a further backup on a tape drive (TSM), if the backups are date based it backs each file, so we get a new on the tape each day...
That's exactly the way you want it to happen. Spend a little money on buying tapes and come up with a proper tape rotation. I don't know when it will happen, of course, but overwriting tapes every night will cost you someday.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 11, 2013 at 5:39 am
Speak of the devil...
Here's a post from today where they did just as you are doing with overwriting backups. They are living to regret it...
http://www.sqlservercentral.com/Forums/Topic1503753-391-1.aspx
--Jeff Moden
Change is inevitable... Change for the better is not.
December 4, 2013 at 8:05 pm
clucasi (10/11/2013)
The main reason for backing up sequentially is because we are taking a further backup on a tape drive (TSM), if the backups are date based it backs each file, so we get a new on the tape each day, if it was sequentially it would overwrite yesterdays backup.
Don't you want more than 1 day of backups ? Or is it a low priority database ?
You could just use a Maintenance Plan, write your backups to a folder, and keep as much as you want in the folder. Then have your tape back up job back up the folder, so it will grab whatever you want to keep.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply