Backup file information.

There are times when I see a large backup file with a very ambiguous name like ‘20100102_backup_000001.bak’. These may be the names created by the SQL backup jobs. When free space has to be created, deleting some of these huge backups will help. But which of these files can I delete? A simple query for this is:

RESTORE HEADERONLY
FROM DISK = N’e:\stemp\DB_backup_201007270001.bak’
WITH NOUNLOAD;

This query will show only the header information of the SQL backup file.  Some of the useful columns for this post are: ServerName, DatabaseName, MachineName, and BackupFinishDate. With this data, I get some information as to when the backup was taken and from where. Then I can decide on weather to keep or turf the backup.

There was a situation in which I could not restore a DB via SSMS. I was creating my TSQL to do the restore when I realized: How do I know what are the names of the different logical files of the DB. Luckily there is another backup command that can be used to see a list of the logical files of the database:

RESTORE FILELISTONLY
FROM DISK = N’e:\stemp\DB_backup_201108020001.bak’

Now that I know the logical names, I can use them in my restore script and place the files at the proper file system location.

If you come across a backup file that you cannot restore, you can verify the integrity of the backup. The command I use is: restore verifyonly from disk=N’Z:\backup\DB_backup_201108020001.bak’. This will return an error if the backup file is corrupt.