Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sequential number sql hourly tl backups Expand / Collapse
Author
Message
Posted Friday, October 4, 2013 9:58 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 5:24 AM
Points: 94, Visits: 439
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
Post #1501661
Posted Monday, October 7, 2013 4:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:15 AM
Points: 2,837, Visits: 3,957
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
Post #1502059
Posted Tuesday, October 8, 2013 3:38 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 5:24 AM
Points: 94, Visits: 439
Could anybody supply the TSQL for this please
Post #1502512
Posted Tuesday, October 8, 2013 4:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:15 AM
Points: 2,837, Visits: 3,957
READ AND TEST it CAREFULLY

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
Post #1502529
Posted Thursday, October 10, 2013 1:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 6:57 AM
Points: 21, Visits: 306
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 .
Post #1503767
Posted Thursday, October 10, 2013 4:21 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 36,794, Visits: 31,253
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


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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1503819
Posted Friday, October 11, 2013 3:58 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 5:24 AM
Points: 94, Visits: 439
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.
Post #1503933
Posted Friday, October 11, 2013 4:34 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 36,794, Visits: 31,253
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1503939
Posted Friday, October 11, 2013 5:39 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 36,794, Visits: 31,253
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1503960
Posted Wednesday, December 4, 2013 8:05 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, July 26, 2014 8:10 PM
Points: 2,826, Visits: 8,463
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.



Post #1519847
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse