asked
How do Logical Backup Devices work?
Hi, I'm new to the wonderful world of MS SQL... Our 'shop' looks after many small SQL databases and they are all backed up daily to individual Logical Backup Devices... Eg the 'Sports' database gets backed up to a logical device called Sports_Data_Backup.bak which maps to D:\SQLBackups I've always assumed that everytime we perform a backup it adds it to the "Sports_Data_Backup"..... So, if we have been doing backups for 6months, there will be 6months worth of backups in the Logical "Sports_Data_Backup" device ??? (unless we have an expiry date set) To investigate this, I have gone to restore the "Sports" database from the logical backup device using SSMS and rather than choosing the latest backup, I can select 'Timeline' and see all of the previous backups, back to 2012! (though I have not tried to restore one of these) The confusing thing is, the "Sports_Data_Backup" logical backup device file is 10Gb.... and if I do a ad-hoc backup to the OS (ie not using the logial backup device) it is also 10Gb! This would suggest that the Backup device is only holding the last backup, which is a little scary (though we do use TSM to copy the backups) I've done some research and it appears that Logical Backup Devices are not very popular as it is easier to manage single files written to the OS. I'm starting to think that it is time to re-write the rule book and that we stop using logical devices... but before I pitch this to 'Management', I'd appreciate your thoughts, comments and corrections to my understanding of how this all works! Many thanks in advance Andy
First of all, backup information is stored in the MSDB database. If this information is not recycled, this databases will be filled with backup information from the first backup being made. Hence the backup info from 2012. There are 2 stored procedures available to dix this issue: sp_purge_jobhistory and sp_delete_backuphistory (see BOL) Second, logical backup devices are something from the past, especially if you were using tape devices. Nowadays a logical backup device is mainly used for 3rd party backup tools like commvault, netbackup etc. If you're using "traditional" MSSQL backups and backup to a disk device, there's no need to use a logical device. Unless you want to add a logical layer to every physical device...
Thanks. The use of logical devices was set up by a previous DBA many years ago and their replacements carried on their example without questioning or understanding why. The trouble we now have is that some logical devices are enourmous and contain months worth of backups... and others only contain the last backup! (fortunately TSM will have a copy of the previous 3 weeks!) I am now slowly working my way through all of our servers and databases 'fixing' this. Thanks again Andy
18 People are following this question.
