Recovering SQL data from Navision

aleix1979

hi,

I've got a big problem. Does anybody know how to recover data from a SQL database?

The instruction was commanded by a DELETEALL instruction that deleted all Contact records

Any ideas please!!!!!!!

[-o<

DenSter

No that data is gone forever... #-o

The good news is that if it was really DELETEALL, then no related records have been deleted, so it should only be from the table itself.

If you have a test database that is a copy of your production database you could copy it back from the test database. Another idea would be to create a new local copy of the database using the most recent backup and copy it from there.

Don't just copy/paste it though, use a dataport.

kriki

Actually, there is a way in SQL, at least if you have the FULL recovery model.
In this case you have to use the SQL-backups to restore the DB.
In the SQL-backup there is an option to restore the DB up to A CERTAIN POINT IN TIME. So you have to know less or more when the DELETEALL has happened and you restore the DB until just before it. The negative of this is that all transaction that happened AFTER the DELETEALL are gone.

Another way (less or more with the idea of DenSter) : restore the backup in a new DB (you can also use the SQL-restore until a certain point in time). Make a dataport for the contacts to export them from the backup and restore them in the production DB.
With this you just lose changes made after the backup.

DenSter

It is always best to recreate data by restoring a backup. If there haven't been many transactions after the 'incident', and oyu can live with the 'loss' this is the preferred way to go of course.

I figured since you'd have to do a backup either way it would be easier to write a simple dataport and do it that way. Of course if you don't mind the transaction loss then the restore is the best way to go, that ensures the highest level of data integrity. With just copying data from one table into another you always run the risk of missing something. For instance deleting the contacts might have actualy deleted some other data as well.

matthiasclaes

It's not that hard, but you need some basic SQL Server skills.
I've had to do this 3 times by now. Depending on table, it takes me about an hour and a half.

1. Restore data

Using SQL Server Management Studio, perform a point-in-time restore into a new database.
Your point-in-time should be up to just before the DELETEALL was committed.

2. Transfer data from restored DB to operational DB

Because of the Timestamp field in every table, you cannot execute the following statement:
insert into [Target] (select * from [Source])

We need to specify the fields to transfer.
- Copy-Paste the list of fields (Table, Design) from NAV to Excel.
- In Excel, get rid of all the flowfields and filter fields by removing those rows + remove the top row.
- Select the Fieldname column, and copy-paste special, Non-formatted text to Word.
- In Word, Replace All alinea marks (^p) with ],[
- Add a [ in front of it all and a ] at the end of it all. You now have a list of the fields enclosed in [] separated with commas. Copy this to the clipboard.
- In SQL Management Studio, create a new query:
insert into [Operational DB].dbo.[Company$TableName] (<Paste list of fields here>) (select <Paste list of fields here> from [Restore DB].dbo.[Company$TableName])
- Execute query and wait of few seconds depending on amount of data.

If there are fields that cannot be transfered (flowfields etc.), they will show up as errors and the query will not execute. Remove them from the query and try again. Remember, each field is listed twice: once in the insert into clause and once in the select from clause.