DPM 2007 and DPM 2010 - Remove Damaged Tape from Database

posted 15 May 2013, 01:26 by Tristan Self [ updated 29 May 2013, 00:36 ]

Tapes are basically sticky tape and rust, therefore they are liable to break especially your weekly rotation ones, just because they get wear and tear. When this happens and the tape is no longer able to be used, we need to remove it from DPM to stop it appearing the in the tape request reports. With DPM, there isn't anyway to remove the tape using the GUI so you need to run a SQL script to get rid of the tape and stop it asking for something you can never put into the tape drive.

1. First get hold of the tape label name, for example: 000013L4

2. Next backup your DPMDB database, this is a must, if this script corrupts it you want to be able to restore. You can do this by:

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

2.2 Microsoft SQL Management Studio - Start off a (copy) backup of the DB and store it somewhere safe.

3. Now open the Microsoft SQL Management Studio, open a "new query", select the DPMDB database as the target for the operation.

4. Now paste into the window the following:

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

-- overdue tapes
-- for clarity, set up the parameter as a variable

declare @paramTapeLabel as nvarchar(256)
set @paramTapeLabel = '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 ----------------------

5. Alter the line @paramtapelabel where the N'SAMPLE_TAPE_LABEL_NAME' should be replaced with: '000013L4' (that's right you leave in the single quotes, or it won't work.)

6. Now execute the script, assuming it worked without error, that's it the tape has been deleted from the database.

7. You should find that the next tape report won't contain the faulty tape as it no longer knows about it and the job will use a new one instead.

CITATION: http://social.technet.microsoft.com/Forums/fr-FR/dpmtapebackuprecovery/thread/dede3c6c-aa37-440e-9205-25008d89059b

I've repeated this post on my site as its too good to lose to the Interwebs.

UPDATE for Microsoft DPM 2010

If you are using Microsoft DPM 2010, you need to adjust the code slightly, because the name has been changed of one of the columns where it has the Label = @paramTapeLabel; you need to change this to BarcodeValue = @paramTapeLabel; instead. Now when you run this script you should see 5 or 6 results saying:

Deleting MediaId = 5DCFFDBC-29E9-4572-B1C1-BE1A9A10A237

(1 row(s) affected)

(5 row(s) affected)

(1 row(s) affected)

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

-- 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 BarcodeValue = @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;