RE: SQL 2005 Failing Backups Because it Exits The Query Prematurely

Hello,

I tried to run the exe directly on my test machine, and it seems the odbc
query failed and it returned immediately with the time label. The same
thing happens to SQL 2000.

Since I don't have Delphi to test, I was not able to troubleshoot in the
source code level. Since you are not able to reproduce the issue
consistently, it might be a behavior change in SQL 2005. Please rest
assured I have reported this situation to the proper channel.

If you need further assistance on this issue, I recommend that you open a
Support incident with Microsoft Product Support Services so that a
dedicated Support Professional can assist with this case. If you need any
help in this regard, please let me know.

For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp

Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
From: "ESPNSTI" <ESPNSTI@xxxxxxxxxxxxx>
Subject: SQL 2005 Failing Backups Because it Exits The Query Prematurely
Date: Wed, 1 Mar 2006 12:33:21 -0600
Lines: 122
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1506
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506
Message-ID: <O$6ui6VPGHA.3728@xxxxxxxxxxxxxxxxxxxx>
Newsgroups: microsoft.public.sqlserver.odbc
NNTP-Posting-Host: 12.10.40.130
Path: TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.odbc:44754
X-Tomcat-NG: microsoft.public.sqlserver.odbc

Hi,


PROBLEM:


We've encountered a problem in testing with our application being unable to
perform a backup successfully in SQL 2005.
This same application (which uses ODBC) is able to backup SQL 7.0 and SQL
2000 databases through the same user stored procedure without problems.

What appears to be the problem is that SQL 2005 returns control to the
application that called the stored procedure (usually a fraction of a
second) before the backup actually finishes.
If this happens, when the calling application then immediately closes the
query handle, SQL 2005 does not complete the backup and it'll produce the
following error:

Error: 3041, Severity: 16, State: 1. BACKUP failed to complete the command
BACKUP DATABASE Amazon_New. Check the backup application log for detailed
messages.

If however the application waits for a little bit before closing the query
handle, SQL 2005 completes the backup successfully.
The query is not being called asynchronously by the client app.


ENVIRONMENT:


I've produced this problem the easiest with the calling application running
on the same machine that hosts SQL 2005 (using 127.0.0.1 or (local) to
connect).
I've also reproduced this with the calling application residing on a
different machine than the SQL 2005 server machine (using a LAN
connection).
However I believe I've seen it succeed in this (LAN) scenario, but I'm not
100% certain about this (I may have been stepping through the calling app
with a debugger at the time).

The SQL 2005 host machines that I've tried so far (two of them) have SQL
2005 Developer edition installed, and are Windows XP SP2 machines (MDAC 2.8
SP1).
The client machines I've tried are the SQL 2005 host machines and also a
Windows 2000 Pro SP4 machine with MDAC 2.8 SP1 installed.
I've tried connecting using both the SQL Native Client in ODBC and the SQL
Server ODBC driver (which the application normally uses).


STEPS TO REPRODUCE:

http://espnsti.in2hosting.org/BackupTest/BackupTest.zip
http://espnsti.in2hosting.org/BackupTest/BackupTestSource.zip
http://espnsti.in2hosting.org/BackupTest/dbo.usp_Backup.sql

I've created and attached a small test app (written in Delphi 5.0) that
will
back up the Northwind DB (or any other DB).
To set up this app do the following:

1) If you do not have the Northwind DB, get a copy and restore it or
identify another DB to use.
2) Modify the attached dbo.usp_Backup.sql script to use the correct DB and
backup file path and name if needed.
Run dbo.usp_Backup.sql against Norhtwind (or the DB that will be used.)
3) Set up a new System DSN in the ODBC Data Source Admin control panel.
Name it NorthWind, and be sure to set it to change the default DB to
Northwind.
4) Extract the attached BackupTest.zip to some directory.

To use the app do the following:


A) To see it fail:

1) Enter the appropriate Data Source Name (and User ID and Password if
needed).
2) Leave the "Wait 5 seconds" check box unchecked, and click on Backup.
3) You should see an error in the sql error log (and the event viewer)
stating that the backup failed, and the backup file should not be there.


B) To check that sql returns from the query before the backup is complete:
(this only works accurately if the app and the SQL Server are on
the
same machine)

1) Enter the appropriate Data Source Name (and User ID and Password if
needed).
2) Check the "Wait 5 seconds" check box, and click on Backup.
3) You should see the backup file appear and stay in the backup
directory.
4) In the event viewer application log, you should see three new
informational entries:

4.1) One from "BackupTest" that ends with "SQL Northwind Backup Control
Returned To App: " followed by a time.
4.2) Then a standard MSSQLSERVER message with details of the database
backup.
4.3) Finally there is a custom MSSQLSERVER message from the usp_Backup
stored procedure that contains "SQL Northwind Backup Finished:" followed by
a time.

If you compare the time in the message from the 1st and last
messages, you'll see that control was returned to the app before the backup
finished.
(Even though there probably is only a fraction of a second
difference.)


QUESTIONS:

1) Can anyone reproduce this behavior?
2) Has anyone run in to a similar problem (perhaps with something other
than
a BACKUP sql statement)?
3) Am I correct in thinking that this is a bug in SQL 2005 or was this a
conscious change?
4) Is there perhaps a configuration setting (in ODBC / SQL / etc...) that
now needs to be used in SQL 2005?

Thanks,
Erik





.

Relevant Pages

  • Re: Backup erros
    ... You may have a SQL ... Error 800423f4 appears in the backup log file when you back up a volume by ... > Reason: The process cannot access the file because it is being used by> another process. ...
    (microsoft.public.windows.server.sbs)
  • Re: My query is empty. How do I reestabish relationships to add d
    ... Following is the SQL and a little other information on the 2 queries ... Following the Query is some information on the Customners ... Backup, backup, backup! ... consider posting the SQL statement of the query that isn't ...
    (microsoft.public.access.queries)
  • Re: SBS2003, NTBackup-Fehler
    ... Windows Server 2003 to resolve some VSS snapshot issues ... Backup fails on a computer that is running Small Business Server 2003 ... Die Sicherung kann nicht fortgesetzt werden. ... Microsoft OLE DB Provider for SQL Server ...
    (microsoft.public.de.german.backoffice.smallbiz)
  • Re: hi, need help
    ... manual hand-jam on the SQL backend through a discovered parameter. ... restore from that backup you took last week. ... ask the provider for a backup (if you dont have a backup ...
    (Security-Basics)
  • Re: How to Restore a single document from Sharepoint 2003
    ... Rob, I'd suggest you query the docs table, the DirNameand LeafNamecan help get the document. ... That can help you locate the document in your backup database, and then try to insert that manually in the current Sharepoint DB. ... I just want to restore one file out of the doc table from the SQL ...
    (microsoft.public.sharepoint.portalserver)