Backup reporting false success

I am experiencing the same behavior as below (see snip)on
my SQL Server 7.0
running on NT (lastest service packs and patched on
both.) I too have witnessed the BAK file created and
then
deleted. I do not have the maintance plan configured to
deleting old .BAK files. I do receive this error message
when I create a backup from the "backup" option (not the
database maintenance plan.) This is the message I get:
10 percent backed up. [SQLSTATE 01000] (Message 3211) 20
percent backed up. [SQLSTATE 01000] (Message 3211)
ConnectionRead (WrapperRead()). [SQLSTATE 01000] (Message
258) General network error. Check your network
documentation. [SQLSTATE 08S01] (Error 11) 30 percent
backed up. [SQLSTATE 01000] (Message 3211). The step
failed.
NOTE: I am backing up to the server harddrive(NO NETWORK
INVOLVED!!!)with 20 gigs free. This database is only
199mb in size. I suspect the "general network error" is
bogus.
Please help!
snip from other person with same issue
>>I have a server with Windows 2000 SP4+hotfixes, SQL
>>Server
>>2000 SE SP3A+hotfix. The server has 4 user databases >>
(the
largest being around 500 megs). 3 of the databases
backup
>>fine but 1 database will not take a full backup or a
transaction log backup from the maintenance plan. The
job
runs and succeeds but the file is not where it should be
(nor anywhere else).
The development server with the same configuration does
not have this same problem, and it takes about 7 seconds
to backup the database in question.
I have monitored the directory that the file should be
created in while the job is running, and the file does
create and stays there for the 5 to 7 seconds that the
job
is running and then it dissappears.
I have also used File Monitor from sysinternals and
monitored the system while a backup is being run and
sqlservr.exe and sqlmaint.exe are the only things that
touch the .BAK file. Sqlservr.exe reads and writes to
the .BAK file numerous times and the last thing that
happens is sqlservr.exe opens the .BAK file, then Deletes
the .BAK file, and then closes the .BAK file. No other
applications besides sqlservr.exe and sqlmaint.exe touch
the .BAK file. The index numbers in File Monitor do not
skip any numbers.
Here are the lines from sysinternals File Monitor where
the backup is deleted:
1646 6:32:56 PM sqlservr.exe:3688 OPEN
E:\Microsoft SQL
Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
SUCCESS Options: Open Access: All
1647 6:32:56 PM sqlservr.exe:3688 DELETE
E:\Microsoft SQL
Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
SUCCESS
1648 6:32:56 PM sqlservr.exe:3688 CLOSE
E:\Microsoft SQL
Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
SUCCESS
The error message in the errorlog is:
BACKUP failed to complete the command BACKUP LOG [t_prod]
TO DISK = N'E:\Microsoft SQL
Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK'
WITH INIT , NOUNLOAD , NOSKIP , STATS = 10 , NOFORMAT
Disk space is not an issue as there is over 60 gigs free
and all the databases together barely total a 1 gig.
Backups from a script that run through a job (without the
maint plan wizard) succeed about 75% of the time but
about
25% of the time the job fails with the same errors as the
maint plan fails with. I'm still trying to get File
Monitor running at the time a backup from a script fails,
but haven't been luckly enough to catch it at the right
time yet.
Any information or suggestions to resolve this problem
are
greatly appreaciated as I've been trying to figure this
out for too long. If there is any other information that
would be useful to post, please let me know.
Thanks,
Trent.
.Just a thought... Do you have disk compression turned on?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Kathy" <rnurse@.educ.state.ak.us> wrote in message news:799c01c3b458$0ebdd730$a601280a@.phx.gbl...
> I am experiencing the same behavior as below (see snip)on
> my SQL Server 7.0
> running on NT (lastest service packs and patched on
> both.) I too have witnessed the BAK file created and
> then
> deleted. I do not have the maintance plan configured to
> deleting old .BAK files. I do receive this error message
> when I create a backup from the "backup" option (not the
> database maintenance plan.) This is the message I get:
> 10 percent backed up. [SQLSTATE 01000] (Message 3211) 20
> percent backed up. [SQLSTATE 01000] (Message 3211)
> ConnectionRead (WrapperRead()). [SQLSTATE 01000] (Message
> 258) General network error. Check your network
> documentation. [SQLSTATE 08S01] (Error 11) 30 percent
> backed up. [SQLSTATE 01000] (Message 3211). The step
> failed.
> NOTE: I am backing up to the server harddrive(NO NETWORK
> INVOLVED!!!)with 20 gigs free. This database is only
> 199mb in size. I suspect the "general network error" is
> bogus.
> Please help!
> snip from other person with same issue
> >>I have a server with Windows 2000 SP4+hotfixes, SQL
> >>Server
> >>2000 SE SP3A+hotfix. The server has 4 user databases >>
> (the
> largest being around 500 megs). 3 of the databases
> backup
> >>fine but 1 database will not take a full backup or a
> transaction log backup from the maintenance plan. The
> job
> runs and succeeds but the file is not where it should be
> (nor anywhere else).
> The development server with the same configuration does
> not have this same problem, and it takes about 7 seconds
> to backup the database in question.
> I have monitored the directory that the file should be
> created in while the job is running, and the file does
> create and stays there for the 5 to 7 seconds that the
> job
> is running and then it dissappears.
> I have also used File Monitor from sysinternals and
> monitored the system while a backup is being run and
> sqlservr.exe and sqlmaint.exe are the only things that
> touch the .BAK file. Sqlservr.exe reads and writes to
> the .BAK file numerous times and the last thing that
> happens is sqlservr.exe opens the .BAK file, then Deletes
> the .BAK file, and then closes the .BAK file. No other
> applications besides sqlservr.exe and sqlmaint.exe touch
> the .BAK file. The index numbers in File Monitor do not
> skip any numbers.
> Here are the lines from sysinternals File Monitor where
> the backup is deleted:
> 1646 6:32:56 PM sqlservr.exe:3688 OPEN
> E:\Microsoft SQL
> Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
> SUCCESS Options: Open Access: All
> 1647 6:32:56 PM sqlservr.exe:3688 DELETE
> E:\Microsoft SQL
> Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
> SUCCESS
> 1648 6:32:56 PM sqlservr.exe:3688 CLOSE
> E:\Microsoft SQL
> Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
> SUCCESS
>
> The error message in the errorlog is:
> BACKUP failed to complete the command BACKUP LOG [t_prod]
> TO DISK = N'E:\Microsoft SQL
> Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK'
> WITH INIT , NOUNLOAD , NOSKIP , STATS = 10 , NOFORMAT
> Disk space is not an issue as there is over 60 gigs free
> and all the databases together barely total a 1 gig.
> Backups from a script that run through a job (without the
> maint plan wizard) succeed about 75% of the time but
> about
> 25% of the time the job fails with the same errors as the
> maint plan fails with. I'm still trying to get File
> Monitor running at the time a backup from a script fails,
> but haven't been luckly enough to catch it at the right
> time yet.
> Any information or suggestions to resolve this problem
> are
> greatly appreaciated as I've been trying to figure this
> out for too long. If there is any other information that
> would be useful to post, please let me know.
> Thanks,
> Trent.
> .
>|||Hi Kathy,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
I should give some explanation to the phenomenon you described in the snip.
The backup process in the SQL Server is an automatic process, which means
when backup failed to complete, all the backup process will rollback. This
is why the customer you snipped from used File Monitor and found that "does
create and stays there for the 5 to 7 seconds that the job is running and
then it disappears." As he mentioned later "Backups from a script that run
through a job (without the maintenance plan wizard) succeed about 75% of
the time but about 25% of the time the job fails with the same errors as
the maintenance plan fails with".
For the error message you mentioned, I need more information for analysis.
First, is this error message encountered for all the databases you have, or
just one database when using "Backup" option?
Second, besides using the "backup" option, do you meet the same error
message when using T-SQL or job for back up? You can try the T-SQL code
like this:
use master
exec sp_addumpdevice 'disk','northwindback', 'c:\northwind_back.bak'
backup database northwind to northwindback
go
restore database northwind from northwindback
go
If you do not encounter this error, then please provide information for
every step when using "backup" option to check if anywhere in this process
might involve the network device
If you meet the error message again, please collect information from
SQLDIAG, NT application event logs, system event logs and dbcc checkdb out
put and post here for our further analysis.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thank you for responding!!! :-)
The first point you made really made sense to me. I knew
transactions rolled back but never thought about a backup
job rolling back. I did as you suggested. I created a
backup device and backup a database (and restored it)
using T-SQL code. Of course this worked, because it is
not a job scheduled using the backup wizard interface. I
can always manually backup a database by right clicking,
choicing "All Task" and "Backup Database." But as soon
as I ran it as a scheduled job it will failed, or SQL
would reported it as suceeding with no BAK file.
So, I decided to create a backup job manually. It
worked!!! Then I scheduled it through the job interfaced
and it ran as scheduled.
To clarify this to you:
Backup Job FAILS when I do the following steps:
Create a backup device, i.e. otg_backupdevice
Right click on the new backup device (otg_backupdevice)
and select "Backup a Database"
The SQL Server backup wizard window appears, select the
database (otg) to backup, add the backup device
(otg_backupdevice) and schedule the backup to occur once
a day at 12:00 A.M.
Then ran the job from the jobs window
The job fails.
Backup Job Succeeds when I do the following steps:
Create a backup device, i.e. otg_backupdevice
Go to the job window
Right-click and select new job
Name the job - OTG backup to device
Click on the "Steps" tab and click on "New Step"
Select OTG as the database
In the command window I enter "backup database otg to
otg_backupdevice
Click on the "Schedule" tab and schedule it to backup at
10:00 am
The job suceeds
I suspect there is a problem with the backup wizard'
Thank you for your help. I still wonder why the backups
will not work through maintenance plans or through the
backup wizard.
Kathy
>--Original Message--
>Hi Kathy,
>Thank you for using MSDN Newsgroup! It's my pleasure to
assist you with
>your issue.
>I should give some explanation to the phenomenon you
described in the snip.
>The backup process in the SQL Server is an automatic
process, which means
>when backup failed to complete, all the backup process
will rollback. This
>is why the customer you snipped from used File Monitor
and found that "does
>create and stays there for the 5 to 7 seconds that the
job is running and
>then it disappears." As he mentioned later "Backups from
a script that run
>through a job (without the maintenance plan wizard)
succeed about 75% of
>the time but about 25% of the time the job fails with
the same errors as
>the maintenance plan fails with".
>For the error message you mentioned, I need more
information for analysis.
>First, is this error message encountered for all the
databases you have, or
>just one database when using "Backup" option?
>Second, besides using the "backup" option, do you meet
the same error
>message when using T-SQL or job for back up? You can try
the T-SQL code
>like this:
>use master
>exec
sp_addumpdevice 'disk','northwindback', 'c:\northwind_back
.bak'
>backup database northwind to northwindback
>go
>restore database northwind from northwindback
>go
>If you do not encounter this error, then please provide
information for
>every step when using "backup" option to check if
anywhere in this process
>might involve the network device
>If you meet the error message again, please collect
information from
>SQLDIAG, NT application event logs, system event logs
and dbcc checkdb out
>put and post here for our further analysis.
>
>Best regards
>Baisong Wei
>Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only. Thanks.
>.
>|||I tested restoring a database and the system reports the
database is restore sucessfully but it does not. The
sysdatabase does not show an entry for the restored
database. When I close Enterprise Manager and reopen the
database is gone. The mdf file is still in the data
directory. What is wrong with my sql server.
I can restore the database from the same file on my SQL
2000 server but it fail on every one of the SQL 7.0
servers. The production 7.0 SQL server and the 7.0 SQL
server on my laptop computer.
Kathy
>--Original Message--
>Hi Kathy,
>Thank you for using MSDN Newsgroup! It's my pleasure to
assist you with
>your issue.
>I should give some explanation to the phenomenon you
described in the snip.
>The backup process in the SQL Server is an automatic
process, which means
>when backup failed to complete, all the backup process
will rollback. This
>is why the customer you snipped from used File Monitor
and found that "does
>create and stays there for the 5 to 7 seconds that the
job is running and
>then it disappears." As he mentioned later "Backups from
a script that run
>through a job (without the maintenance plan wizard)
succeed about 75% of
>the time but about 25% of the time the job fails with
the same errors as
>the maintenance plan fails with".
>For the error message you mentioned, I need more
information for analysis.
>First, is this error message encountered for all the
databases you have, or
>just one database when using "Backup" option?
>Second, besides using the "backup" option, do you meet
the same error
>message when using T-SQL or job for back up? You can try
the T-SQL code
>like this:
>use master
>exec
sp_addumpdevice 'disk','northwindback', 'c:\northwind_back
.bak'
>backup database northwind to northwindback
>go
>restore database northwind from northwindback
>go
>If you do not encounter this error, then please provide
information for
>every step when using "backup" option to check if
anywhere in this process
>might involve the network device
>If you meet the error message again, please collect
information from
>SQLDIAG, NT application event logs, system event logs
and dbcc checkdb out
>put and post here for our further analysis.
>
>Best regards
>Baisong Wei
>Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only. Thanks.
>.
>|||Hi Kathy,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
Thank you for your detailed information and it is helpful for us to
analysis.
As you said, when execute the TSQL and JOB schedule, the backup process
will complete fine while when using backup option, it failed, right? If I
misunderstood the information you provided, please feel free to let me know.
As using the "backup" option, because it add many options when carried out
the backup, so please carry out it step by step as I mentioned below:
1) Right clicking, choose "All Task" and "Backup Database."
2) This is very important step! Make sure in the destination, there should
be only one device (When you add the device, there could be some other
devices in it), remove other devices from the destination. If not, you will
encounter the errors.
3) Click "OK" after you assigned the schedule time (You can also run it
immediately by click "ok" to test if this will work first)
Try the above steps and pay attention to the second one, only one
destination could be appeared in the "destination".
I hope this will solve the problem and if there is any problem, feel free
to post message here and I am ready to help! By the way, happy
thanks-giving day to you and your family!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.