Automations

Automatically Create All LiteSpeed SQL Backups And Place Into A Job

Here’s some really great backup logic if you are using LiteSpeed for your SQL backups. This does a number of things.

0. Configures SQL Server to use Backup Compression for all backups.
1. Automatically creates the folder structure on your Backup Share based on ServerName(or instance name), DatabaseName, etc. 2. Creates an automatic TimeStamp in the backup file name.
3. Adds the “.bkp” backup extension to the file to readily identify between standard ‘.bak’, and ‘.bkp’.

All you need to do is put this into an SQL Backup Job, and it automatically creates the rest of your backup folder structure under the backup share you specify. Ensure that the Agent Service Account has full rights to your backup share for this to work.

use master;

set quoted_identifier off
set nocount on

declare @dbserver varchar(255)
declare @servername varchar(255)
declare @fullbackup varchar(255)
declare @logbackup varchar(255)
declare @diffbackup varchar(255)
declare @backup_time_stamp varchar(255)
declare @backup_location varchar(255) declare @create_folder_servername varchar(max) declare @create_folder_backup_type varchar(max) declare @backup_database varchar(max)

set @fullbackup = 'Full'
set @logbackup = 'Tran'
set @diffbackup = 'Diff'
set @servername = ( select @@servername )
set @dbserver = ( select left(@@servername, charindex('\', @@servername) +50) )
set @backup_time_stamp = ( select replace(replace(replace(replace(replace(convert(varchar,getdate(), 9),' ',' '),':','-'),' ',' ' ), 'pm', ' pm'), 'am', ' am') ) set @backup_location = '\\MyBackupShare\MyBackupFolder\' set @backup_database = ''
exec master..sp_configure 'show advanced options' , '1' reconfigure
exec master..sp_configure 'xp_cmdshell' , '1' reconfigure
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s