Have you seen the video from Grant Fritchey regarding database backups for the impatient people that are as yet to see it or for those of a strong enough constitution to watch it again you can watch it here but i would say hold on a moment…

Grant makes some great points in this video and any DBA who values keeping their job beyond the next time they are asked to restore a database ought to pay a lot of attention to it.

Anyone using SQL Backup Pro from Red Gate has a head start on calming Grant down and being in the clear with all the checks he suggests demands we all use.

In the most basic form the database backup is done with TSQL like this:

Let’s review what a backup is, it’s a file on your disk somewhere, it might be on your SQL server, on a NAS or SAN or it could be on your back up tapes or even nowadays somewhere in the ‘cloud’ in a data centre. It is still just a file. Prone to corruption, incomplete writes and so on.

How can we avoid this?

Well, how about when SQL Server creates the backup that it checks that the data written is the same as the data read from the database? We can do that by using the CHECKSUM keyword. Let’s see how that happens in TSQL:

We now know that the data comprising the backup file is the same as the data in the database. So that is our next problem. What, you don’t see the problem there? Well, consider that a page in the database file (mdf, ndf or ldf ) got written badly the last time it was accessed. If we go to read that again then there could well be an error. If we copy the database contents exactly faithfully to the backup then when we restore the database then the corruption will be restored and down goes the database in the same way as it would have in the original. How do we check for corruption in database files I hear you asking? We use a DBCC (Database Consistency Check) command:

This reads every page and will alert you if there are any errors in any part of the database files. Read more about CHECKDB in books online.

So, if CHECKDB is coming up clean and then CHECKSUM is making sure that the backup file is identical to the database we are making progress. Next we need to prove that the backup file will is actually accessible as a valid SQL Database backup. CHECKSUM checks the data part but there is information, metadata, in the backup file that it doesn’t check.

How can we do this? Well, how about running a pretend restore of the database backup just to check the backup header information? Great idea. The TSQL to do that is:

This runs a quick check to see if, in theory, the backup file will work in the event that we ask SQL Server to use it to restore a database. Pretty neat, but not 100%, absolutely, copper-bottom, guaranteed. What? That’s no good, DBAs need to be 100% sure about this sort of thing. Well, RESTORE VERIFYONLY doesn’t actually check ALL of the header. So what can we do?

There is only one thing for it. Restore the database completely. Use the backup file to create a new database and providing that succeeds then we know the backup file is going to work if we need it.

Great. We have a database that is error free, a backup that is an exact copy of the database data and will restore successfully when we need it.

One thing to do now would be to run DBCC CHECKDB on the restored database to make sure the mass write to the new disk location (we are assuming the production server is still on fire) has been error free.

This is a pretty simple process but it gets very time consuming doing this for every backup, for every database and it cant take place on the production hardware for fear of interfering with the live databases.

This is where SQL Backup Pro has a major benefit for the busy DBA. This can be all automated for each database you set the restore location, the database name, what checks to make on the backup and the restore, what to do once the database is restored depending on the success of the restore and the DBCC CHECKDB. All the details about how SQL Backup Pro does this and how easy it is to use are explained by the Red Gate DBA-Team here.

Since installing it I am restoring approximately 50 databases a night, checking them for errors and then dropping them. Each and every night. I know for sure that the backup files that are created in case of a disaster are going to be ready for me to use and will work as i need them to to let me keep the company systems running.

Now, let’s fix a smug grin on our faces and go watch the Scary DBA again.!