Remove Tape from DPM database

When the data on a tape has expired it will show in the Tape Management Report as Tapes due online in the next x weeks.

When you do not add the tape back in the library the tape will eventually show up as over due Tapes.

There might be a situation where you are unable or unwilling to place the tape back in the library. For instance when the tape is broke, has been in the cycles for to long or the tape is even lost. In these cases you don’t want the tape to show up in report each week.

The procedure below (written by Mike Jacquet from Microsoft ) shows how to remove tapes from the Tape Management Report.

To remove a tape media from the over due tape report, you can run a SQL script to remove the media from the DPM database.

To run the script, perform the following steps:

1)  Open the DPM console and under the reporting tab, double-click the "tape management" report and select the number of weeks you want a report for (up to 4 weeks).
2)  Once the report opens- go to the page that list Over Due Tapes.
3)  Make a note of the "Tape labels"  for the tapes you want to remove from the DPM database so they will no longer show up on the report.

4) Make a backup of the DPMDB Sql database before proceeding using the following command:

     DPMBACKUP -db (The database will be saved in the C:\Program Files\Microsoft DPM\DPM\Volumes\ShadowCopy\Database Backups folder.

5) Open SQL Enterprise manager and connect to the Server_name\$MSDPM2010 instance.

6) Under DATABASES - Highlight the DPMDB entry - then click on "NEW QUERY" button.
7)  Copy / paste the following SQL script into the new query window.

---------- START COPY HERE -------------

-- overdue tapes
-- for clarity, set up the parameter as a variable
declare @paramTapeLabel as nvarchar(256)
set @paramTapeLabel = N'SAMPLE_TAPE_LABEL_NAME'

-- keys
declare @vMediaId as guid
declare @vGlobalMediaId as guid

-- if the delete gives trouble, add keyset after cursor
declare cur_label cursor
for select MediaId, GlobalMediaId
from tbl_MM_Media
where label = @paramTapeLabel;

open cur_label
while (0 = 0)
begin
fetch next from cur_label into @vMediaId, @vGlobalMediaId
-- test for being done
if @@fetch_status <> 0 break;

print 'Deleting MediaId = ' + cast(@vMediaId as varchar(36))
-- do a set of deletes atomically
begin transaction;
delete from tbl_MM_TapeArchiveMedia
  where MediaId = @vMediaId;

delete from tbl_MM_MediaMap
  where MediaId = @vMediaId;

delete from tbl_MM_ArchiveMedia
  where MediaId = @vMediaId;

delete from tbl_MM_Global_ArchiveMedia
  where MediaId = @vGlobalMediaId;

delete from tbl_MM_Global_Media
  where MediaId = @vGlobalMediaId;

delete from tbl_MM_Media
  where current of cur_label;

commit transaction;

end
close cur_label
deallocate cur_label

-------------- END COPY HERE ----------------------

8)  replace the tape label name parameter in the script with the name of the tape label from the over due tape report that you want to delete.
        set @paramTapeLabel = N'SAMPLE_TAPE_LABEL_NAME'      <--- replace tape label between the single quotes ' '

9) Execute the SQL script.

10)  Repeat steps 8. and 9. for each tape label that you want to delete.

---