Wednesday, October 1, 2008

[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
exec xp_sqllitespeed_version

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"'

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: 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

1 comment:

javen said...

Have a look at HyperBac, developed by the original developers of LiteSpeed.

You can do object level recovery using TSQL SELECT statements from Linked Servers pointing to backup files.

Also has a service based architecture allowing you to do compression in 2000/2005 using native BACKUP DATABASE with no command substitution or extended stored procs.