Bulk-Logged Recovery Model

Question

Post reply

Bulk-Logged Recovery Model

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

David Naples SSC-Addicted Points: 415 More actions · Answer 1

Anchelin:

Careful what ya ask for, pal. It's more than one script. 🙂

All objects described below are located in the [master] database on each server. The tables are populated and the procedures are written from a centralized location. This was a consciuos decision to do this, instead of reading the data from a central server. This way, if there's a network outage, the backup process can still execute as everyting is local.

TABLES

/* This table contains one row only. */

CREATE TABLE [dbo].[AutoMaint_Server](

[BackupRoot] [varchar](1024) NULL, -- The root folder under which all backup files are stored.

[LocalDays] [smallint] NULL, -- The number of days backups are retained on the local server. This value is actually one less than the actual number of days stored; this is done to ensure that, in the case of databases with transaction log backups, this number of complete days are stored locally.

[ShareDays] [smallint] NULL -- The number of days backups are retained on the centralized file share(s). Same behavior as [LocalDays].

) ON [PRIMARY]

GO

/* This table contains settings for each database one the server. */

CREATE TABLE [dbo].[AutoMaint_Database](

[dbid] [int] NOT NULL, -- corresponds to the [dbid] in the [dbo].[sysdatabases] system view, or [database_id] in the [sys].[databases] system view.

[name] [varchar](128) NOT NULL, -- Name of the database extracted from the system view.

[LocalDays] [smallint] NULL, -- Same behavior as [LocalDays] in the server table. If this is left as NULL, the value from the server table is used.

[ShareDays] [smallint] NULL, -- Same behavior as [ShareDays] in the server table. If this is left as NULL, the value from the server table is used.

[IsBackedUp] [tinyint] NOT NULL CONSTRAINT [DF_AutoMaint_Database_IsBackedUp] DEFAULT ((1)), -- Indicates whether or not this database is to be part of the backup process.

CONSTRAINT [PK_AutoMaint_Database] PRIMARY KEY CLUSTERED

(

[dbid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/* Contains a list of file share locations to which backup files are copied. */

CREATE TABLE [dbo].[AutoMaint_Share](

[ID] [int] IDENTITY(1,1) NOT NULL,

[BackupShare] [varchar](1024) NULL, -- The file share location

[IsHome] [tinyint] NOT NULL CONSTRAINT [DF_AutoMaint_Share_IsHome] DEFAULT ((0)), -- indicates whether or not this file share is located on the same local network as the server being backed up. This is intended for use in an automated restore process (not yet built).

PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/* This procedure looks at the specified folder, and if there are no files present removes the folder. */

CREATE PROCEDURE [dbo].[usp_sys_DeleteEmptyFolders]

@Dir VARCHAR (1024)

AS

SET NOCOUNT ON

DECLARE @FileCount INT

DECLARE @nSQL NVARCHAR (4000)

DECLARE @Folder VARCHAR (1024)

DECLARE @FolderFetch INT

DECLARE @FileName VARCHAR (1024)

-- Create temp tables.

CREATE TABLE #Folders (Folder VARCHAR (260))

CREATE TABLE #Dir (Item VARCHAR (8000))

-- Retrieve a list of any subfolders that may reside in the specified folder.

INSERT #Folders

EXEC xp_subdirs @Dir

-- Scroll through that list. If none were found we simply exit the cursor anyway.

DECLARE curFolders CURSOR DYNAMIC LOCAL FOR

SELECT Folder

FROM #Folders

ORDER BY Folder

OPEN curFolders

FETCH FIRST FROM curFolders INTO @Folder

SET @FolderFetch = @@FETCH_STATUS

WHILE @FolderFetch = 0

BEGIN

-- Build the parameter to pass to the recursively called procedure.

SET @Folder = @Dir + '\' + @Folder

-- Call the procedure.

EXEC usp_sys_DeleteEmptyFolders @Folder

FETCH NEXT FROM curFolders INTO @Folder

SET @FolderFetch = @@FETCH_STATUS

END

CLOSE curFolders

DEALLOCATE curFolders

-- Build the command line to be passed to [xp_cmdshell].

SET @nSQL = 'dir "' + @Dir + '"'

-- Truncate the temp table (just to be safe).

TRUNCATE TABLE #Dir

-- Call the [xp_cmdshell] extended proc, inserting the results into the temp table.

INSERT #Dir

EXEC xp_cmdshell @nSQL

-- Extract a count from the [xp_cmdshell] results that are files, not subfolders.

-- NOTE: all subfolders have been removed, but the results will contain two folder entries

-- for "." and ".."; these need to be filtered out.

SELECT @FileCount = COUNT(*)

FROM #Dir

WHERE CHARINDEX (' ', Item) = 0

AND ISDATE (LEFT (Item, 10)) = 1

-- If this count = 0, then we can remove the folder.

IF @FileCount = 0

BEGIN

SET @nSQL = 'rd "' + @Dir + '"'

EXEC xp_cmdshell @nSQL, no_output

END

-- Drop the temporary tables.

DROP TABLE #Folders

DROP TABLE #Dir

SET NOCOUNT OFF

GO

/* This procedure removes any files older than the specified date from the specified folder. */

CREATE PROCEDURE [dbo].[usp_sys_DelOldBackups]

@Dir VARCHAR (1024),

@DeleteTime DATETIME

AS

SET NOCOUNT ON

DECLARE @Item VARCHAR (8000)

DECLARE @Path VARCHAR (1024)

DECLARE @FileDate DATETIME

DECLARE @SQL NVARCHAR (4000)

-- Create a temporary table to hold results from [xp_cmdshell] "dir" calls.

CREATE TABLE #Dir (Item VARCHAR (8000))

-- Build the command line for the [xp_cmdshell] call. We use the "/s" option to recusrsively list the contents of any subfolders.

SET @SQL = 'dir "' + @Dir + '" /s'

INSERT #Dir

EXEC xp_cmdshell @SQL

-- Open a cursor to retrieve the results from the temp table.

DECLARE curDelete CURSOR LOCAL STATIC FOR

SELECT Item

FROM #Dir

OPEN curDelete

FETCH FIRST FROM curDelete INTO @Item

WHILE @@FETCH_STATUS = 0

BEGIN

IF LEFT (LTRIM (@Item), 12) = 'Directory of'

BEGIN

-- Parse the entry to retrieve the parent path.

SET @Path = RTRIM (SUBSTRING (@Item, 15, 1024)) + '\'

GOTO NextEntry

END

IF ISDATE (LEFT (@Item, 10)) = 1

BEGIN

-- Retrieve the date of the file.

SET @FileDate = CONVERT (DATETIME, LEFT (@Item, 10))

-- Check to see if the file date is older than the specified retention date.

IF DATEDIFF (dd, @FileDate, @DeleteTime) > 0

BEGIN

-- Check to see if this is a backup file or a compressed archive.

IF RIGHT (RTRIM (@Item), 3) IN ('bak', 'trn', 'bz2')

BEGIN

-- Build the command line for the [xp_cmdshell] call.

SET @SQL = 'EXEC xp_cmdshell ''del "' + @Path + RTRIM (SUBSTRING (@Item, 40, 1024)) + '"'', no_output'

EXEC (@SQL)

END

CLOSE curDelete

DEALLOCATE curDelete

-- Drop the temp table.

DROP TABLE #Dir

Finish:

SET NOCOUNT OFF

GO

/* This procedure performs all the actual work of the backup/archiving process. */

/* NOTE: The bZip2 compression utility is required, and it needs to be in a */

/* folder referenced in the PATH system variable, in the current implementation */

/* the utility is located in the Windows folder. */

CREATE PROCEDURE [dbo].[usp_sys_AutoMaint_Backup]

@IsFullBackup TINYINT -- Code to indicate the type of backup taken: 1 = full backup; 2 = differential; 3 = transaction log.

AS

SET NOCOUNT ON

DECLARE @LocalTime DATETIME

DECLARE @FileType NVARCHAR (4)

DECLARE @BackupExtension NVARCHAR (3)

DECLARE @BackupType NVARCHAR (8)

DECLARE @Database_ID INT

DECLARE @DBFetch INT

DECLARE @DBName VARCHAR (128)

DECLARE @SQL NVARCHAR (4000)

DECLARE @Pos INT

DECLARE @LocalDays SMALLINT

DECLARE @FileName NVARCHAR (1024)

DECLARE @ShareDays SMALLINT

DECLARE @Share VARCHAR (1024)

DECLARE @ShareFetch INT

DECLARE @RootDeleteTime DATETIME

DECLARE @ShareDeleteTime DATETIME

DECLARE @IsHomeShare TINYINT

DECLARE @Root VARCHAR (1024)

DECLARE @ServerLocal INT

DECLARE @ServerShare INT

/* Retrieve the local time. This is used to build the backup filename. */

SELECT @LocalTime = GETDATE()

-- Retrieve the server-level default values.

SELECT TOP 1

@Root = BackupRoot,

@ServerLocal = LocalDays,

@ServerShare = ShareDays

FROM AutoMaint_Server

-- Set the various components used to build the SQL statement.

IF @IsFullBackup = 1

BEGIN

SET @BackupType = 'DATABASE'

SET @FileType = 'full'

SET @BackupExtension = 'bak'

END

IF @IsFullBackup = 2

BEGIN

SET @BackupType = 'DATABASE'

SET @FileType = 'diff'

SET @BackupExtension = 'bak'

END

IF @IsFullBackup = 3

BEGIN

SET @BackupType = 'LOG'

SET @FileType = 'log'

SET @BackupExtension = 'trn'

END

-- Retrieve the list of databases. We join to the system view to retrieve the recovery model;

-- this is done to allow for changes in the recovery model without manual intervention. We

-- also filter out [database_id] = 2 (tempdb). NOTE: This is a SQL 2005 implementation; SQL 2000

-- requires a different system table and parsing of the [status] bitfield.

DECLARE curDB CURSOR STATIC SCROLL GLOBAL FOR

SELECT d.database_id, d.name, ISNULL (a.LocalDays, @ServerLocal), ISNULL (a.ShareDays, @ServerShare)

FROM master.sys.databases d

INNER JOIN AutoMaint_Database a ON d.database_id = a.dbid

AND d.name = a.name

WHERE d.is_read_only = 0

AND d.state = 0

AND a.IsBackedUp = 1

AND d.database_id NOT IN (2)

AND d.recovery_model = CASE WHEN @IsFullBackup IN (1, 2) THEN d.recovery_model ELSE 1 END

--and d.name = 'TfsBuild'

--and d.name = 'ABT089'

--and d.name like 'Asacol%'

--and d.name = 'BIFS_CallLog'

ORDER BY name

OPEN curDB

FETCH FIRST FROM curDB INTO @Database_ID, @DBName, @LocalDays, @ShareDays

SET @DBFetch = @@FETCH_STATUS

WHILE @DBFetch = 0

BEGIN

-- Create the local directory

SET @SQL = 'md "' + @Root + @DBName + '"'

EXEC xp_cmdshell @SQL, no_output

-- Build the filename of the backup, i.e. AdventureWorks_full_200803171500.bak.

SET @FileName = @DBName + '_' + @FileType + '_' + CONVERT (VARCHAR, @LocalTime, 112) + LEFT (REPLACE (CONVERT (VARCHAR, @LocalTime, 108), ':', ''), 4)

-- Build the T-SQL used to back up the database.

SET @SQL = 'BACKUP ' + @BackupType + ' [' + @DBName + '] TO DISK = N''' + @Root + @DBName + '\' + @FileName + '.' + @BackupExtension + ''' WITH

' + CASE WHEN @IsFullBackup = 2 THEN 'DIFFERENTIAL,' ELSE '' END + '

RETAINDAYS = ' + CONVERT (VARCHAR, @LocalDays) + ',

NOFORMAT,

NOINIT,

NAME = N''' + @FileName + ''',

SKIP,

REWIND,

NOUNLOAD,

STATS = 10'

-- Back up the database

EXEC @Pos = sp_executesql @SQL

-- If there was an error, we skip the archiving and go to the next database.

IF @Pos <> 0

BEGIN

PRINT 'Error backing up database [' + @DBName + ']'

GOTO NextDB

END

NextDB:

FETCH NEXT FROM curDB INTO @Database_ID, @DBName, @LocalDays, @ShareDays

SET @DBFetch = @@FETCH_STATUS

END

-- The compression and copy routines are only run for full and differential backups.

Compress:

IF @IsFullBackup IN (1, 2)

BEGIN

-- Create the temp table to hold results from the [xp_cmdshell] calls.

CREATE TABLE #Dir (Item VARCHAR (8000))

-- Retrieve the list of share locations from the share table. The folder structure is

-- \\ . In the case of

-- the default MS SQL instance, we use "_DefaultMSSQL".

DECLARE curShare CURSOR STATIC SCROLL GLOBAL FOR

-- NOTE: Replace values in the angle brackets with your own values.

SELECT DISTINCT BackupShare + ' \'

FROM AutoMaint_Share

OPEN curShare

FETCH FIRST FROM curShare INTO @Share

SET @ShareFetch = @@FETCH_STATUS

WHILE @ShareFetch = 0

BEGIN

-- Truncate the temp table.

TRUNCATE TABLE #Dir

-- Print a status message. This is used for debugging, and in the case of directing SQL Agent job output to a file for logging purposes.

PRINT 'Creating ' + @Share + ' ...'

-- Build the command line to create a folder.

SET @SQL = 'md "' + @Share + '"'

-- Insert the output from the folder creation into the temp table.

INSERT #Dir

EXEC @Pos = xp_cmdshell @SQL

-- If there was an error ...

IF @Pos = 1

BEGIN

-- ... retrieve the first entry.

SELECT TOP 1 @SQL = Item

FROM #Dir

-- If the folder already exists, reset the error code and move to the next share.

IF @SQL = 'A subdirectory or file ' + @Share + ' already exists.'

BEGIN

SET @Pos = 0

END

ELSE

BEGIN

-- This code is in place to handle errors caused by processes that use cross-domain addressing.

-- In some cases, a share located in the same domain will generate an error if the "create folder"

-- statement is used with a fully qualified server name (i.e. .com). This routine

-- was built to strip the .com out of the share name and attempt to build the folder that way.

-- This will rarely be called, but is necessary.

-- Parse the share name.

SET @Share = LEFT (@Share, CHARINDEX ('.', @Share) - 1) + SUBSTRING (@Share, CHARINDEX ('\', @Share, 3), 1024)

-- Print a status message (same as above).

PRINT 'Creating ' + @Share + ' ...'

-- Build the command line.

SET @SQL = 'md "' + @Share + '"'

-- Execute the command and retrieve the return code.

EXEC @Pos = xp_cmdshell @SQL, no_output

END

-- If we still get an error, it's probably a result of either permissions or quotas and requires

-- intervention from a system admin.

IF @Pos = 1

BEGIN

PRINT 'Error creating share folder on ' + @Share

GOTO CloseCursor

END

FETCH NEXT FROM curShare INTO @Share

SET @ShareFetch = @@FETCH_STATUS

END

-- Go back to the beginning of the database cursor.

FETCH FIRST FROM curDB INTO @Database_ID, @DBName, @LocalDays, @ShareDays

SET @DBFetch = @@FETCH_STATUS

WHILE @DBFetch = 0

BEGIN

-- Set the retention cutoff dates for the local and remote file locations.

SET @RootDeleteTime = DATEADD (dd, @LocalDays * (-1), CONVERT (DATETIME, CONVERT (VARCHAR, @LocalTime, 101)))

SET @ShareDeleteTime = DATEADD (dd, @ShareDays * (-1), CONVERT (DATETIME, CONVERT (VARCHAR, @LocalTime, 101)))

-- If the @ShareDays value evaluates to -1 then backups are not copied to the remote locations at all.

IF @ShareDays > -1

BEGIN

-- Build the backup filename.

SET @FileName = @DBName + '_' + @FileType + '_' + CONVERT (VARCHAR, @LocalTime, 112) + LEFT (REPLACE (CONVERT (VARCHAR, @LocalTime, 108), ':', ''), 4)

-- Compress backup

PRINT 'Compressing ' + @Root + @DBName + '\' + @FileName + '.' + @BackupExtension + ' ...'

SET @SQL = 'bzip2 -k -9 "' + @Root + @DBName + '\' + @FileName + '.' + @BackupExtension + '"'

EXEC xp_cmdshell @SQL, no_output

-- Go back to the beginning of the share cursor.

FETCH FIRST FROM curShare INTO @Share

SET @ShareFetch = @@FETCH_STATUS

WHILE @ShareFetch = 0

BEGIN

-- Create the directory on the fileshare

PRINT 'Creating ' + @Share + @DBName + ' ...'

SET @SQL = 'md "' + @Share + @DBName + '"'

EXEC xp_cmdshell @SQL, no_output

-- Copy compressed backup

PRINT 'Copying ' + @Root + @DBName + '\' + @FileName + '.' + @BackupExtension + '.bz2 to ' + @Share + @DBName + '\' + @FileName + '.' + @BackupExtension + '.bz2 ...'

SET @SQL = 'COPY /Y "' + @Root + @DBName + '\' + @FileName + '.' + @BackupExtension + '.bz2" "' + @Share + @DBName + '\' + @FileName + '.' + @BackupExtension + '.bz2"'

EXEC xp_cmdshell @SQL, no_output

FETCH NEXT FROM curShare INTO @Share

SET @ShareFetch = @@FETCH_STATUS

END

-- Delete compressed backup

PRINT 'Deleting ' + @Root + @DBName + '\' + @FileName + '.' + @BackupExtension + '.bz2 ...'

SET @SQL = 'del "' + @Root + @DBName + '\' + @FileName + '.' + @BackupExtension + '.bz2"'

EXEC xp_cmdshell @SQL, no_output

END

DeleteOldFiles:

-- Removing old files is only done when the backup type is FULL. Otherwise, this section is skipped.

IF @IsFullBackup = 1

BEGIN

-- Build the folder from which backups are to be removed.

SET @SQL = @Root + @DBName

-- Call the procedure.

EXEC usp_sys_DelOldBackups @SQL, @RootDeleteTime

-- Go back to the beginning of the share cursor so that we can remove files from the remote locations.

FETCH FIRST FROM curShare INTO @Share

SET @ShareFetch = @@FETCH_STATUS

WHILE @ShareFetch = 0

BEGIN

SET @SQL = @Share + @DBName

EXEC usp_sys_DelOldBackups @SQL, @ShareDeleteTime

FETCH NEXT FROM curShare INTO @Share

SET @ShareFetch = @@FETCH_STATUS

END

FETCH NEXT FROM curDB INTO @Database_ID, @DBName, @LocalDays, @ShareDays

SET @DBFetch = @@FETCH_STATUS

END

DROP TABLE #Dir

END

CLOSE curDB

DEALLOCATE curDB

DeleteEmptyFolders:

-- Removing empty folders is only done when the backup type is FULL. Otherwise, this section is skipped.

IF @IsFullBackup = 1

BEGIN

-- Go back to the beginning of the cursor share.

FETCH FIRST FROM curShare INTO @Share

SET @ShareFetch = @@FETCH_STATUS

WHILE @ShareFetch = 0

BEGIN

-- Remove all empty folders.

EXEC usp_sys_DeleteEmptyFolders @Share

FETCH NEXT FROM curShare INTO @Share

SET @ShareFetch = @@FETCH_STATUS

END

-- Remove any empty folders from the local root folder.

EXEC usp_sys_DeleteEmptyFolders @Root

-- Close the cursors.

CloseCursor:

CLOSE curShare

DEALLOCATE curShare

END

Finish:

SET NOCOUNT OFF

GO

/*******************************************************************/

Have fun with THIS. 🙂

Cogiko ergo sum (I geek, therefore I am).

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Ignacio A. Salom Rangel (3/27/2008)

I think that you should Really consider using third party tools.

Don't leave us hanging... spit it out.. WHICH 3rd party tools would you recommend?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

David Naples (3/27/2008)

Have fun with THIS.

Pleasant surprise... there's even some documentation in the code... thanks David.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 4

Ignacio A. Salom Rangel

SSC-Insane

Points: 20443

March 28, 2008 at 8:00 pm

#795546

I have used LiteSpeed, but i'm sure there are other third party applications that gives the same result as LiteSpeed. It will reduce the size of the backups and the backup/restore times. You can download a free trial to check it out.

My blog

David Naples SSC-Addicted Points: 415 More actions · Answer 5

The thing with LiteSpeed is, if you use top-level LiteSpeed compression and then try to compress the files with something like bZip2 or WinRAR, your compressed files are actually larger than if you compressed a native SQL backup. I ran extensive tests on this with several different databases, so I know it wasn't an anomaly resulting from the type of data being stored in one database.

If you;re not going to compress the backup files after the fact then LiteSpeed is definitely the way to go. However, if you're looking solely to save disk space and network traffic, I would recommend a native SQL backup followed by a compression utility.

My two cents. 🙂

Cogiko ergo sum (I geek, therefore I am).

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 6

Ignacio A. Salom Rangel

SSC-Insane

Points: 20443

March 28, 2008 at 8:29 pm

#795551

Good point, but what about restore time?

My blog

David Naples SSC-Addicted Points: 415 More actions · Answer 7

You have to be more specific about what you mean by "restore time." If you are talking only about the amount of time to restore a backup into SQL Server, then that's one thing. However, I always take the amount of time to retrive the backup into account. This includes copying it from a remote location (if necessary ... see my earlier post about DR strategies), decompressing it, and restoring the database.

I've found that the best balance of speed and compression ratios comes with WinRAR using its medium compression algorithm. The enables the backup to compress (and consequently decompress) relatively quickly, while also providing better than average file compression.

As far as the restore time difference between LiteSpeed and native SQL? I haven't actually benchmarked it, but I suspect that, since the resulting database is the same size no matter what you, the native SQL may give slightly better performance since the backup does not have to be decompressed while the restore is happening.

Cogiko ergo sum (I geek, therefore I am).

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

Ignacio A. Salom Rangel (3/28/2008)

I have used LiteSpeed, but i'm sure there are other third party applications that gives the same result as LiteSpeed. It will reduce the size of the backups and the backup/restore times. You can download a free trial to check it out.

Thanks, Ignacio.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 9

David Naples (3/28/2008)

As far as the restore time difference between LiteSpeed and native SQL? I haven't actually benchmarked it, but I suspect that, since the resulting database is the same size no matter what you, the native SQL may give slightly better performance since the backup does not have to be decompressed while the restore is happening.

I did test LiteSpeed restore and the restore times where better than native SQL. The database that I restore was 120GB and the restore time with native SQL backups was 75 minutes and with LiteSpeed it was 50 minutes. The backup files with native SQL backups where 100GB, with LiteSpeed they where 20GB with the defaults settings (thus lower compression). I'm sure that there are tools that can do the same as LiteSpeed, so for your information I'm not currently working for Quest software :P. You could try other tools, I heard from a friend that red gate has also a good backup product for SQL. One of the factors that made us use LiteSpeed is that Microsoft uses too.

I hope I answer your question. 🙂

My blog