Accidental DBA Backup Question
Tim Writes:
I just inherited a 2000 SQL server with 3 databases and 4 system databases that are being backed up but I do not understand the sequence of the backup events.
Under enterprise manager: at 6:00pm databases are frozen then thawed and database backups are taken by our cloud backup vendor. Then at 8:00pm a differential backup is taken and a log backup is taken. But I do not know where the original full backup is. If you are doing a full backup and then differentials and never doing a full backup again the differential backup will increase as it is doing. So I feel that this differential backup is useless unless I find the full correct?
Many a DBA started with a similar experience. You are the last man standing, and have to do what you can to keep the lights on. So, let me share what I know to see if we can get you rolling.
I reviewed the database backup history provided by your database log and found the following databases being backed up. Lets first review these to determine what needs to be backed up.
- Master
- Model
- MSDB
- Northwind
- Pubs
- UserDatabase (name omitted for privacy)
Master needs to be backed up whenever you create new databases or add new SQL Server logins. It is often quite static. There is a good probability in your situation that you could simply do a full backup on master once daily.
Model database only needs to be backed up whenever it is modified. You modify the model database whenever you want to change features included in every new database you create. You probably need to make one backup of this database and ignore it from that point on.
MSDB (Microsoft Database) stores definitions for scheduled jobs (such as your backup plans) and should be backed up with a similar frequency of your User Database.
Pubs is a training database used to demonstrate software examples. It is created by Microsoft. Unless you are personalizing it for something you are doing, it can be ignored. A new copy can be obtained online from Microsoft and the database reset to the original contents.
Northwind is a newer training database. It also can be restored by downloading from Microsoft. Unless you are modifying it for personal reasons, I would also ignore this database when it comes to backups.
Finally, you have your User Database. This is the intellectual property you are most interested in protecting.
You are correct that a differential or transaction log backup is of no value without the preceding full backup. In order to restore a database you must first restore the full backup, followed by the last good differential backup and the following transaction logs in sequence.
So, without the full backup, you have no restoration point because that must be restored first. In some slower changing environments a full backup may only be executed once per week. Then a differential backup may be taken daily. To recover, you restore the last full backup, and then the last differential backup prior to the failure.
In a weekly scenario like the one previously described, there is a lot of redundancy in the differential backups. However, because they run pretty quickly (depending on your system) we throw away some disk space with redundant data (each differential backup) in order to restore more quickly.
I would like to leave you with on final suggestion. An untested disaster recovery plan is not really a plan. You already have questions about the strategy you have inherited. The only way to be sure it works is to emulate restoring your database. You should be able to restore the backups on another machine. I highly suggest this strategy. There’s nothing worse than finding out you can’t restore your database from the backups you have been taking.
Thanks for sending in your question Tim. If you have other questions or suggestions feel free to drop me an email at btaylor@sswug.org.
Cheers,
Ben