Rebuild a database from a point in time

twdavis

Crazy question! I know!
Can a database be rebuilt from a point in time? If so how,?
Is it possible to restore a database to a point in time, say August 31, 2008, and then programatically, perform all transactions from that point in time to the present?
Where would one start?

kine

Native or SQL server?

If SQL, how the backup are configured?

garak

twdavis wrote:

Crazy question! I know!
Can a database be rebuilt from a point in time? If so how,?
Is it possible to restore a database to a point in time, say August 31, 2008, and then programatically, perform all transactions from that point in time to the present?
Where would one start?

if you use SQL and you have all backupfiles (min. one fullbackup before the August 31 2008 file and all TransLogBackupfile since the first fullbackup) and u use FULL Backupoption.

If you have NAV it's a little bit more complex.

Regards

DenSter

garak wrote:

If you have NAV it's a little bit more complex.

I don't agree, on NAV it's very easy: not possible.

On SQL Server.... why would you want to do this? If you are going through all of the trouble to restore a database per August 31st, and then restore all transaction log backups until today (which you would only be able to do if your transaction log chain has not been broken since August 31st by the way... are you willing to make that guarantee?), why not just restore yesterday's backup?

Maye you can explain to us what it is that you want to do, and maybe someone in here has a good suggestion.

garak

I don't agree, on NAV it's very easy: not possible.

i mean it so: if you have more backups (fbk) , like since the 31.Aug.2008 (every day one or two), you can create databases for the needed day (but not for the needed hour of a day like with sql).

But the question is: Why he need it. Or is the 31.08.2008 only an example (i think it is so). With sql it's possibel with Log Backups.

Alex_Chow

Wow... This is one of those questions on the SQL Server exams that NSCs have to pass. There were a couple of questions on the study guide and the exam that asks the same type of question as the one you're asking.

Apparently, this is a common requirement amongst the SQL Server community.

If you're using SQL, the answer is YES. However, you need to have the proper backup (Full, differential, and TLOG). If you're unsure how to do a restore, get a SQL guy to help you.

If you're using Native database and you don't do full backup every hour, then you won't be able to restore up to a particular hour.

DenSter

This particular question was about restoring the database as per August 31, 2008 and replay every transaction since, which you could only do with transaction log backups, which would only be possible if their transaction log chain hasn't been broken since that date, which I really don't think any DBA would vouch for.

I don't think it is at all common to restore 4 months worth of transaction log backups, that is not a normal scenario at all. I do think there's more to this question.

Alex_Chow

A good backup policy on SQL is a full backup everyday, a differential backup every 4 hours, and TLOG backup every hour.

If done this way, the restore process would be a lot easier.

DenSter

I know what 'good practices' are, that was not the question though.

My gut feeling says that he is not looking for best practices for SQL Server backup strategies, but for some sort of way to automatically run transactions, like a test script, for a load test or something. Why else would you want to be able to replay transactions since 4 months ago?

Alex_Chow

DenSter wrote:

I know what 'good practices' are, that was not the question though.

Not responding directly to the question. I'm just making a general statement so he can get what he wants next time.

garak

DenSter wrote:

My gut feeling says that he is not looking for best practices for SQL Server backup strategies, but for some sort of way to automatically run transactions, like a test script, for a load test or something.

Now it's time for twdavis to give a statement. Ask he this question only why he is in a SQL Server exam or why he really need, for example, a testscript.

David_Singleton

DenSter wrote:

I don't agree, on NAV it's very easy: not possible.

Sure its possible. ... You just need C/DART. With C/Dart you just roll back to the exact point in time that you want.

kine

But I am sure that with some limit how deep you can go... ;-)

DenSter

David Singleton wrote:

With C/Dart you just roll back to the exact point in time that you want.

Really? C/DART even keeps track of past versions of master records? If that's the case then I know where the inspiration for a wellknown history and security add-on came from.

twdavis

Thanks for the replies.
The database is Native Navision 4.3. The client has made numerous erroneous changes to their Currency rates, Posting Groups, Standard Costs over the past 4 months. All activity since then has resulted in major data discrepancies. The question posed was can we 1) save a copy of the databse aside for reference, 2) "delete" all transaction activity from the current database going back to August 31, 2008, because the currency settings , posting setups, standard costs, etc. have now been corrected. 3) perform each of the transactions up to current date.

Since it is not SQL, there is no tlogs available.

We have concluded that this is not a feasible option and are trying now to establish a good base from which to proceed.

DenSter

1: Yes you can, either by creating a backup, or just a plain copy of the database files. A backup would be easiest I'd say

2: Technically you can delete whatever you want. The bigger question I think is whether it would be legal to delete financial historical records.

3: Related to my reply for 2, you'd have to create correcting entries instead of deleting and redoing all transactions.

There is no automated way to 'replay' transactions, so you would have to re-enter all transactions manually, which I guess if the volume is small enough could be done.

Aside from legal issues, if you have a database backup for August 31, you could create a new database with that, make sure that all set up is correct, and enter all transactions. It would be a huge task to get it just right though, and I really don't think it is legal.

garak

if you have some wrong postings (because wrong setup), create the correction postings to "cancel" it and then, after changing the setup, the correct posts.
To create a new database based on a old backup and repley all postings per hand is also a way. If it is legal as Denster ask? I don't know the law in US.