The Ultimate MS SQL Backup Script

Average: Select ratingPoorOkayGoodGreatAwesome

Your rating: None Average: 5 (1 vote)

I am finally getting around to publishing my ultimate Microsoft SQL Server Backup scripts. All told, it is about 1600 lines of code and it can do just about anything you would need for Native or Quest Litespeed backups.

http://www.sqlwebpedia.com/files/Scripts/DBOPSBackupScripts.zip

Here are some examples so you get idea of its power.

Declare @RC int, @StatusMsg varchar(1024)

--Example1: Try 2 times (per database) to do a FULL backup on ALL databases, with two destination files, to a local folder, don't verify the backups, exclude a bunch of databases I don't care about, and create servername in destination path to better organize my backups.

Exec @RC = prc_DBA_AdvancedBackup @RetryAttempts=2, @BackupType='C', @DBName='*', @Debug = 0, @NumberOfBackupFiles = 2, @BackupDir='e:\backups', @VerifyBackup = 0, @ExcludedDBs = 'Model,litespeedlocal,questworkdatabase,reportservertempdb', @CreateSrvDir = 1, @StatusMsg=@StatusMsg OUTPUT

--Example2: Try 2 times to do a FULL backup on DBOPS database, to a network share with two destination files, purge MSDB history and keep last 60 days of history, don't verify the backups, Retain 7 days of backups, and create servername in destination path to better organize my backups.

Exec @RC = prc_DBA_AdvancedBackup @RetryAttempts=2, @BackupType='C', @DBName='DBOPS', @MSDBPurgeHistory = 1, @MSDBRetainDays=60,@Debug = 0, @NumberOfBackupFiles = 2, @InitBackupDevice=0, @BackupDir='\\SERVERNAME\DBBackup', @VerifyBackup = 0, @RetainDays = 7, @CreateSrvDir = 1, @StatusMsg=@StatusMsg OUTPUT

--Example3: Try 2 times to do a FULL backup on databases that start with letters between A-M, but exclude the Model db, to a network share, don't overwrite the files - append to existing, verify the backups, Retain 7 days of backups, and create servername\databasename in destination path to better organize my backups.

Exec @RC = prc_DBA_AdvancedBackup @RetryAttempts=2, @BackupType='C', @DBName='A-M', @ExcludedDBs = 'Model', @Debug = 1, @InitBackupDevice=0, @BackupDir='\\SERVERNAME\dbbackups\', @VerifyBackup = 1, @RetainDays = 7, @CreateSrvDir = 1, @CreateSubDir = 1, @StatusMsg=@StatusMsg OUTPUT

Select @RC, @StatusMsg

The main procedure is dbo.prc_DBA_AdvancedBackup with a BOAT LOAD of parameters! See below for list and description (copied from file).

@BackupType char(1) = 'C'

--'C' for Complete/Full Database Backup (Default)

--'D' for Differential Backups

--'G' for Individual File Group Backups

--'L' for Transaction Log Backups

,@DBName varchar(4000) = '*'

--'*' = Backup All Databases (Default)

--Range A-Z which will translate into database name start letter. First letter only. A-C will be >=A and <= C, so you can have multiple jobs A-C;D-G etc.

--Delimited list of databases to be included in backup operation.

,@ExcludedDBs varchar(1024) = NULL

--Delimited list of files to be excluded in backup operation

,@BackupDir varchar(300) = NULL

--Directory/Path to store backups (can use UNC paths as well).

,@MirrorBackupDir varchar(300) = NULL

--Backup location to duplicate the backup (no relation to database mirroring) - Enterprise feature only of course, what did you expect?

,@NumberOfBackupFiles tinyint = 1

--Number of files to backup to, @FileName backup option

,@MaxTransferSizeKB int = 1024

--Specifies the data size in bytes for each transfer when communicating with SQL Server. The size can be any multiple of 64KB in the range from 64KB to 4MB.

,@RetryAttempts tinyint = 1

--Number of attempts to run backup command.

,@VerifyBackup tinyint = 1

--1 = Perform verification of backups (Default)

--0 = Skip Verification

,@Backup_Readonly bit = 0

-- 0 = Don't backup readonly databases

,@BackupProduct tinyint = 0

--0 = Use SQL LiteSpeed Extended Stored Procedure Interface (Default)

--2 = Use SQL Native Backup to File

,@Debug bit = 0

--1 = Print verbose logging

--0 = Minimal logging (Default)

,@EncryptionKey varchar(1024) = NULL

--Encryption Key used to secure Backup Devices (Optional).

,@CompressionLevel tinyint = 3 --3 is vendor recommended value.

--Litespeed compression level to use.

--0 no compression

--1 compression using algorithm (a)

--2-10 compression using algorithm (b), on a progressive scale from least compression to most compression, with a corresponding CPU trade-off.

,@DoubleClickRestore int = 0

--Create Litespeed doubleclick restore.

,@SLSThreads tinyint = 2

--Number of worker threads to use to backup when backing up to one file.

,@SLSThrottle tinyint = 100

--Set LiteSpeed's CPU % throttle usage. Value should be between 1 and 100.

,@SLSAffinity tinyint = 0

--Set LiteSpeed's processor affinity. Default is 0.

--On a 4-processor box, processors are numbered 0, 1, 2, and 3.

--0 = All processors

--1 = Processor 0

--2 = Processor 1

--3 = Processor 0 and 1

--4 = Processor 2

--5 = Processor 2 and 0

--6 = Processor 2 and 1

--7 = Processor 2, 1 and 0

--8 = Processor 3

--See SQL SiteSpeed documentation for more information on the @affinity variable.

,@SLSPriority tinyint = 0

--Base priority of SQL LiteSpeed Backup process.

--2 = High

--1 = Above Normal

--0 = Normal (Default)

,@SLSLogging tinyint = 0 --C:\Documents and Settings\All Users\Application Data\Quest\LiteSpeed

--0 - No logging.

--1 - Automatically generates a "crash dump" file in the LiteSpeed Program Files\Logs directory in the event of a process failure or abnormal termination.

--This can be used by Product Technical Support for problem determination and analysis.

--2 - Generates a verbose log file for the operation regardless of the process outcome (success or failure). These logs must be manually deleted.

--3 - LiteSpeed creates verbose logs and only saves them if the back up fails. If it succeeds, LiteSpeed does not save the log.

,@SLSOptionalCommands varchar(400) = NULL

--Send in other commands not listed. Format not checked but should be like @ioflag='OVERLAPPED',@ioflag='SEQUENTIAL_SCAN'

,@RetainDays smallint = 7

--Number of days to retain backup device files, if supplied backup files older than the number of days specified

--will be purged.

,@MSDBPurgeHistory bit = 1

--Purge MSDB history if set to 1. Days kept is = @MSDBRetainDays

,@MSDBRetainDays smallint = 90

--Days of MSDB history to keep.

,@InitBackupDevice tinyint = 1

--1 = Reinitialize backup device (timestamped name and no append to file)

--0 = Append to existing file if it exists.

,@PerformDBCC bit = 0

--1 = Perform DBCC CHECKDB prior to backing up database

--0 = Do not Perform DBCC CHECKDB prior to backing up database (default)

,@CreateSubDir bit = 0

--Creates a subdirectory under the backup directory for each db being backed up

,@CreateSrvDir bit = 1

--Creates a directory under the backup directory for the current server, used for scenarios where multiple servers

--are backing up to the same location, ensures no namespace conflicts

,@LOGResults bit = 1

--Log each DB backup results using a call to prc_DBA_InsDatabaseBackupLog.

,@PreserveBackupSet bit = 0

--1 = Change filename by appending .save to file name to prevent @RetainDays from deleting.

--0 = Normal operation (Default)

,@StatusMsg varchar(1024) = NULL OUTPUT

 

That is all 31 of them. Hope you enjoy!

-Chuck Lathrope