Backup jobs and restores reporting false success!

Below are the problems I am having with my SQL 7.0
server. The first problem is backup jobs created with
the backup wizard and scheduled with the backup wizard
fail. The latest problem I discovered is my databases
can not be restored. The restore problem is very
critical to solve.
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
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'
I am experiencing the same behavior 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!
>--Original Message--
>been researching a problem I discovered yesterday on the
>SQLSever 7.0 I administer. I discovered Call Heller
>posted a problem very similar to what I am
experiencing.
>
>Every morning I check to see if my SQL backup jobs run.
>No errors are reported. Yesterday, I was ask to restore
a
>database and discovered there was not output file i.e.
>teachercert_db_200308201656.BAK. I viewed job log,
>backup maintenance plan history and event viewer, all
>reported that the output file was created. Now, if I go
>in and manual backup the database directly from the
>database "All Task" option, an output file is created.
>
>Next I discovered all the database maintenance plans I
>viewed on changed yesterday, did not generate an output
>file when the scheduled job ran.
>
>My questions are:
>
>1. Why is the job not generating a output file and
all
>log report the output file was created?
>2. How do I open a case with Microsoft
>
>I'd appreciate any help you can offer.
>
>Kathy Long
>
>.
>It seems that you can create a backup job successfully just have
problems with the wizard.
Do you mean the maintenance exe? I always advise against using that so I
would say stick with simplest solution.
Instead of creating a device use backup dateabase dbname to disk = ...
This will create a backup file for you.
I always include the date and time in the filename to make
administration easier.
Here is an SP that will backup all databases on a server - you can get
some ideas from that:
http://www.nigelrivett.net/BackupAllDatabases.html
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi Kathy,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
About the backup wizard to backup the database, I have provided solution
and you can try the steps I provided and if there is any problem, please
feel free to post any new message in that post and I am pleased and ready
to provide support there.
In this post, I will assist you to solve the prolem of restore your
database in SQL Server 7.0. I will work on it today. But please go to the
old post to check if the backup problem is solved.
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.
From the information you provided, your problem should be:
You want to restore database from a file. This process succeeded in SQL
Server 2000 but failed in SQL Server 7.
Actually, because the information you provide is so limited, I cannot give
an answer or solution this time. I wonder if you could provide the
information below for further analysis:
1) When using the Enterprise Manager to restore the database, what steps
have you taken? Could you give me more detailed information? Because you
just said you restored a database, but it is gone. I just want to the
detailed information of how you carry out this restore process, by T-SQL or
by Wizard? Have you got any message indicate that the restore is successful
or failed, what is these messages? Could you provide the System and
Application logs together with the output of SQLDiag if they are available
to you?
2) What kind of file you are using in this backup process? Is the file you
used the MDF file you mentioned above? If not, is it a SQL Server 2000
backup file or a SQL Server 7 backup file?
The feedback from you will be great helpful in clarifying the problem you
encountered and for us to analysis. I am waiting on your response and ready
to provide further help!
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.|||1. Steps to Enterprise manager is to right click on the
database to restore the backup file to and select the
file I want to use. Then on the Option tab I choice to
restore over the current database and change the path to
E:\Data\Test.mdb. The system clains it restore correctly
but when I attempt to open up the database I receive this
message: Error 911- could not locate entry in
sysdatabase 'Test.' No entry found with that name. Make
sure that the name is entered correctly.
I know the database is there because I still see it in
the file folder.
2. The file is created with SQL Server 7.0. I've used
backup devices and files.
When I do a manual using T-SQL restore to the same
database with the following command
RESTORE DATABASE PBDMI
FROM DISK = 'H:\PBDMI.bak'
I receive the following info in my results pane.
Processed 28968 pages for database 'PBDMI',
file 'PBDMI_Data' on file 1.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]
ConnectionRead (WrapperRead()).
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General
network error. Check your network documentation.
Processed 1 pages for database 'PBDMI', file 'PBDMI_Log'
on file 1.
Connection Broken
The database actually restores because I've deleted a row
in one of the tables and it comes back after the
restore. No luck restoring to a different database.
When I run the code to restore to a different database:
BACKUP DATABASE PBDMI
TO DISK = 'H:\PBDMI.bak'
RESTORE FILELISTONLY
FROM DISK = 'H:\PBDMI.bak'
RESTORE DATABASE PBDMI_New
FROM DISK = 'H:\PBDMI.bak'
WITH MOVE 'PBDMI' TO 'E:\Data\PBDMI_New_Data.mdf',
MOVE 'PBDMI_log' TO 'E:\Data\PBDMI_New_Log.ldf',
REPLACE
GO
Here is the message I receive. I pretty sure our
language is US-English
Changed language setting to us_english.
Processed 28968 pages for database 'PBDMI',
file 'PBDMI_Data' on file 6.
Processed 1 pages for database 'PBDMI', file 'PBDMI_Log'
on file 6.
Backup or restore operation successfully processed 28969
pages in 38.095 seconds (6.229 MB/sec).
LogicalName
PhysicalName
Type
FileGroupName
Size
MaxSize
----
----
-- ---
----
----
----
--- --
-- ----
----
-- -- --
--
PBDMI_Data
e:\data\PBDMI_Data.MDF
D
PRIMARY
240582656 35184372080640
PBDMI_Log
e:\data\PBDMI_Log.LDF
L
NULL
704905216 35184372080640
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 9
File 'PBDMI' is not a database file for
database 'PBDMI_New'.
Server: Msg 3013, Level 16, State 1, Line 9
Backup or restore operation terminating abnormally.
I cannot send any file to your email address.
Thanks for your help.
Kathy
>--Original Message--
>Hi Kathy,
> Thank you for using MSDN Newsgroup! It's my pleasure to
assist you with
>your issue.
> From the information you provided, your problem should
be:
>You want to restore database from a file. This process
succeeded in SQL
>Server 2000 but failed in SQL Server 7.
>
>Actually, because the information you provide is so
limited, I cannot give
>an answer or solution this time. I wonder if you could
provide the
>information below for further analysis:
>1) When using the Enterprise Manager to restore the
database, what steps
>have you taken? Could you give me more detailed
information? Because you
>just said you restored a database, but it is gone. I
just want to the
>detailed information of how you carry out this restore
process, by T-SQL or
>by Wizard? Have you got any message indicate that the
restore is successful
>or failed, what is these messages? Could you provide
the System and
>Application logs together with the output of SQLDiag if
they are available
>to you?
>2) What kind of file you are using in this backup
process? Is the file you
>used the MDF file you mentioned above? If not, is it a
SQL Server 2000
>backup file or a SQL Server 7 backup file?
> The feedback from you will be great helpful in
clarifying the problem you
>encountered and for us to analysis. I am waiting on your
response and ready
>to provide further help!
>
>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.
>
>.
>