Database restore fixes problem - why?

We are using a SQL Server 7.0 database for a financial software package that
we use. Every year we run into problems with the software package not
working properly on a certain function. The solution to fixing the problem
we have discovered is to simply back-up the database and then restore it.
After doing the restore of the database, the problem is fixed. Does anyone
have any idea why? And, if so, can you tell me what could be down to prevent
the problem in the first place? What is it about a restore that would change
something enough to make it suddenly start working correctly? Thanks for
anything you can offer. This is driving me nuts.
Jeff
Jeff
You did not say what is the problem?
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:F67A59F9-DCA9-4830-9C24-1E9096A26360@.microsoft.com...
> We are using a SQL Server 7.0 database for a financial software package
> that
> we use. Every year we run into problems with the software package not
> working properly on a certain function. The solution to fixing the
> problem
> we have discovered is to simply back-up the database and then restore it.
> After doing the restore of the database, the problem is fixed. Does
> anyone
> have any idea why? And, if so, can you tell me what could be down to
> prevent
> the problem in the first place? What is it about a restore that would
> change
> something enough to make it suddenly start working correctly? Thanks for
> anything you can offer. This is driving me nuts.
> Jeff
|||Uri,
The specific problem is within the financial sotfware - when we do a year
end close the software creates a batch of transactions then the next step is
to "post" those transactions. The software creates the batch OK, but when we
try to post the batch it just crashes the program without giving any error
messages.
After the restore, everything works as it should.
"Uri Dimant" wrote:

> Jeff
> You did not say what is the problem?
>
> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> news:F67A59F9-DCA9-4830-9C24-1E9096A26360@.microsoft.com...
>
>
|||Jeff,
without understand what your financial system does and without any errors
whatsoever, you can imagine that this is a needle in a haystack response!
If this happens everytime without fail, I suggest replicating it in you test
environment and profiling what happens to start with.
Immy
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:19173B07-D12D-445E-A433-D5A5F0666FD0@.microsoft.com...[vbcol=seagreen]
> Uri,
> The specific problem is within the financial sotfware - when we do a year
> end close the software creates a batch of transactions then the next step
> is
> to "post" those transactions. The software creates the batch OK, but when
> we
> try to post the batch it just crashes the program without giving any error
> messages.
> After the restore, everything works as it should.
> "Uri Dimant" wrote:
|||Jeff
I still don't undertsand how does it relate to SQL Server? Is it possible
that the problem is in software program?

>but when we
> try to post the batch it just crashes the program without giving any error
> messages.
What does it mean? The program tries to insert the data into a table and
then it crashes?
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:19173B07-D12D-445E-A433-D5A5F0666FD0@.microsoft.com...[vbcol=seagreen]
> Uri,
> The specific problem is within the financial sotfware - when we do a year
> end close the software creates a batch of transactions then the next step
> is
> to "post" those transactions. The software creates the batch OK, but when
> we
> try to post the batch it just crashes the program without giving any error
> messages.
> After the restore, everything works as it should.
> "Uri Dimant" wrote:
|||Uri,
It's possible that it is within the software, but what I'm wondering is -
what is it about a SQL database restore that would cause the same software
code to go from not working to working all of a sudden? What changes occur
to the database when a restore is done?
I realize this is a tough one to answer, I was just hoping that you might
have some ideas as to why the restore would "fix" things.
"Uri Dimant" wrote:

> Jeff
> I still don't undertsand how does it relate to SQL Server? Is it possible
> that the problem is in software program?
>
> What does it mean? The program tries to insert the data into a table and
> then it crashes?
>
> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> news:19173B07-D12D-445E-A433-D5A5F0666FD0@.microsoft.com...
>
>
|||Jeff
I see that you would not want to "reveal" more info about your business
requirements , it is ok , however we will no be able to help you much if you
cannot provide more info
RESTORE operations does simple thing , it reads all datapages/data from the
..BAK file and create a new database assume you want to create a new one
If you had a proper database backup and then your software dod some mistake
, you can come back to the previous state of the database to restore it. It
does not fix anything
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:15F36608-14B2-456F-9032-C99B53A6D93B@.microsoft.com...[vbcol=seagreen]
> Uri,
> It's possible that it is within the software, but what I'm wondering is -
> what is it about a SQL database restore that would cause the same software
> code to go from not working to working all of a sudden? What changes
> occur
> to the database when a restore is done?
> I realize this is a tough one to answer, I was just hoping that you might
> have some ideas as to why the restore would "fix" things.
> "Uri Dimant" wrote:
|||Uri,
It's not so much that I don't want to "reveal" anything, it's just that I
don't know exactly what the software is doing as it hits the database. It's
a third party software, and they have no idea why just doing a restore works.
So I thought I would pose the question to someone more knowledgable on the
SQL database side.
Somethings that I was thinking might be going on (keep in mind I am in no
way an expect on any of this) - maybe there is a bad sector on the disk, and
doing the restore ends up writing the data necessary for the Year End process
to complete correctly to a different sector of the disk. Or maybe something
like - the restore causes indexes to be updated, or causes page files to be
readjusted or anything like that.
"Uri Dimant" wrote:

> Jeff
> I see that you would not want to "reveal" more info about your business
> requirements , it is ok , however we will no be able to help you much if you
> cannot provide more info
> RESTORE operations does simple thing , it reads all datapages/data from the
> ..BAK file and create a new database assume you want to create a new one
> If you had a proper database backup and then your software dod some mistake
> , you can come back to the previous state of the database to restore it. It
> does not fix anything
>
>
> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> news:15F36608-14B2-456F-9032-C99B53A6D93B@.microsoft.com...
>
>
|||"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:15F36608-14B2-456F-9032-C99B53A6D93B@.microsoft.com...
> Uri,
> It's possible that it is within the software, but what I'm wondering is -
> what is it about a SQL database restore that would cause the same software
> code to go from not working to working all of a sudden? What changes
> occur
> to the database when a restore is done?
> I realize this is a tough one to answer, I was just hoping that you might
> have some ideas as to why the restore would "fix" things.
>
Well a restore will finish rolling forward or backwards any pending
transactions depending on where they were.
It's possible your financial app posts some large transaction and never sees
it complete and hence locks up.
In any event I think you need to take it up with the financial app vendor.
One option though is to run profiler and record what the app is doing and
seeing if there's anything strange going on there.
And are you sure the app is locking up, or simply waiting for a huge
transaction to commit on the SQL side?
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||There are likely other steps you can take before the restore
that can help you track down what the issues are -
have you run DBCC checks when you hit this?
Are you checking for open transactions?
Are you monitoring with profiler? Are you executing sp_who2,
checking sysprocesses, checking wait stats, locking,
blocking, etc?
Those would probably be the first steps anyway and you'd
want to go from there - depending on the results.
-Sue
On Tue, 20 Feb 2007 06:22:50 -0800, Jeff
<Jeff@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Uri,
>It's possible that it is within the software, but what I'm wondering is -
>what is it about a SQL database restore that would cause the same software
>code to go from not working to working all of a sudden? What changes occur
>to the database when a restore is done?
>I realize this is a tough one to answer, I was just hoping that you might
>have some ideas as to why the restore would "fix" things.
>"Uri Dimant" wrote: