Possible to Get List of Managed Backups?

SloopJohnB

Hi -
I have purchased Virtual Restore for my reporting server. On this server, I have already set up over a dozen DBs which are managed via SQL Virtual Restore. Some of these DBs are long-term and some are ad-hoc (i.e., for quick testing of proposed updates, etc.).

Since (1) I have so many DBs and (2) I have problems with Virtual Restore holding on to backups even after a Virtual DB is deleted, I have taken to documenting which backup files are being used by which virtual DBs. Right now, this is in a simple manually-maintained Excel worksheet. I'm wondering if there is a way that this information can be gleaned out of the Virtual Restore utility itself, so that I can take the manual (human) element out of the equation.

Thanks in advance for your help.

fgsimon

Hi, I have made the same request to support, apparently there is already an issue reported: ref. SVR-256. Just email support and ask them to do a +1 on your behalf.

Our disk will soon fill up unless we can solve this in an automated way!

/Simon

javen

Hi,

In the meantime, before this information is available in the UI, you can use the following SQL script to get the information you are after, let us know if this helps:

set nocount on
declare @dbname sysname
declare @sqlstmt varchar(1024)

declare dbases cursor
for select name from sys.databases where database_id > 4
for read only

print 'The following databases have been Virtually Restored:'

open dbases
fetch next from dbases into @dbname
while @@fetch_status = 0
begin
	select @sqlstmt = 'IF (SELECT count(*) FROM {DATABASENAME}.sys.database_files where (RIGHT(physical_name,4) = ' + char(39) + 'vmdf' + char(39) + ') OR (RIGHT(physical_name,4) = ' + char(39) + 'vldf' + char(39) + ') OR (RIGHT(physical_name,4) = ' + char(39) + 'vndf' + char(39) + ')) > 0 PRINT ' + char(39) + '{DATABASENAME}' + char(39)
	select @sqlstmt = REPLACE(@sqlstmt, '{DATABASENAME}', @dbname)
	exec (@sqlstmt)
	fetch next from dbases into @dbname
end

close dbases
deallocate dbases

fgsimon

Thank you for the sql, but I really need a way to find which Hyperbac zip-files are being used.
/Simon

PS
The reason is that we have automated backup/restore routines, so we can not rely on people making notes of which files are being used and which are not used anymore.
So, I need to able to get this information form tsql/bat/shellscript or some other way, so that I can write some code that automatically removes unused zip files.