Red Gate forums :: View topic - Possible to Get List of Managed Backups?
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Virtual Restore 2
SQL Virtual Restore 2 forum

Possible to Get List of Managed Backups?

Search in SQL Virtual Restore 2 forum
Post new topic   Reply to topic
Jump to:  
Author Message
SloopJohnB



Joined: 22 Jul 2006
Posts: 17

PostPosted: Tue May 22, 2012 6:39 pm    Post subject: Possible to Get List of Managed Backups? Reply with quote

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.
Back to top
View user's profile Send private message
fgsimon



Joined: 21 May 2012
Posts: 18

PostPosted: Wed May 23, 2012 9:49 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
javen



Joined: 25 May 2010
Posts: 50

PostPosted: Wed May 30, 2012 1:43 am    Post subject: Reply with quote

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:

Code:
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



_________________
Jeffrey Aven
Product Management - HyperBac Technologies
Red Gate Software
Back to top
View user's profile Send private message
fgsimon



Joined: 21 May 2012
Posts: 18

PostPosted: Wed May 30, 2012 8:45 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group