How to check multiple backups stored in one file

Backup device is a standard backup file. But this file is registered in management studio and often used to store more than one backups from database. There are also some pros and cons in usage compared to standard backups stored in folders and made by maintenance plan. So let’s have a look.

Pros:

Backups centralized: Backup Device allows you to have handy place where you can access your backups in very easy and fast way (directly from Management studio).  You don’t need to know on which storage .bak files are located. Device could have simple name corresponding with db name in which all db backups are located in one place.

Fast access from TSQL: If you ever used TSQL in restore sequence you have been probably bored with describing paths in RESTORE FROM DISK command. You could make a typo in file path which could lead to inconvenience in restore process. Backup Device allows you to just type the name of the device without boring with path to .bak files. This will lead to faster and safer restores, because it decreases the chance for human error.

More protective: If you have all backups in one file you can use it for your advantage. Let’s imagine the situation in which you are using standard backup procedure to store transaction log backups in one folder.  If someone deletes one of the backup files, your whole sequence of log backups will be useless because they are incremental. Only continuous sequence of log backups can be restored. With backup Device you will have all backups in one file. So you will lose all or nothing which is in this case better. You will faster notice missing of whole backup device than missing one file in folder filled by many files.

Cons:

Automating backups: If you are fans of maintenance plans because they are fast for setting up backup strategy for whole instance, I have bad news for you.  Backup task have no option to pack all db backups to their respective backup devices (e.g. one backup device per one db). So if you need to setup strategy fast, you are forced to store db backups in folders. One option is to create maintenance plan per db so you will manage to backup into one device per db.

Automating backups cleanup: Currently there is no way how to remove single backup from backup device, so if you want to cleanup backup device I recommend to first backup device file to tape (to preserve backups) and then issue Full backup with INIT to initialize (delete all backups from backup family) backup device.

Backup verification: If you are using RESTORE VERYFYONLY WITH CHECKSUM to check whether data pages in .bak file are correct, you will found Backup device not much useful, because mentioned command verify only first backup in the file by default. You will be forced to use FILE = parameter to select every backup in Backup device. This is pretty ugly and boring thing :(

To overcome this issue I have prepared Backup device checker for you.

This stored procedure allows you to check multiple backups in one file (backup device). It checks backups with CHECKSUM and also without CHECKSUM located in one file together. Currently it doesn’t understand standard backup files (with paths) until they are registered as backup devices. But it is ok, primary purpose of this procedure is to check Backup Devices :)

NOTE: Script for download USP_DeviceCheck.sql

So I found Backup Devices pretty useful, you just need to spend few more moments on server when setting up your db backup strategy. But it will return in saving more time when you will need to restore database or check for your backups on one of your instances (especially if you have many of them with different storage setups). Backup devices are not useful if you are not regularly moving Backups to tapes or different location, because we are not able to cleanup single backup from device.

Hope it helps.

Mike.