Doing backups might become a repetitive and reflex action if done for quite a long time. But due to this, you might skip a few things at the beginning and never add them to your checklist schedule when doing backups.
The following article presents a list of databases to backup and reasons why you should do it.
Databases to backup
Apart from the production databases which are the main reason for which we do backups, there are other databases that you might miss when doing your regular backup schedule.
First on the list of other databases which you should be backing up is the master database. Another essential database which you should not forget to backup is the msdb database.
Backing up master Database
You might be asking yourself why should you do it? Well, the answer is quite simple.
The master database is essential and actually is the most important database in the system because it has all of the information about all of the other databases present in the system. This is why the master should be the first in the line of databases to backup.
Thus, your master database has to be backed up on a regular basis. You should do this backup every time you do a modification to a database that is in the system. The creation, modification, or removal of any database from the system will change the state of the master database.
So, if you want your system to be backed up to its latest state, remember to backup your master database as well.
Without a backup of your system’s master database, you will have to rebuild all system databases completely in case your master database is damaged as all references to your existing user-defined databases will be lost.
You will only be able to perform a full backup of your master database as SQL Server does not support differential, transaction log, or file backups for the master database.
Backing up Production Databases
This point is obviously the biggest (and maybe sometimes the only) reason to do backups and it’s why it’s second in our list of databases to backup.
You probably already know this, but each production database should be backed up regularly. Back them up after you create them after you add indices after you clear their transaction log, and each time you do nonlogged operations.
Backing up your database after creating one or more indices will eventually save you time during the restore process because the structure of the indexes will be backed up together with your data which will make the restore process much faster.
You might expect that if you create the database, back it up, and then add indices and do a transaction log backup, during the restore operation you will have the same performance.
Actually, when doing the log backup, inside the log it is only specified that the index was created and it will try to be found on the FULL backup and used during restore, but if it’s not then your restore process will go much slower than expected.
Remember those transaction log backups cannot be made until you do a FULL backup of your database.
Another time you should be backing up the database is after the transaction log has been cleared as the transaction log will no longer contain any database activity records which you can use during restore.
All operations that are not recorded by the transaction log are called nonlogged operations. The following operations are considered nonlogged operations and this is when you should also be doing backups of your production database:
- After running a script that contains the
WRITETEXTorUPDATETEXTstatements without theWITH LOGoption - After running a
SELECT INTOstatement - After using the bcp utility
Backing up msdb Database
The next database in this list of databases to backup, you should definitely remember the msdb database.
This database is used for storing things like alerts, jobs, and other recording operations that are also used by the SQL Server Agent service.
So, any modification to these operations will have an impact on the msdb database.
There are software applications that you can use to do the backups of these databases along with your production databases in a fast and easy way. You can find these applications here along with other tips that will help you improve your backup habits.