Ramblings of a DBA

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

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

Backup databases stored procedure - SQL LiteSpeed version

EDIT: This stored procedure has been updated.

Yesterday, I posted a stored procedure that performs a full backup on the databases. I mentioned that I had a SQL LiteSpeed version.  This version backs up all databases except master, model, msdb, tempdb, pubs, NorthWind.  It has one input parameters, 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.  It checks how many processors exist in the database server using xp_regread since SQL LiteSpeed can perform better on a multi-processor server.  We want n-1, for n processors according to the documentation.  I haven't checked if it fails when there is only one processor, since 1-1 equals 0 and @threads is 1 - 32.  I just don't have a single processor server with SQL LiteSpeed installed.

----------------------------------------------------------------------------------------------------
-- OBJECT NAME         : isp_Backup_LS
--
-- AUTHOR                    : Tara Duggan
-- 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\'
----------------------------------------------------------------------------------------------------
CREATE        PROC isp_Backup_LS
(@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 @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

 -- 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 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, June 17, 2004 4:33 PM | Filed Under [ SQL Server - Database Administration ]

Feedback

Gravatar

# re: Backup databases stored procedure - SQL LiteSpeed version

Yes I have a valid login and license. I am using their xp_backup_database stored procedure in my stored proc. I wanted it to do more than what theirs offers.
6/18/2004 9:05 AM | Tara
Gravatar

# re: Backup databases stored procedure - SQL LiteSpeed version

They have a stored procedure that does this already built on top of their xp_backup_database.

Just search the downloads.
6/18/2004 3:49 PM | Valter Borges
Gravatar

# Here it is.

--
-- prc_DoSLSBackup
-- Authors: Jeffrey Aven, DBassociatesIT Pty Ltd
--
-- Revision History:
-- 07/02 – 1.0 (Initial Version)
-- 11/02 - 1.1 Added Differential Backup Functionality (J. Aven)
-- 22/03 - 1.2 Added CompressionLevel Input Parameter (J. Aven)
-- Added Support for Exclusion of Databases (J. Aven)
-- 24/09 - 1.3 Added Error Handling for Concurrent Backup Operations (R. Risotto, J.Aven)
-- 15/10 - 1.4 Fixed Imtermittent Issue with Excluded DBs (J. O'Brien, J.Aven)
-- Continues backing up after DBCC error returns error at the end of the procedure (B. Reynen, J.Aven)
-- 16/10 - 1.5 Added support for creating subdirectory for each database (J.Aven)
-- 28/11 - 1.501 Added support for logging, removed compression level (J.Aven)
-- 03/12 - 1.502 Added support for servername directory (J.Aven)


CREATE PROCEDURE prc_DoSLSBackup
@BackupType char(1) = 'F'
--'D' for Differential Backups
--'F' for Full Database Backup (Default)
--'L' for Transaction Log Backup
,@DBName sysname = '*'
--'*' for Backup All Databases (Default)
--'<database_name>' to backup an individual database
,@BackupDir varchar(1024)
--Directory to store SQL LiteSpeed Backups
,@DoVerify bit = 1
--1 = Perform Verification of SQL LIteSpeed Backups (Default)
--0 = Skip Verification
,@UseCmdLine bit = 0
--1 = Use SQL LiteSpeed Command Line Interface
--0 = Use SQL LiteSpeed Extended Stored Procedure Interface (Default)
,@Debug int = 0
--2 = Print verbose logging and generate SQL LiteSpeed log files
--1 = Print verbose logging
--0 = Minimal logging (Default)
,@EncryptionKey varchar(1024) = NULL
--Encryption Key used to secure SQL LiteSpeed Backup Devices (Optional)
,@Threads int = NULL
--Number of Threads to use for SQL LiteSpeed Backup, dynamically determined if not supplied
,@Priority int = NULL
--Base priority of SQL LiteSpeed Backup process, dynamically determined if not supplied
,@RetainDays int = NULL
--Number of days to retain backup device files, if supplied backup files older than the number of days specified
--will be purged
,@InitBackupDevice bit = 0
--1 = Reinitialize backup device without date time stamp
--0 = Create a new device for each backup which has the date time stamp embedded in the file name (Default)
,@PerformDBCC bit = 1
--1 = Perform DBCC CHECKDB prior to backing up database (default)
--0 = Do not Perform DBCC CHECKDB prior to backing up database
,@ExcludedDBs varchar(2048) = NULL
--Comma Delimited List of Databases in Double Quotes to be Excluded From Backup Operation
--CAUTION: Ensure proper syntax (eg '"pubs","Northwind"')
,@create_sub_dir bit = 0
--Creates a subdirectory under the backup directory for each db being backed up
,@create_server_dir bit = 0
--Creates a directory under the backup directory for the current server, used for scenarios where muliple servers
--are backing up to the same location, ensures no namespace conflicts

AS
SET ARITHABORT ON
SET NOCOUNT ON
--Local Variables
DECLARE @DBStatus int
,@DBMode varchar(50)
,@StatusMsgPrefix char(18)
,@StatusMsg varchar(1024)
,@PhyName varchar(1024)
,@BackupType2 varchar(4)
,@Cmd varchar(1024)
,@RC int
,@dbid int
,@BackupStartTime varchar(12)
,@BackupAllDBs bit
,@Operation varchar(6)
,@CmdStr varchar(255)
,@BUFile varchar(255)
,@BUFileDate char(12)
,@BUFileDate2 smalldatetime
,@BaseBUFileDatalength int
,@sqlstr varchar(1048)
,@is_db_excluded bit
,@orig_backup_dir varchar(512)

SELECT @StatusMsgPrefix = 'prc_DoSLSBackup : '
SELECT @BackupAllDBs = 0
SELECT @orig_backup_dir = @BackupDir

--Check Access Level
IF IS_SRVROLEMEMBER ( 'sysadmin') = 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + ' Error - Insufficient system access for ' + SUSER_SNAME()
+ ' to perform SQLLiteSpeed backup'
RAISERROR(@StatusMsg,16,1)
RETURN 1
END

--Validate @BackupType Argument
IF @BackupType IN ('L', 'F', 'D')
BEGIN
IF @BackupType = 'L'
SELECT @BackupType2 = 'Log'
IF @BackupType = 'F'
SELECT @BackupType2 = 'Full'
IF @BackupType = 'D'
SELECT @BackupType2 = 'Diff'
END
ELSE
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + ' Error - Valid parameters for @BackupType are L,F,D'
RAISERROR(@StatusMsg,16,1)
RETURN 1
END

--Validate @DBName Argument
IF @DBName <> '*'
BEGIN
IF NOT EXISTS (SELECT [name] FROM master.dbo.sysdatabases WHERE [name] = @DBName)
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + ' Error - Invalid database selected for @DBName (' + @DBName
+ ') parameter'
RAISERROR(@StatusMsg,16,1)
RETURN 1
END
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + @DBName + ' Selected for ' + @BackupType2 + ' Backup'
PRINT @StatusMsg
END
GOTO DoBackup
END
ELSE
BEGIN
SELECT @BackupAllDBs = 1
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + ' All Databases Selected for ' + @BackupType2 + ' Backup'
PRINT @StatusMsg
END
END

DECLARE DBs CURSOR FOR
SELECT name, dbid, status
FROM master..sysdatabases
WHERE [name] <> 'tempdb'
FOR READ ONLY

OPEN DBs
FETCH NEXT FROM DBs INTO @DBName, @dbid, @DBStatus
WHILE @@FETCH_STATUS = 0
BEGIN
--Is Databases Explicitly Excluded
IF @ExcludedDBs IS NOT NULL
BEGIN
SELECT @ExcludedDBs = REPLACE(@ExcludedDBs,'"',char(39))
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE [name] = 'tmp_is_db_excluded')
DROP TABLE tempdb..tmp_is_db_excluded

CREATE TABLE tempdb..tmp_is_db_excluded
(
is_db_excluded bit
)
-- J. O'Brien need to reset bit field to 0 as if tmp_is_db_excluded is empty
SELECT @is_db_excluded = 0
SELECT @sqlstr = 'IF ' + char(39) + @DBName + char(39) + ' IN (' + @ExcludedDBs + ') INSERT tempdb..tmp_is_db_excluded SELECT 1'
EXEC (@sqlstr)
SELECT @is_db_excluded = is_db_excluded FROM tempdb..tmp_is_db_excluded
IF @is_db_excluded = 1
BEGIN
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + ' Skippping ' + @DBName + ' as this database has been explicitly excluded'
PRINT @StatusMsg
END
GOTO NextDB
END
END
DoBackup:
SELECT @Operation = 'Backup'
SELECT @BackupStartTime = CONVERT(varchar(12),GETDATE(),12) + REPLACE(CONVERT(varchar(12),GETDATE(),8),':','')
IF @BackupType = 'L'
BEGIN
--Check for System Database
IF @dbid <= 4
BEGIN
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + 'System Database (' + @DBName
+ ') skipped for transaction log backup'
PRINT @StatusMsg
END
IF @BackupAllDBs = 1
GOTO NextDB
ELSE
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + ' Error - Unable to backup ' + @DBName
+ ' - System Database'
RAISERROR(@StatusMsg,16,1)
RETURN 1
END
END
--Check for Simple Recovery Model
IF @DBStatus & 8 <> 0
BEGIN
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + 'Database (' + @DBName
+ ') skipped for transaction log backup - Simple Recovery Model'
PRINT @StatusMsg
END
IF @BackupAllDBs = 1
GOTO NextDB
ELSE
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + ' Error - Unable to backup '
+ @DBName + ' - Simple Recovery Model'
RAISERROR(@StatusMsg,16,1)
RETURN 1
END
END
END

--Check Database Accessibility
SELECT @DBMode = 'OK'
IF DATABASEPROPERTY(@DBName, 'IsDetached') > 0
SELECT @DBMode = 'Detached'
ELSE IF DATABASEPROPERTY(@DBName, 'IsInLoad') > 0
SELECT @DBMode = 'Loading'
ELSE IF DATABASEPROPERTY(@DBName, 'IsNotRecovered') > 0
SELECT @DBMode = 'Not Recovered'
ELSE IF DATABASEPROPERTY(@DBName, 'IsInRecovery') > 0
SELECT @DBMode = 'Recovering'
ELSE IF DATABASEPROPERTY(@DBName, 'IsSuspect') > 0
SELECT @DBMode = 'Suspect'
ELSE IF DATABASEPROPERTY(@DBName, 'IsOffline') > 0
SELECT @DBMode = 'Offline'
ELSE IF DATABASEPROPERTY(@DBName, 'IsEmergencyMode') > 0
SELECT @DBMode = 'Emergency Mode'
ELSE IF DATABASEPROPERTY(@DBName, 'IsShutDown') > 0
SELECT @DBMode = 'Shut Down (problems during startup)'
IF @DBMode <> 'OK'
BEGIN
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + 'Unable to backup ' + @DBName
+ ' - Database is in ' + @DBMode + ' state'
PRINT @StatusMsg
END
IF @BackupAllDBs = 1
GOTO NextDB
ELSE
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + ' Error - Unable to backup ' + @DBName
+ ' - Database is in ' + @DBMode + ' state'
RAISERROR(@StatusMsg,16,1)
RETURN 1
END
END
--Check if Backup Directory Exists
IF @create_server_dir = 1 and @create_sub_dir = 1
SELECT @BackupDir = @orig_backup_dir + '\' + @@SERVERNAME + '\' + @DBName
IF @create_server_dir = 1 and @create_sub_dir = 0
SELECT @BackupDir = @orig_backup_dir + '\' + @@SERVERNAME
IF @create_server_dir = 0 and @create_sub_dir = 1
SELECT @BackupDir = @orig_backup_dir + '\' + @DBName

SELECT @Cmd = 'dir "' + @BackupDir + '"'
EXEC @RC = master..xp_cmdshell @Cmd, NO_OUTPUT
IF @RC <> 0
--Create Backup Directory
BEGIN
SELECT @Cmd = 'md "' + @BackupDir + '"'
EXEC @RC = master.dbo.xp_cmdshell @Cmd, NO_OUTPUT
IF @RC <> 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + ' Error - Unable to create backup directory ('
+ @BackupDir + ')'
RAISERROR(@StatusMsg,16,1)
RETURN 1
END
END

--Build the Backup File Name
SELECT @PhyName = @BackupDir + '\' + REPLACE(@@SERVERNAME,'\','_') + '.' + REPLACE(@DBName,'.','_')
+ '.' + @BackupType2
IF @InitBackupDevice = 0
--New Device For Each Backup
SELECT @PhyName = @PhyName + @BackupStartTime + '.SLS'
ELSE
--Re-Initialize Each Backup Device
SELECT @PhyName = @PhyName + '.SLS'

--Set Tuning Defaults
IF @Threads IS NULL
BEGIN
CREATE TABLE #MSVer
(
[Index] int
,[Name] varchar (255)
,Internal_Value int NULL
,Charater_Value varchar(255)
)
INSERT #MSVer EXEC master..xp_msver
SELECT @Threads = Internal_Value FROM #MSVer WHERE [Name] = 'ProcessorCount'
DROP TABLE #MSVer
END
IF @Priority IS NULL
SELECT @Priority = 0
--Do DBCC CHECKDB
IF @PerformDBCC = 1
BEGIN
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + 'Executing DBCC CHECKDB on Database ' + @DBName
+ char(10)
PRINT @StatusMsg
DBCC CHECKDB (@DBName)
END
ELSE
DBCC CHECKDB (@DBName) WITH NO_INFOMSGS
SET @RC = @@ERROR
IF @RC <> 0
BEGIN
SELECT @Operation = 'DBCC CHECKDB'
GOTO FailedBackup
END
END

--Do Backup
IF @BackupType = 'F'
--Perform Full Database Backup
BEGIN
IF @UseCmdLine = 1
BEGIN
SELECT @Cmd = char(34) + 'C:\Program Files\DBAssociates\SQLLiteSpeed\SQLLiteSpeed.EXE' + char(34)
+ ' -S' + @@SERVERNAME
+ ' -BDatabase'
+ ' -D' + @DBName
+ ' -F' + @PhyName
+ ' -t' + CONVERT(varchar(2),@Threads)
+ ' -p' + CONVERT(varchar(2),@Priority)
+ ' -I'
+ ' -T'
IF @EncryptionKey IS NOT NULL
SELECT @Cmd = @Cmd + ' -K' + @EncryptionKey
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + 'Executing Backup of Database ' + @DBName
PRINT @StatusMsg
SELECT @StatusMsg = @StatusMsgPrefix + 'Command to be Executed : ' + char(10)
+ char(9) + @Cmd + char(10)
PRINT @StatusMsg
EXEC @RC = master..xp_cmdshell @Cmd
END
ELSE
EXEC @RC = master..xp_cmdshell @Cmd, NO_OUTPUT
END
ELSE
BEGIN
SELECT @Cmd = 'EXEC master..xp_backup_database' + char(10)
+ char(9) + '@database = ' + char(39) + @DBName + char(39) + char(10)
+ char(9) + ', @filename = ' + char(39) + @PhyName + char(39) + char(10)
+ char(9) + ', @threads = ' + CONVERT(varchar(2),@Threads) + char(10)
+ char(9) + ', @priority = ' + CONVERT(varchar(2),@Priority) + char(10)
+ char(9) + ', @init = 1' + char(10)
+ char(9) + ', @logging = ' + CONVERT(char(1),@Debug) + char(10)
IF @EncryptionKey IS NOT NULL
SELECT @Cmd = @Cmd + char(9) + ', @encryptionkey = ' + char(39)
+ @EncryptionKey + char(39) + char(10)
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + 'Executing Backup of Database '
+ @DBName
PRINT @StatusMsg
SELECT @StatusMsg = @StatusMsgPrefix + 'Command to be Executed : ' + char(10)
+ char(9) + @Cmd + char(10)
PRINT @StatusMsg
END
IF @EncryptionKey IS NOT NULL
EXEC @RC = master..xp_backup_database
@database = @DBName
,@filename = @PhyName
,@threads = @Threads
,@priority = @Priority
,@init = 1
,@encryptionkey = @EncryptionKey
,@logging = @Debug
ELSE
EXEC @RC = master..xp_backup_database
@database = @DBName
,@filename = @PhyName
,@threads = @Threads
,@priority = @Priority
,@init = 1
,@logging = @Debug
END
IF @RC <> 0
GOTO FailedBackup
END

IF @BackupType = 'D'
--Perform Differential Database Backup
BEGIN
IF @DBName = 'master'
GOTO NextDB
IF @UseCmdLine = 1
BEGIN
SELECT @Cmd = char(34) + 'C:\Program Files\DBAssociates\SQLLiteSpeed\SQLLiteSpeed.EXE' + char(34)
+ ' -S' + @@SERVERNAME
+ ' -BDatabase'
+ ' -D' + @DBName
+ ' -F' + @PhyName
+ ' -t' + CONVERT(varchar(2),@Threads)
+ ' -p' + CONVERT(varchar(2),@Priority)
+ ' -I'
+ ' -T'
+ ' -WDIFFERENTIAL'
IF @EncryptionKey IS NOT NULL
SELECT @Cmd = @Cmd + ' -K' + @EncryptionKey
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + 'Executing Backup of Database ' + @DBName
PRINT @StatusMsg
SELECT @StatusMsg = @StatusMsgPrefix + 'Command to be Executed : ' + char(10)
+ char(9) + @Cmd + char(10)
PRINT @StatusMsg
EXEC @RC = master..xp_cmdshell @Cmd
END
ELSE
EXEC @RC = master..xp_cmdshell @Cmd, NO_OUTPUT
END
ELSE
BEGIN
SELECT @Cmd = 'EXEC master..xp_backup_database' + char(10)
+ char(9) + '@database = ' + char(39) + @DBName + char(39) + char(10)
+ char(9) + ', @filename = ' + char(39) + @PhyName + char(39) + char(10)
+ char(9) + ', @threads = ' + CONVERT(varchar(2),@Threads) + char(10)
+ char(9) + ', @priority = ' + CONVERT(varchar(2),@Priority) + char(10)
+ char(9) + ', @init = 1' + char(10)
+ char(9) + ', @with = ' + char(39) + 'DIFFERENTIAL' + char(39) + char(10)
+ char(9) + ', @logging = ' + CONVERT(char(1),@Debug) + char(10)
IF @EncryptionKey IS NOT NULL
SELECT @Cmd = @Cmd + char(9) + ', @encryptionkey = ' + char(39)
+ @EncryptionKey + char(39) + char(10)
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + 'Executing Backup of Database ' + @DBName
PRINT @StatusMsg
SELECT @StatusMsg = @StatusMsgPrefix + 'Command to be Executed : ' + char(10)
+ char(9) + @Cmd + char(10)
PRINT @StatusMsg
END
IF @EncryptionKey IS NOT NULL
EXEC @RC = master..xp_backup_database
@database = @DBName
,@filename = @PhyName
,@threads = @Threads
,@priority = @Priority
,@init = 1
,@encryptionkey = @EncryptionKey
,@with = 'DIFFERENTIAL'
,@logging = @Debug
ELSE
EXEC @RC = master..xp_backup_database
@database = @DBName
,@filename = @PhyName
,@threads = @Threads
,@priority = @Priority
,@init = 1
,@with = 'DIFFERENTIAL'
,@logging = @Debug
END
IF @RC <> 0
BEGIN
IF @RC = 11704
BEGIN
GOTO NextDB
END
ELSE
BEGIN
GOTO FailedBackup
END
END
END

IF @BackupType = 'L'
--Perform Transaction Log Backup
BEGIN
IF @UseCmdLine = 1
BEGIN
SELECT @Cmd = char(34) + 'C:\Program Files\DBAssociates\SQLLiteSpeed\SQLLiteSpeed.EXE' + char(34)
+ ' -S' + @@SERVERNAME
+ ' -BLog'
+ ' -D' + @DBName
+ ' -F' + @PhyName
+ ' -t' + CONVERT(varchar(2),@Threads)
+ ' -p' + CONVERT(varchar(2),@Priority)
+ ' -I'
+ ' -T'
IF @EncryptionKey IS NOT NULL
SELECT @Cmd = @Cmd + ' -K' + @EncryptionKey
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + 'Executing Backup of Database ' + @DBName
PRINT @StatusMsg
SELECT @StatusMsg = @StatusMsgPrefix + 'Command to be Executed : ' + char(10)
+ char(9) + @Cmd + char(10)
PRINT @StatusMsg
EXEC @RC = master..xp_cmdshell @Cmd
END
ELSE
EXEC @RC = master..xp_cmdshell @Cmd, NO_OUTPUT
END
ELSE
BEGIN
SELECT @Cmd = 'EXEC master..xp_backup_log' + char(10)
+ char(9) + '@database = ' + char(39) + @DBName + char(39) + char(10)
+ char(9) + ', @filename = ' + char(39) + @PhyName + char(39) + char(10)
+ char(9) + ', @threads = ' + CONVERT(varchar(2),@Threads) + char(10)
+ char(9) + ', @priority = ' + CONVERT(varchar(2),@Priority) + char(10)
+ char(9) + ', @init = 1' + char(10)
+ char(9) + ', @logging = ' + CONVERT(char(1),@Debug) + char(10)
IF @EncryptionKey IS NOT NULL
SELECT @Cmd = @Cmd + char(9) + ', @encryptionkey = ' + char(39)
+ @EncryptionKey + char(39) + char(10)
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + 'Executing Backup of Database ' + @DBName
PRINT @StatusMsg
SELECT @StatusMsg = @StatusMsgPrefix + 'Command to be Executed : ' + char(10)
+ char(9) + @Cmd + char(10)
PRINT @StatusMsg
END
IF @EncryptionKey IS NOT NULL
EXEC @RC = master..xp_backup_log
@database = @DBName
,@filename = @PhyName
,@threads = @Threads
,@priority = @Priority
,@init = 1
,@encryptionkey = @EncryptionKey
,@logging = @Debug
ELSE
EXEC @RC = master..xp_backup_log
@database = @DBName
,@filename = @PhyName
,@threads = @Threads
,@priority = @Priority
,@init = 1
,@logging = @Debug
END
IF @RC <> 0
BEGIN
IF @RC = 11704
BEGIN
GOTO NextDB
END
ELSE
BEGIN
GOTO FailedBackup
END
END
END
--Verify Backup Device
IF @DoVerify = 1
BEGIN
IF @UseCmdLine = 1
BEGIN
SELECT @Cmd = char(34) + 'C:\Program Files\DBAssociates\SQLLiteSpeed\SQLLiteSpeed.EXE' + char(34)
+ ' -S' + @@SERVERNAME
+ ' -RVerifyonly'
+ ' -F' + @PhyName
IF @EncryptionKey IS NOT NULL
SELECT @Cmd = @Cmd + ' -K' + @EncryptionKey
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + 'Executing Verifyonly of Backup Device '
+ @PhyName
PRINT @StatusMsg
SELECT @StatusMsg = @StatusMsgPrefix + 'Command to be Executed : ' + char(10)
+ char(9) + @Cmd + char(10)
PRINT @StatusMsg
EXEC @RC = master..xp_cmdshell @Cmd
END
ELSE
EXEC @RC = master..xp_cmdshell @Cmd, NO_OUTPUT
END
ELSE
BEGIN
SELECT @Cmd = 'EXEC master..xp_restore_verifyonly' + char(10)
+ char(9) + ' @filename = ' + char(39) + @PhyName + char(39) + char(10)
IF @EncryptionKey IS NOT NULL
SELECT @Cmd = @Cmd + char(9) + ', @encryptionkey = ' + char(39)
+ @EncryptionKey + char(39) + char(10)
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + 'Executing Verifyonly of Backup Device '
+ @PhyName
PRINT @StatusMsg
SELECT @StatusMsg = @StatusMsgPrefix + 'Command to be Executed : ' + char(10)
+ char(9) + @Cmd + char(10)
PRINT @StatusMsg
END
IF @EncryptionKey IS NOT NULL
EXEC @RC = master..xp_restore_verifyonly
@filename = @PhyName
,@encryptionkey = @EncryptionKey
,@logging = @Debug
ELSE
EXEC @RC = master..xp_restore_verifyonly
@filename = @PhyName
,@logging = @Debug
END
IF @RC <> 0
BEGIN
SELECT @Operation = 'Verify'
GOTO FailedBackup
END
END

--Delete Old Backup Files
IF @RetainDays IS NOT NULL AND @InitBackupDevice = 0
BEGIN
--Building up Table of Files to Delete
CREATE TABLE #DirOut
(
[Output] varchar(255)
)
SELECT @CmdStr = 'dir "' + @BackupDir + '\' + REPLACE(@@SERVERNAME,'\','_') + '.'
+ REPLACE(@DBName,'.','_') + '.' + @BackupType2 + '*.SLS" /B'
SELECT @BaseBUFileDatalength = LEN(REPLACE(@@SERVERNAME,'\','_') + '.'
+ REPLACE(@DBName,'.','_') + '.' + @BackupType2)
INSERT #DirOut EXEC master..xp_cmdshell @CmdStr
--Scroll Through Table
DECLARE BUFiles CURSOR FOR
SELECT [Output] FROM #DirOut WHERE [Output] IS NOT NULL
FOR READ ONLY
OPEN BUFiles
FETCH NEXT FROM BUFiles INTO @BUFile
WHILE @@FETCH_STATUS = 0
BEGIN
--Reconstruct DateTime From Filename
SELECT @BUFileDate = LEFT(REPLACE(SUBSTRING(@BUFile,@BaseBUFileDatalength + 1,LEN(@BUFile)),'.SLS',''),6)
SELECT @BUFileDate2 = CONVERT(smalldatetime,@BUFileDate,12)
--Compare Date
IF @BUFile <> (REPLACE(@@SERVERNAME,'\','_') + '.' + REPLACE(@DBName,'.','_') + '.'
+ @BackupType2 + '.SLS')
BEGIN
IF DATEDIFF(d,@BUFileDate2,getdate()) > @RetainDays
BEGIN
SELECT @CmdStr = 'del "' + @BackupDir + '\' + @BUFile + '"'
IF @Debug > 0
BEGIN
SELECT @StatusMsg = @StatusMsgPrefix + 'Deleting File : '
+ @BUFile
PRINT @StatusMsg
SELECT @StatusMsg = @StatusMsgPrefix
+ 'Command to be Executed : ' + @CmdStr
PRINT @StatusMsg
EXEC @RC = master..xp_cmdshell @CmdStr
END
ELSE
EXEC @RC = master..xp_cmdshell @CmdStr, NO_OUTPUT
END
END
FETCH NEXT FROM BUFiles INTO @BUFile
END
CLOSE BUFiles
DEALLOCATE BUFiles
DROP TABLE #DirOut
END

IF @BackupAllDBs = 0
GOTO NoCursor
ELSE
GOTO NextDB

FailedBackup:
SELECT @StatusMsg = @StatusMsgPrefix + ' Error - ' + @Operation + ' Operation Failed for '
+ @BackupType + ' Backup of ' + @DBName
RAISERROR(@StatusMsg,16,1)
IF @BackupAllDBs = 0
GOTO NoCursor
ELSE
GOTO NextDB
NextDB:
FETCH NEXT FROM DBs INTO @DBName, @dbid, @DBStatus
END

CLOSE DBs
DEALLOCATE DBs

NoCursor:




GO
6/18/2004 4:01 PM | Valter Borges
Gravatar

# re: Backup databases stored procedure - SQL LiteSpeed version

I'm pretty happy with mine. Theirs uses a cursor. You have to use some kind of loop to do it, but I'd rather use a WHILE loop than a cursor.
6/18/2004 4:06 PM | Tara
Gravatar

# re: Backup databases stored procedure - SQL LiteSpeed version

I'm just curious what kind of compression your getting with litespeed. I'm very happy with mine but i was curious what others were getting? what's your largest db and what does it go down to? My 280GB goes down to 50 for it's fulls and usually about 25 for it's diffs. Thanks


patrick.wright@aruplab.com
7/8/2004 6:40 AM | Pat Wright
Gravatar

# re: Backup databases stored procedure - SQL LiteSpeed version

Mine get down by 75% usually. I haven't run any diffs, so I can't comment on that.
7/8/2004 9:18 AM | Tara
Gravatar

# re: Backup databases stored procedure - SQL LiteSpeed version

I am having problems with the prc_DoSLSBackup scirpt. It does not resume wel when it fails to backup a database. isp_Backup_LS script does work well at resuming but does not provide me with the same customazation with the legth the duration the backups are kept and it does not provide the function to verfy the backup. Can anyone help?
7/23/2004 10:12 AM | Andrew
Gravatar

# re: Backup databases stored procedure - SQL LiteSpeed version

I am having problems with the prc_DoSLSBackup scirpt. It does not resume wel when it fails to backup a database. isp_Backup_LS script does work well at resuming but does not provide me with the same customazation with the legth the duration the backups are kept and it does not provide the function to verfy the backup. Can anyone help?
7/23/2004 10:13 AM | Andrew
Gravatar

# re: Backup databases stored procedure - SQL LiteSpeed version

Change this line:

WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 0 OR DirInfo LIKE '%<DIR>%' OR SUBSTRING(DirInfo, 1, 10) >= GETDATE() - 2


to

WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 0 OR DirInfo LIKE '%<DIR>%' OR SUBSTRING(DirInfo, 1, 10) >= GETDATE() - @Retention


Then change this

CREATE PROC isp_Backup_LS
(@Path VARCHAR(100))
AS


to

CREATE PROC isp_Backup_LS
(@Path VARCHAR(100), @Retention INT)
AS



So you'll now be able to pass in the number of days to retain the backup:

EXEC isp_Backup_LS @Path = 'G:\MSSQL\Backup\', @Retention = 5


7/23/2004 10:14 AM | Tara
Gravatar

# re: Backup databases stored procedure - SQL LiteSpeed version

Thank you that is a big help. Do you have any code to verifing the backup? I have tried to integrate the code to verify from the other script but I do not know the lnaguage very well. Any help would be greatly appreciated.

-Andrew
7/23/2004 10:41 AM | Andrew
Gravatar

# re: Backup databases stored procedure - SQL LiteSpeed version

The only true way to verify a backup is to restore it. Backups should be tested at least once a week. You should have a test server setup for this. Copy the backup files over to it and restore them. If it restores fine, then the backup is good.
7/23/2004 10:44 AM | Tara
Gravatar

# re: Backup databases stored procedure - SQL LiteSpeed version

Thank you for the advise but because of the scale of our SQL severs that is not an option. Thank you greatly for your help.

-Andrew
7/23/2004 10:48 AM | Andrew
Gravatar

# re: Backup databases stored procedure - SQL LiteSpeed version

xp_msver ProcessorCount is the best way to get the proc count. Reading the registry the way you do can give bogus information.

Wes
8/3/2004 10:50 AM | Wes
Gravatar

# re: Backup databases stored procedure - SQL LiteSpeed version

Here is an example:

declare @numproc tinyint

create table #numproc
(
[Index] int,
Name varchar(255),
Internal_Value varchar(255),
Character_value varchar(255)
)

insert into #numproc
exec xp_msver ProcessorCount

set @numproc = (select cast(Internal_Value as int) from #numproc)
if @numproc > 1
begin
set @numproc = @numproc - 1
end
print @numproc

drop table #numproc

it doesn't take into account of hyperthreaded cpu's I'm not sure how you can find that out yet. I'm working on it.

Wes
8/3/2004 11:11 AM | Wes
Gravatar

# re: Backup databases stored procedure - SQL LiteSpeed version

please explain what stored procedure backs up all databases at once.

Thanks
9/29/2004 3:23 PM | alex
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET