The problem
I came up with this solution while working at a software development shop a few years ago. The options in SQL Server Maintenance plans were to backup all databases, just the user databases or to specify which databases to back up. We had some huge databases on our servers that did not really change much and it was overkill to back them up all the time. But when we used the 'backup these databases' option, a developer would come along and add a new database without updating the maintenance plan. This meant the new and important database was never backup up. The solution:
I decided to step away from the maintenance plans for backup, or at least add a new method in addition to the maintenance plan.I added a table to the master database to hold a record for each database used on the server. In this table you can mark a database as 'not to be backed up'. But if a new database would not be in this table because the developer forgot to add it, it would still be backed up. In other words, this table is only really important for databases that should not be backed up.
I also wanted the ability to restore to a specific point in time, this is only possible if you do frequent transaction log backups.
I came up with the following schedule:
- Make a full backup of the database on Sunday nights
- Make a differential backup of the databases every night but Sundays
- Make a transaction log backup every hour.
I came up with three stored procedures:
dba_BackupSingleDatabase
which takes four parameters: @databaseName is the name of the database to be backed up.
@backupType is either 'full', 'diff' or 'log' to indicate the type of the backup. The default is 'full'.
@backDir the directory to save the backup files to. If this is left empty it uses the default backup directory of the SQL Server instance it is running in.
@filePrefix - to distinguish these backups from other files I add a prefix before the database name in the file name. By default this is 'dba_' You can run this procedure by itself, but usually it is used by the other two:
dba_BackupActiveDatabases
This gets all user databases that are not marked as 'not to be backed up' in the dba_Databases table. It then calls dba_BackupSingleDatabase to perform the actual backup. The parameters are the last three from the dba_BackupSingleDatabase procedure with the same default values. The fourth parameter is @namePrefix, when not empty it only processes databases that begin the that particular prefix. On most of our server different clients have different prefixes, so you can limit the backups to just one client.
dba_BackupAllDatabases
This is an optional procedure and backs up all user databases on the instance regardless of their status as well as the three system databases 'master','model' and 'msdb'. Is has the same parameters as dba_BackupActiveDatabases.
Setup:
You need to add the table and the three stored procedures to your master database. Download the setup script and run it on your server Next check your backup directory:
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory'you may want to change this to a different one:
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', REG_SZ, 'X:\sqldata\backups\'Please note that the registry key shown here is not where this actually lives, in my case the actual key was 'HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\BackupDirectory'. Make sure you are using the xp_instance_regwrite stored procedure even if you are just using the default instance of SQL Server.
Now set up three jobs in SQL Agent, each has only one step in it:
Name: dbaBackupFull Category: Database Maintenance Step 1: TSQL against master: exec [dbo].[dba_BackupActiveDatabases] 'full' Schedule: Sunday 23:30 hours
Name: dbaBackupDiff Category: Database Maintenance Step 1: TSQL against master: exec [dbo].[dba_BackupActiveDatabases] 'Diff' Schedule: Monday to Saturday 23:30 hours
Name: dbaBackupLog Category: Database Maintenance Step 1: TSQL against master: exec [dbo].[dba_BackupActiveDatabases] 'Log' Schedule: Daily, every 1 hour, start: 00:00 ends 23:59To exclude a database from backup, set the CreateBackup field in master.dbo.dba_Databases to 0. You may have to add the database first.