Tara's sp_Backup_TLog - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Tara's sp_Backup_TLog

Author

Topic

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2005-01-14 : 13:31:30

Tara I think this code is great so I implemented it at my company...

I noticed that the backup process is not removing the old TRN files that it creates after the retention period has
expired...

mydatabase_20050113050001.TRN
.
.
mydatabase_20050110170001.TRN

[blue]
-- Run every 4 hours
-- generates a 140 mg file against a 1 gb database.

sp_Backup_TLog @Path='G:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\', @Retention=1

[\blue]

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Backup_TLog]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_Backup_TLog]
GO

CREATE PROC sp_Backup_TLog
(@Path VARCHAR(100), @Retention INT = 2)
AS

----------------------------------------------------------------------------------------------------
-- OBJECT NAME : isp_Backup_TLog
--
-- AUTHOR: Tara Duggan -
-- DATE : May 12, 2004
--
-- INPUTS : @Path - location of the backups
-- @Retention - number of days to retain transaction log backups
-- OUTPUTS : None
-- DEPENDENCIES : None
--
-- DESCRIPTION : This stored procedure performs a transaction log backup on the non-log
-- shipped user databases that do not have SIMPLE set as the recovery model.
--
-- EXAMPLES (optional) : EXEC isp_Backup_TLog @Path = 'C:\MSSQL\Backup\', @Retention = 5
--
-- MODIFICATION HISTORY :
----------------------------------------------------------------------------------------------------
--
----------------------------------------------------------------------------------------------------
-- Implemented by Clark Baker 12/1/2004
[\green]
SET NOCOUNT ON

DECLARE @Now CHAR(14) -- current date in the form of yyyymmddhhmmss
DECLARE @DBName SYSNAME -- stores the database name that is currently being processed
DECLARE @cmd SYSNAME -- stores the dynamically created DOS command
DECLARE @Result INT -- stores the result of the dir DOS command
DECLARE @RowCnt INT -- stores @@ROWCOUNT
DECLARE @disk VARCHAR(200) -- stores the path and file name of the TRN file

CREATE TABLE #WhichDatabase
(
dbName SYSNAME NOT NULL
)

-- Get the list of the databases to be backed up
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.dbo.sysdatabases
WHERE [name] NOT IN ('master', 'model', 'msdb', 'tempdb') AND
[name] NOT IN (SELECT database_name FROM msdb.dbo.log_shipping_databases) AND
DATABASEPROPERTYEX([name], 'Recovery') <> 'SIMPLE'
ORDER BY name

-- Get the database to be backed up
SELECT TOP 1 @DBName = dbName
FROM #WhichDatabase

SET @RowCnt = @@ROWCOUNT

-- Iterate throught the temp table until no more databases need to be backed up
WHILE @RowCnt <> 0
BEGIN

-- Get the current date using style 120, remove all dashes, spaces, and colons
SELECT @Now = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), '-', ''), ' ', ''), ':', '')

-- Build the .BAK path and file name
SELECT @disk = @Path + @DBName + '\' + @DBName + '_' + @Now + '.TRN'

-- Build the dir command that will check to see if the directory exists
SELECT @cmd = 'dir ' + @Path + @DBName

-- Run the dir command, put output of xp_cmdshell into @result
EXEC @result = master.dbo.xp_cmdshell @cmd

-- If the directory does not exist, we must create it
IF @result <> 0
BEGIN

-- Build the mkdir command
SELECT @cmd = 'mkdir ' + @Path + @DBName

-- Create the directory
EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT

END
-- The directory exists, so let's delete files older than two days
ELSE
BEGIN

-- Stores the name of the file to be deleted
DECLARE @WhichFile VARCHAR(1000)

CREATE TABLE #DeleteOldFiles
(
DirInfo VARCHAR(7000)
)

-- Build the command that will list out all of the files in a directory
SELECT @cmd = 'dir ' + @Path + @DBName + ' /OD'

-- Run the dir command and put the results into a temp table
INSERT INTO #DeleteOldFiles
EXEC master.dbo.xp_cmdshell @cmd

[green]-- Delete all rows from the temp table except the ones that correspond to the files to be deleted
DELETE
FROM #DeleteOldFiles
WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 0 OR DirInfo LIKE '%

%' OR SUBSTRING(DirInfo, 1, 10) >= GETDATE() - @Retention

-- Get the file name portion of the row that corresponds to the file to be deleted
SELECT TOP 1 @WhichFile = SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))
FROM #DeleteOldFiles

SET @RowCnt = @@ROWCOUNT

-- Interate through the temp table until there are no more files to delete
WHILE @RowCnt <> 0
BEGIN

-- Build the del command
SELECT @cmd = 'del ' + @Path + + @DBName + '\' + @WhichFile + ' /Q /F'

-- Delete the file
EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT

-- To move to the next file, the current file name needs to be deleted from the temp table
DELETE
FROM #DeleteOldFiles
WHERE SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo)) = @WhichFile

-- Get the file name portion of the row that corresponds to the file to be deleted
SELECT TOP 1 @WhichFile = SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))
FROM #DeleteOldFiles

SET @RowCnt = @@ROWCOUNT

END

DROP TABLE #DeleteOldFiles

END

-- Backup the transaction log
BACKUP LOG @DBName
TO DISK = @disk
WITH INIT

-- To move onto the next database, the current database name needs to be deleted from the temp table
DELETE
FROM #WhichDatabase
WHERE dbName = @DBName

-- Get the database to be backed up
SELECT TOP 1 @DBName = dbName
FROM #WhichDatabase

SET @RowCnt = @@ROWCOUNT

-- Let the system rest for 5 seconds before starting on the next backup
WAITFOR DELAY '00:00:05'

END

DROP TABLE #WhichDatabase

SET NOCOUNT OFF

RETURN 0

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2005-01-14 : 15:53:48

Ok I believe the problem is with the long file names specifically with spaces in the directory so its cutting off the command at "dir g:\program" rather than "Dir g:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\..."

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2005-01-14 : 16:01:15

It looks like you can get the information from dir /x which returns both long and short file names... Hmmmm

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2005-01-14 : 16:31:53

Ok... I got it and if you did what you did intentially I understand why and I agree.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources