Backup is one off the most important things that need to be arranged after a SQL Server database is created, restored or attached.
The advantages of the T-SQL scripts presented here over a SSIS task:
1. Newly created db's are automatically included, no additional steps are required;
2. Databases that are in recovery mode 'Full' or 'Bulk Recovery' are automatically selected when transaction log backups are sheduled;
3. The checksum option that was introduced with SQL 2005 is included;
4. After a backup is completed a verify is executed;
5. Expired backups are automatically deleted;
6. Each set of dumps are stored in their own folder: less clutter
7. Databases can be set off-line without the job-step to fail
8. Databases in standby mode are skipped
The actual backup is made in the 'usp_MakeBackup' stored proc that is not called directly,
but is executed from the parent sp's: 'usp_MakeFullBackup', 'usp_MakeDifferentialBackup' and 'usp_MakeLogBackup' all scheduled from SQL Agent.
I usally make full backup around 2300hr and schedule log backups every 2hrs or so between 0800 until 2000hr.
The differential backup sp was only used in an OLTP config with a high volume of log backups (every 25 minutes), To avoid a very long list if individual log files we scheduled a differential backup every 4hrs. So the restore sequence was: full backup, last differential backup after the full backup and one or more transaction log backup that were made after the differential.
Code to initiate a full backup (store in CodeCatalog)
Code to initiate a differential backup (store in CodeCatalog)
Code to initiate a transaction log backup (store in CodeCatalog)
The actual backup to disk is made here (to store in CodeCatalog). Looks bit odd, still fighting the code editors: