The Importance of a Full Backup in SQL Server

This is the first of 12 posts this year in support of Tim Ford’s #iwanttohelp initiative. These will be completely 100 level, introductory blog posts meant to help people that are just getting started as data professionals. I’ll try to cover several different topics over the year, but felt I should start with what I think is the most important, backups.

It is impossible to overstate the importance of getting a good backup of your SQL Server databases. A backup is the most fundamental of protections for the information on which your business is dependent. Since SQL Server is a service, it manages it’s own files. Because of this, you can’t just copy the *.mdb file where your data is stored. Instead, you must run a process, usually through the BACKUP command within SQL Server. That link goes to the MSDN documentation at Microsoft (which is always your first source for information about SQL Server). The first command outlined there is BACKUP DATABASE… This will create a complete, page-by-page (everything is stored within the database file in a construct called a page), copy of your database, including every single object within the database and all the data.

Well, you think, I’ve got that bit. I’ll take one of those full backup thingies and I’m all set. Ooh, look there are all kinds of other backups too. Log, Differential, File, Filegroup and options, COPY_ONLY, MIRROR… Not only does this get complicated, quickly, but it’s very easy to lose sight of the most fundamental aspect of all this. That is, that backups are not the main thing you’re going for here. The main thing you’re going for here is the ability to RESTORE your database. That link goes to the MSDN entry on RESTORE.

As you explore all the aspects of backups, you must always remember that the most important thing is that you can restore those backups. The most important aspect of the RESTORE operation, whether you’re restoring a full backup, a differential backup, a file backup a filegroup backup or a log backup is that every single one of them starts with a restore of the full backup. If you lose your full backup, that complete, BACKUP DATABASE command, you can’t run the first restore needed. Yes, taking log backups means that you can restore to a point in time. That starts with a restore of the database from a full backup. Yes, taking differential backups can be faster because they’re copying fewer pages to create a backup. Restoring a differential is only possible when you have the full backup that was taken before that differential was taken. The same goes for the rest of the backups (with some exceptions that are WAY beyond entry level). You must have that full backup before you can do the rest. This simple fact is why the full backup is so important.

So please, if you’re just getting started working with SQL Server, then get your backups in order, but make darned sure that you know exactly where your backups are stored and that you can get to them when the time comes to restore a database. Without that full backup, you’re in serious trouble.

I have a video of exactly how to take a full backup of your database using T-SQL to help you get started. Just remember, don’t lose that backup.