From the DBA's perspective, the least demanding way to recover a deleted attachment is to do as follows:
- Restore a recent backup of the database into another environment
- Have the user:
- find the attachment within the application
- download a copy
- save the copy back to the production system
However, this can be a lot of work (particularly if there is no UAT system, or if the UAT system is at a different version). An alternative is to recover the file directly from a database using the procedure below.
There are things that need doing before recovery can start:
- Restore a copy of the database from before the deletion to a temporary location
- Locate the file. To find it, you'll need to start in the DocumentInfo table - narrow the scope of the data you are looking at by using a query like:
SELECT FileUri, FileName FROM DocumentInfo WHERE FileName LIKE '%some text from the file name%';
- There may be multiple rows returned - find the row containing file you're after
- Copy the FileURI, minus the curly brackets.
Having found the URI of the file, recovery can begin:
- Create a table containing just the required file:
SELECT FileData INTO zRescueFile FROM [YourDBName].dbo.FileStore WHERE FileID = '[URI of the file required]';
- At the database server's command line, use bcp to create a new format file from the file we're recovering:
MD c:\temp CD c:\temp bcp [YourDBName].dbo.zRescueFile format nul -T -n -S [Your SQL instance] -f formatfile.fmt
- Open c:\temp\formatfile.fmt in notepad
- The third line contains an 8 in the third column - swap this for a zero and save - so:
1 SQLBINARY 8 0 "" 1 MyColumn ""
1 SQLBINARY 0 0 "" 1 MyColumn ""
- Finally, run this command:?
bcp "SELECT FileData FROM [YourDBName].dbo.zRescueFile" queryout "c:\temp\[your file name]" -T -S [Your SQL instance] -f c:\temp\formatfile.fmt
The file is now at c:\temp, ready to be passed on to the user for attaching back to the Pentana record.
Critical Note: This information is intended for use by qualified database administrators only. Any edits made directly within the Pentana SQL database are not supported by Ideagen. It is strongly recommended that a backup of your database is taken before making any changes and that all changes are thoroughly tested.