Ramblings of a DBA

Tara Kizer
posts - 166, comments - 837, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

Backup transaction log stored procedure

EDIT: This stored procedure has been updated.

The below stored procedure will backup the transaction log for each of the databases that aren't being log shipped (you don't want to break  log shipping by running a transaction log backup outside of the log shipping plan) and also aren't using SIMPLE for the recovery model.  It has one input parameter, which is the path to where you would like to backup the databases.  It'll create a sub directory underneath that path for each of the databases.  It also deletes any files that are older than two days in that sub directory.  If a sub directory doesn't exist, it creates one for you. 

I've got three more DBA maintenance stored procedures (DBREINDEX, INDEXDEFRAG, and my own version of sp_delete_backuphistory that doesn't use a cursor) that I'm going to post soon.

----------------------------------------------------------------------------------------------------
-- OBJECT NAME         : isp_Backup_TLog
--
-- AUTHOR               : Tara Duggan
-- INPUTS    : @Path - location of the 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 = 'F:\MSSQL\Backup\'
--
-- MODIFICATION HISTORY :
----------------------------------------------------------------------------------------------------
--
----------------------------------------------------------------------------------------------------
CREATE        PROC isp_Backup_TLog
(@Path VARCHAR(100))
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() - 2

  -- 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 Friday, June 18, 2004 1:59 PM | Filed Under [ SQL Server - Database Administration ]

Feedback

Gravatar

# re: Backup transaction log stored procedure

I am getting error
Server: Msg 208, Level 16, State 1, Procedure Usp_Backup_TLog, Line 20
Invalid object name 'msdb.dbo.log_shipping_databases'.

~what can I change in the stored procedure.
When I observe, I saw log_shipping_Primaries and log_shipping_secondaries tables.

Thanks,
Venkat.
7/16/2004 12:13 PM | Venkat
Gravatar

# re: Backup transaction log stored procedure

I should have mentioned that the stored procedure is for SQL Server 2000 Enterprise and Developer Editions only. To fix it for other editions, use this:

-- 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
DATABASEPROPERTYEX([name], 'Recovery') <> 'SIMPLE'
ORDER BY name
7/19/2004 9:13 AM | Tara
Gravatar

# re: Backup transaction log stored procedure

I had copied procedure and run it.
When I excute this procedure,

EXEC isp_Backup_TLog @Path = 'F:\MSSQL\Backup\'
but it doesn't work. I can see anything.

Please help me. Thanks a lot
6/1/2006 8:26 PM | Thienlydochanh
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET