INSTALL:
1. Open the downloaded code (sp_BackupDB.sql) in Microsoft SQL Server Management Studio (SSMS)
2. Execute (compile) the code.
----- optional -----
Mark procedure as system object:
EXEC sys.sp_MS_marksystemobject sp_BackupDB;
Grant to public:
GRANT EXEC ON sp_BackupDB TO PUBLIC;
USAGE:
EXECUTE dbo.sp_BackupDB p1, p2, [p3, p4, p5, p6, p7, p8];
parameters:
p1: @showUsage VARCHAR(10) /REQUIRED/
If not NULL (formally '--help' or '/?') then display this HELP.
Note that '' /empty string/ or 0 are iterpreted as NULL.
If @showUsage is not null all other parameters will be ignored.
p2: @dbListInc VARCHAR(MAX) /REQUIRED/
DB list for backup, separate with commas. DB names can be enclosed
with square brackets (not required) and wildcard characters can be
used as it is in the syntax of SQL LIKE command. Note that stand alone
joker (%) cannot be used with other database(s).
p3: @dbListExc VARCHAR(MAX) /NOT REQUIRED, DEFAULT NULL/
DB list exclude from backup. Rules are the same as p2.
p4: @backupTypeID CHAR(1) /NOT REQUIRED, DEFAULT 'F'/
This parameter determines backup type, which can be 'F' (Full) /default/,
'D' (Differential), or 'L' (Log). Most important backup rules are:
- tempdb cannot be saved in any case
- LOG and DIFF backup cannot be executed on master
- TLOG backup cannot be executed if recovery model is SIMPLE
Due to the above rules, tempdb will be forced to add to the exclude list
and also the master database, if the backup mode is LOG or DIFF.
p5: @backupPath VARCHAR(200) /NOT REQUIRED, DEFAULT '\\{BACKUPSERVER}\{SERVERNAME}\{BACKUPTYPE})'/
The path of the backup device. Note that only local paths will be checked before execution.
(Wrong network paths will drop backup error.)
p6: @compression INT /NOT REQUIRED, DEFAULT 0 (NO_COMPRESSSION)/
This flag overrides the server-level COMPRESSION setting.
In case of @compression = 1, backup file will be compressed. Note that the
compression available only in SQL Server 2008 Enterprise and later versions.
p7: @copyOnly INT /NOT REQUIRED, DEFAULT 1 (COPY_ONLY)/
This flag specifies that the backup is a copy-only backup or not.
Note that COPY_ONLY backup (default) does not affect the normal sequence of
backups and it has no effect when specified with the 'D' (differential) backup type.
p8: @printOnly INT /NOT REQUIRED, DEFAULT 1 (PRINTONLY)/
This flag specifies that the backup commands will be executed or not. For
security reasons default value is 1. This can be useful for large DB lists
to see backup commands and control parameter values before execution.
Empty string and NULL is equivalent. NULL values are interpreted as defaults in each
cases. Note that the order in the parameter list is fixed and hierarchical,
so intermediate values cannot be missed, even if they have default values! (Eg. in case
if you want to use the @printOnly flag as 0 at the end of the parameter list, then you must
give at least NULL values for all of the imtermediate parameters.) Backup files and
extensions will be made automatically. Extension is *.bak except LOG backup, when it's *.trn.
Filename is preformed from DB name, backup type, date and time.
examples:
EXECUTE dbo.sp_BackupDB 0, 'T%', 'T[_]%', NULL, 'C:\SQLBackup', 1, 1, 1;
It will print out (not execute) the backup command with backup type FULL,
COMPRESSION and COPY_ONLY options for all databases, where DB name begins
with letter 'T', except databases where underscore character follow the first 'T'.
EXECUTE dbo.sp_BackupDB 0, 'master', NULL, 'L', NULL, 0, 0, 0;
It will backup the transaction log of the master database as a part of
the normal backup sequence. Backup device (path and name) will be the default.
EXECUTE dbo.sp_BackupDB NULL, '%';
It will print out (not execute) the backup command for all databases with default
parameter values.
