[Tools] Some handy commands for Litespeed

Some notes of mine on using Quest Software's Litespeed Backup. Really handy software, but with some quirks.

To backup without having it compressed, when using NCS (Native Command Substitution)

/* ads_translator_deactivate */
BACKUP DATABASE dynamics TO DISK ='e:\dynamics_native.bak'

(the code in the comments is actually read by the parser and turns off the NCS)

To see if Litespeed is installed on a machine

use master
go
exec xp_sqllitespeed_version
go

Basic Restore(needs to be on one line - split here for readability)

exec master.dbo.xp_restore_database
@database = 'datbase',
@filename = '\\path\datbase.bak',
@filenumber = 1,
@with = 'RECOVERY',
@with = 'NOUNLOAD',
@with = 'STATS = 10'

To restore a database with a new name

--First, get a list of all logical files within the backup--
xp_restore_filelistonly @filename= '\\path\datbase.BAK'

--Now do a restore with MOVE.  @database is the new db name
exec master..xp_restore_database @database='datbase2'
  , @filename= '\\path\datbase.BAK'
  , @with = 'MOVE "datbase_Data" TO "e:\SQL\datbase2.MDF"'
  , @with = 'MOVE "datbase_Log" TO "e:\SQL\datbase2.LDF"' 

Extractor
The extractor.exe application will take a compressed backup file and decompress a standard MS SQL backup file. Useful if you don't have Litespeed on your other server, or if you need to send a file to someone who doesn't have it.

To call it:

extractor.exe -Fc:\temp\Northwind.bak -Ec:\temp\NorthwindNative.bak

where -F is the original compressed file and -E is the name for the tape files that will be generated (typically 7 files per backup). These can be restored via SSMS by adding each of the .bak[0-7] files to a restore.

Object-level Restores
OR.exe is the application used to do object-level restores from Litespeed. It uses BCP to pull the data out of one table and to place it in the next. It can only do it on a FULL backup - not a Filegroup, Diff, or TLOG. Irritating, that - maybe they've fixed that in Version 5.

Sample command to restore the "route" table from a backup on ServerA to ServerB. This needs to be on one line, I've split it up to illustrate the parameters.

"C:\Program Files (x86)\Imceda\LiteSpeed\SQL Server\Engine\or.exe"
 -F\\path\datbase.BAK
 -Odbo.route
-R1
-EServerB
-Sexisting_database
-Tdbo.TBD_test_restore

• -F: database backup filename
  
• -O: table name - must include schema
  
• -R: connection type - 1 is trusted
  
• -E: target server
  
• -S: target database
  
• -T: target table name - must include schema.

It can be done within SQL Server as well.

exec master..xp_objectrecovery
    @status_filename='{178FA185-ABC7-4183-910A-4DDE775BB614}',
    @FileName='E:\datbase.BAK',
    @ObjectName='dbo.tbd_test_restore',
    @DestinationServer='qa_database',
    @DestinationDatabase='new_copy_of_datbase',
    @DestinationTable='TBD_newtable_temp',
    @TempDirectory='E:\temp\'