Unofficial Solution: Open a closed media set

[shark]

Dear Community,

I had some trouble with Veeam because it closed a WORM tape media set and created a new one, which I did not want.
Using standard tapes such a behavior is annoying, but using WORM tape this behavoir directly costs lot of money and can easily happen again when a person inserts a wrong tape in standalone drive and backup-to-tape-job starts.

I searched a lot on the web but could not find a solution to 're-open' a closed media set.
I did research on this topic myself and found a way, which for me perfectly worked. However this is not offically documented and your doing the following steps on your own risk without having any guarantee it will work for you. This method here was not provided by veeam support so one more reason to be very careful what you are doing:

I) PEPARATION
Ensure that in case of a standalone drive Veeam only asked for a new tape because a new media set was started. Important is that this requested tape was never loaded and no following job was processed. If you already loaded a new tape and started a job with the new tape, the following process might result in incostistent backups which you might not be able to restore!

II) OPERATION
Step 0)
On your Backup-Host where Veeam is installed open 'regedit' and navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Veeam\Veeam Backup and Replication
Here you can find the database name of SQL-Instance and database VEEAM uses.
Now open SQL management Studio or similar to connect to database.

Step 1) Disable all jobs in Veeam BR
We don't want Veeam to perform any job while we ware manipulaton its database later!

Step 2) Backup database
Perform a backup of Veeam database using SQL Management Studio. If anything goes wrong you will be able to read it back later.
For doing so it is important the database is not changed by Veeam itself inbetween. Therefore don't skip "step 1", because each run of a job would change the database. If the you restored the backup database later you end up in an inconstistend state of what's on disk/tape and what Veeam knows about it.

Step 3)
Show all media sets you are using in Veeam

Code: Select all

SELECT *  FROM [VeeamBackup].[dbo].[Tape.media_families]

Pick that one which you want to open and that one which has been created which you did not want to.
For both note the "id", be sure not to mix them up.
In the example I'll use 123 for media set to open, and 456 for the one which was created but I did not want to.

Step 4.1) Open the closed media set
(In following statements I use "rollback" to test operation, feel free to change "rollback" to "commit" after you verified results! Only then the changed take effect permanently in database)

Code: Select all

begin transaction 
	  update [VeeamBackup].[dbo].[Tape.media_families]
	  set is_closed=0
	  where id='123'
         
         /* Show results of changes which were made */
          SELECT *  FROM [VeeamBackup].[dbo].[Tape.media_families]
 rollback /* commit*/

Step 4.2) Delete the unwanted, new media set

Code: Select all

begin transaction 
                delete from [VeeamBackup].[dbo].[Tape.media_families]
		where id='456'
		
		/* Show results of changes which were made */
                SELECT *  FROM [VeeamBackup].[dbo].[Tape.media_families]
rollback /* commit*/

Step 5)
Show all media which are introduced to Veeam

Code: Select all

SELECT * FROM [VeeamBackup].[dbo].[Tape.tape_mediums]

Now chose the tape, which was the last one you used for the specified job and Veeam closed but you did not want it to be closed. Note its ID, in my Example I am taking 789

Code: Select all

begin transaction
  update [VeeamBackup].[dbo].[Tape.tape_mediums]
     set continuation ='1'
  where id='789'		
rollback /* commit*/

What we have now:

1. the unwanted new media set is deleted
2. the 'old' media set is open again
3. the medium you orignially wanted to insert and use but you did not by a mistake is arked a open for append

Now I could run the tape job again which appended data to the tape.
Since this here is not documented and the result of deep investigation in Veeam database (not recommended) please test the whole backup chain(s) if the data is consistent. In my case it worked fine, I can restore and append data and did not need to buy new WORM tape for lot of money.

I am interested in your experience so please let me know your thoughts and results or even modifications.

[Dima P.]

Hello shark,

If you use WORM media sequentially and do not eject the WORM media before tape job run (so tape is present when tape job kicks in) backup job will continue the WORM media and use all available space. I strongly do not recommend any database modifications on a production environment because it may affect your tape media 'media set' sequence and as a result you might have issues during recovery. Thank you!

[shark]

Dear Dima,

thank you for additional comments!
Yes, if you use WORM media sequentially and do not eject the WORM media before tape job run backup job will continue. The above mentioned approach is for the other case, you do eject and did not load the right medium at the right time...
I agree, that you cannot recommend any database modifications. Modifications are done on own risk!

[david.domask]

Forgive me for intervening and being "that guy", but I do need to stress a few points on the above.

1. Database modifications without the supervision of Support are not supported
2. Any issues determined to be a result of such modifications will not be supported nor will Support assist with correcting issues related to the changes
3. Veeam Support cannot assist with any elements related to DB modifications intended to circumvent the intended behavior of the software, even if it's undesired behavior

shark, your post is really good and you made some great commentaries here, I sincerely appreciate what you've done! I just want it to be 100% clear for anyone who wants to pursue this path that Veeam Support's ability to help you will be quite limited, so there are no surprises as you follow this path. shark's guide "looks" sound, but this is not an official endorsement, just a personal appreciation