At a chance meeting with a staff member from another company in our tea room they asked me what I did. I explained that I was a DBA and that I administered databases, made backups and checked things were working. As they walked off they said "Oh so we blame you then if anything goes missing".
I was a bit perplexed at that statement and could only offer "…only if it is a backup that goes missing…." as the person darted off down the hall.
When I got back to my desk I realised that actually a DBA recovers lost things. When someone else wrecks data a DBA gets it back from backups.
For example the other day I had to recover some data for an analyst. However restoring the whole “metal” database at 16G was too much when all that was lost is several rows from 18 tables. But how do you safely recover that? This is the basic walkthrough:
Firstly I created an empty database called “backup_metal” on the same SQL instance. To create an empty database I usually alternate mouse click on “databases” in Enterprise Manager and choose New Database.
Then I examined the backup job to find the path where the backups were written. Once I found the location I asked the IT systems guys to get a backup off tape from a few days previous when the analyst was sure the records were uncorrupted.
Then I restored the “metal” database over backup_metal. Alternate mouse click on the backup_metal database and choose All tasks then restore database. I usually use the From device option and browse to the database backup file. Most importantly, before I initiated the restore, I clicked on the options tab and ensured that the metal database was restored over the backup_metal database physical files.
Then I requested that the analyst delete only one of the corrupted records as they know the business process for that and it meant one less risk for me.
One of the developers gave me the delete code which was helpful as I needed to know what tables required data again.
Delete * from property where propno= @propno
Delete * from tenement where propno= @propno
Delete * from status where propno= @propno
Delete * from options where propno= @propno
Delete * from claim where propno= @propno
(and so on for 18 tables)
I specified the database and table names to keep it clear and did some manual testing with code like this:
Insert into Metal.dbo.property
select * from backup_metal.dbo.property where propno=1234
This worked fine so I left that record there. However I found the insert does not work when the table had an identity field. An identity field can not normally have values inserted into it. This can be overcome using the SET IDENTITY_INSERT table_name ON command but I found it more reliable to nominate the fields to be inserted and omit any field that was an identity column.
So this code:
INSERT INTO [Metal].[dbo].[Tenement]
SELECT *
FROM backup_metal.dbo.Tenement where propno = @propno
Became like this:
INSERT INTO [Metal].[dbo].[Tenement]([propno], [status], [notes], [image])
SELECT [propno], [status], [notes], [image]
FROM backupmetal.dbo.Tenement where propno = @propno
That is with the identity field removed from the field lists. To create the field list I used an alternate mouse button click option on the table concerned in Query Analyzer.
I constructed a stored procedure that inserted the data required into each of table in turn. Here is part of the stored procedure I created:
-- usp_recoverProperty
Create procedure [usp_recoverProperty]
@propno int
as
-- GJO June 2008
-- Source is : backup_metal
-- Destination is : metal (Live database)
-- Check to see if data is there first
IF not exists ( select * from metal.dbo.property where propno = @propno)
BEGIN
-- Insert the primary record
INSERT INTO metal.dbo.property
SELECT * FROM backup_metal.dbo.property where propno = @propno
END
else
print 'Propno exists in propery table'
--
IF not exists ( select * from metal.dbo.Tenement where propno = @propno)
BEGIN
-- Identity field was [ten_id]
INSERT INTO [Metal].[dbo].[Tenement]([propno],[status], [notes], [image])
SELECT [propno], [status], [notes], [image]
FROM backup_metal.dbo.Tenement where propno = @propno
END
else
print 'Propno exists in Tenement table'
--
IF not exists ( select * from metal.dbo. status where propno = @propno)
BEGIN
-- No identity field
INSERT INTO [Metal].[dbo].[status]
SELECT *
FROM backup_metal.dbo.status where propno = @propno
END
else
print 'Propno exists in status table'
-- Other tables followed
-- Example Usage: usp_recoverProperty 1122
Running the stored procedure gave me a row count of one for each successful insert and if the propno existed in a table then that table name would be listed. After the first run I found I have a repeated table name so had to edit the stored procedure. Then when I ran it every insert gave back a row count of one.
I tried out the stored procedure on one record only then checked with the data analysis guy who checked that property number in the system using the font end application. He was overjoyed. So then we went ahead and deleted all the corrupted records and used the stored procedure to recovery them.
Once reliable backup systems are in place data losses should not be a problem. I think a DBA can get data back!
Graham Okely B App Sc
Senior Database Administrator