Include additional information with your database backups using LiteSpeed's Attached Files/Folder feature

by Trent Mera

 

While your database backups are the most important part of your recovery strategy, there are additional items that you can keep alongside them that can make recovery and other tasks faster and more convenient. LiteSpeed can play a helpful role in this by enabling you to attach these items to your database backup so that they're always where you need them, compressed for space savings, and if desired, encrypted for security.

.First, some examples of additional information that you might want to attach to your backups:

  • Version and other information about the database's server environment
  • Login information
  • Schema scripts (to see what older versions of object DDL looked like and quickly revert)
  • Jobs and Schedule information

First, How to pull the information

While there are a number of easy ways to grab metadata from SQL Server and save it off as a file, the easiest is probably to just add a step to your backup job to BCP out the data you need. The job should be an Operating system (CmdExec) type, and typical syntax looks like this:

  • bcp "exec sp_server_info" queryout C:\output.csv -Sserver -T -c -t, -r\r\n

If you would like to see this this BCP command in action before putting it into your job, just run it as a t-sql statement by wrapping it in single quotes and running it with xp_cmdshell from your query editor:

  • EXEC master..xp_cmdshell 'bcp "exec sp_server_info" queryout C:\output.csv -Sserver -T -c -t, -r\r\n'

In this example, we're using "exec sp_server_info" to get version and other information about our SQL Server, but this could be replaced with other queries depending on what we need. Please see http://db-management.com/blog/2010/12/29/enhancing-your-sql-server-backups-with-additional-information/ to see queries you can use in the BCP statement to get different types of metadata, including those mentioned above.

.

Then, where LiteSpeed comes in

Once you've used SQL Server's BCP command to save metadata out to a file, LiteSpeed can grab the file (or an entire folder), compress it, and embed it right inside the backup file. Should you need to restore the backup or the attached files, the LiteSpeed's Restore Wizard simply asks you where you would like the file(s) extracted out to, and the files are placed there. This can also be done programatically through T-SQL calls.

.

To have LiteSpeed attached the desired file(s) to your backup, you simply navigate to the files in the Attached Files/Folder step of LiteSpeed's Backup Wizard. If you would like to schedule the backup to run as a job, simply use the LiteSpeed Backup Wizard to create the job, and then edit it in LiteSpeed's Job Manager to add a job step to run the BCP command above as a prior step. This way, the correct file(s) will be in place when the LiteSpeed backup step runs. The @attachedfile parameter of the backup script indicates the location of the file(s) to be included in the backup file:

.

exec master.dbo.xp_backup_database

@database = N'AdventureWorks',

@backupname = N'AdventureWorks - Full Database Backup',

@desc = N'Full Backup of AdventureWorks on 12/3/2010 10:22:12 PM',

@compressionlevel = 1,

@filename = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\AdventureWorks.bkp',

@init = 1,

@with = N'SKIP',

@with = N'STATS = 10',

@attachedfile = N'C:\yourfolder'

GO

.

This process can also be done outside of the UI by simply typing the syntax above for a LiteSpeed backup via T-SQL, or using a command line call like this:

.

sqllitespeed.exe -Bdatabase

-D"AdventureWorks"-C1 -I

-n"AdventureWorks - Full Database Backup"

-d"Full Backup of AdventureWorks on 12/3/2010 10:22:12 PM"

-F"C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\AdventureWorks.bkp"

--attachedfile "C:\yourfolder"

-WSKIP

-S"REMC2BT1D1" -T

.

About the Author