Ramblings of a DBA

Backup databases stored procedure using SQL LiteSpeed - new version

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

I decided to finish posting the new versions of my database maintenance routines.  Here's the new version for backing up your databases using SQL LiteSpeed:

----------------------------------------------------------------------------------------------------
-- OBJECT NAME         : isp_Backup_LS
--
-- AUTHOR               : Tara Duggan
-- DATE   : December 18, 2003
--
-- INPUTS  : @Path - location of the backups
-- OUTPUTS  : None
-- DEPENDENCIES         : None
--
-- DESCRIPTION         : This stored procedure performs a full backup on all of the user databases
--
-- EXAMPLES (optional)  : EXEC isp_Backup_LS @Path = 'G:\MSSQL\Backup\', @Retention = 5
----------------------------------------------------------------------------------------------------
CREATE           PROC isp_Backup_LS
(@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 @NumProcs INT -- stores the number of processors that the server has registered
DECLARE @RowCnt INT -- stores @@ROWCOUNT
DECLARE @desc VARCHAR(200) -- stores the description of the backup
DECLARE @filename VARCHAR(200) -- stores the path and file name of the bkp file

-- Get the list of the databases to be backed up, does not include master, model, msdb, tempdb, Northwind, or pubs
SELECT name
INTO #WhichDatabase
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master', 'model', 'msdb', 'pubs', 'tempdb', 'Northwind')
ORDER BY name

-- Get the number of processors that the server has
EXEC master..xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = 'SYSTEM\CurrentControlSet\Control\Session Manager',
  @value_name = 'RegisteredProcessors',
  @value = @NumProcs OUTPUT

--  We want n - 1 threads, where n is the number of processors
SELECT @NumProcs = @NumProcs - 1

-- Get the database to be backed up
SELECT TOP 1 @DBName = name
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 .bkp path and file name
 SELECT @filename = @Path + @DBName + '\' + @DBName + '_LS_Full_' + @Now + '.bkp'

 -- Build the description of the backup
 SELECT @desc = 'Full backup of ' + @DBName + ' - ' + CONVERT(VARCHAR(50), GETDATE())
 
 -- 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, NO_OUTPUT

 -- 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 '%<DIR>%' 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 database using xp_backup_database
 EXEC master.dbo.xp_backup_database
  @database = @DBName,
  @filename = @filename,
  @backupname = @DBName,
  @desc = @desc,
  @init = 1,
  @threads = @NumProcs

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

 -- Get the database to be backed up
 SELECT TOP 1 @DBName = name
 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:30 PM | Filed Under [ SQL Server - Database Administration ]