Ramblings of a DBA

Tara Kizer
posts - 165, comments - 832, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

Backup transaction log stored procedure - new version

EDIT: Please see my new version of the isp_Backup stored procedure.

Here's the new version for the backup transaction log stored procedure:

----------------------------------------------------------------------------------------------------
-- 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 :
----------------------------------------------------------------------------------------------------
--
----------------------------------------------------------------------------------------------------
CREATE        PROC isp_Backup_TLog
(@Path VARCHAR(100), @Retention INT = 2)
AS

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

  -- 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

Print | posted on Thursday, September 23, 2004 3:32 PM | Filed Under [ SQL Server - Database Administration ]

Feedback

Gravatar

# Backup transaction log stored procedure

9/23/2004 6:33 PM | Ramblings of a DBA
Gravatar

# Database maintenance routines

9/23/2004 6:37 PM | Ramblings of a DBA
Gravatar

# re: Backup transaction log stored procedure - new version

Hey, given that there's so much duplication between the tx and full backup scripts in the retention area, wouldn't it make sense to break that out to a seperate SP?
9/23/2004 10:25 PM | aiken
Gravatar

# re: Backup transaction log stored procedure - new version

I love these scripts Tara! Nice work. Straight in the vault they go.

Aiken, I can see where you are coming from but having dependencies on admin scripts can be a pain. I like the fact that each is fully contained and can work "solo".

9/26/2004 1:32 PM | DavidM
Gravatar

# re: Backup transaction log stored procedure - new version

I was trying to run the above-mentioned TSQL in the Northwind Database However I was recieved the following error message:

Invalid object name 'msdb.dbo.log_shipping_databases'.

Would you let know how can i fix this progress?
8/5/2005 1:48 PM | NetDeveloper
Gravatar

# re: Backup transaction log stored procedure - new version

These stored procedures were developed with Enterprise Edition for SQL Server 2000. Since these log shipping tables do not exist in certain other versions, you'll need to remove that part from the query.
8/5/2005 6:21 PM | Tara
Gravatar

# re: Backup transaction log stored procedure - new version

Thanks for publishing this.

Some nice alternatives to the ones I am using now.
8/23/2005 2:47 AM | wildmist
Gravatar

# re: Backup transaction log stored procedure - new version

Thanks for posting the great scripts.

I got a weird error when using the script when there was a read only database on the server. Since the readonly dbs are changing anyhow, I added the following:

AND
DATABASEPROPERTYEX([name],'Updateability')<> 'READ_ONLY'

to the query below. Which prevented the weird error.

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
9/12/2005 12:15 PM | Matthew Martin
Gravatar

# re: Backup transaction log stored procedure - new version

there is mistake in the second sp.


SET @command1 = '"' + @path + 'FBZip.exe" -a ' + @path + @fileName2 + ' ' + @fileName

should be as

SET @command1 = '"' + @path + 'FBZip.exe" -a ' + @path + @fileName2 + ' ' + @path + @fileName
11/19/2005 7:47 PM | Dursaliye Yıldız
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET