Programs

SQL_STORED_PROCEDURE - prc_backup_systemdb

Main program

Name	prc_backup_systemdb
Type_desc	SQL_STORED_PROCEDURE
Comment	SQLServer Stored Procedure PURPOSE Make full backup of all system databases. Only system databases are backupped. Only database with status ONLINE are backupped. Database tempdb cannot be backupped. Backup is made to a subdir (or not) of the given @backup_path (with name {dbname}), depending of parameter @subdir (default is backup to subdir). Backup is followed by a restore with verifyonly depending on parameter @verify (default is backup without verify). A log backup of the model database is made (after a full backup) if the model is in FULL or BULK_LOGGED recovery model. This is to prevent growing the logfile. PARAMETERS - 1 @backup_path VARCHAR(265) Backup_path - 2 @subdir BIT 0 / 1 (default) Optional: Backup to subdir 0/1. - 3 @verify BIT 0 (default) / 1 Optional: Backup with verify 0/1. EXAMPLES -- Full backups with standard parameters EXEC [dbo].[prc_backup_systemdb] @backup_path = 'C:\Data\MSSQL\backup'; -- Full backups with verify EXEC [dbo].[prc_backup_systemdb] @backup_path = 'C:\Data\MSSQL\backup', @verify=1; USED OBJECTS - dbo.prc_backup_database Stored procedure (in local database) - dbo.prc_backup_log Stored procedure (in local database) - dbo.fun_FolderExist UDF (in local database) SQLSERVER CONFIGURATION - OLE Automation has to be enabled. HISTORY 2014-07-29 - Created procedure 2015-07-08 - Renamed cursor 2015-07-28 - Changed error messages 2016-01-19 - Changed variable @verify_backup to type BIT - Read variable @local_backup and skip backups if 0 2018-01-16 - Variable @local_backup is obsolete now 2019-10-29 - Added logbackup of model database when in FULL of BULK_LOGGED recovery model 2020-07-08 - Replaced table LOCAL_BACKUP_CONFIG by CONFIGURATION 2020-11-03 - Replaced table CONFIGURATION into SETUP_CONFIG 2020-11-04 - Check on parameter backup_base in stead of local_backup to continue 2021-12-14 - SETUP_CONFIG parameter verify_backup is renamed to backup_with_verify 2023-05-12 - Removed @options in commandline prc_backup_db 2023-12-07 - Made suitable for DBMAINT 2023-12-09 - Changed backup path 2024-02-11 - Changed table SETUP to Setup - Use dbo.fun_setup2value to read Setup values 2024-04-19 - Extend backup_path with servername 2024-04-23 - Don't extend backup_path anymore 2024-07-23 - Adjusted Setup field lengths and types 2025-05-18 - Renamed table Setup to Ref_setup 2026-04-02 - Made procedure full configurable with parameters 2026-04-03 - Changed pararameters for call to prc_backup_database 2026-05-12 - Renamed procedure from prc_backup_all_systemdb to prc_backup_systemdb TAGS <program> <description>Make full backup of all system databases</description> <generic>1</generic> <author>Gerrit Mantel</author> <created>2014-07-29</created> <lastmodified>2026-05-12</lastmodified> </program>
Description	Make full backup of all system databases
Minversion
Generic	1
Author	Gerrit Mantel
Created	2014-07-29 00:00:00
Lastmodified	2026-05-12 00:00:00
Xp_cmdshell	0
Ole_automation	0
Subprogram_cnt	4
Sourcecode

Sub programs