473,888 Members | 1,599 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

restore db stuck loading

Hi

I am trying to copy a 20 GB database from server 1 to 2. I took a full
backup, copied to server2 and restored ( ready for use) using EM.

After the message restore completed, I see the database's status as LOADING.
We are on Sql Server 2000 (8.00.818) on Windows 2000 Adv Servers.

I ran dbcc on the source DB and it returned no errors. I tried the command
'restore db with recovery ' which didnot help. I get an message saying that
the data file is only partially restored.. It seems the last resort is to
change the status in system tables.

Any ideas?

Thanks
Ragu


Jul 23 '05 #1
13 23467
(ra*****@sbcglo bal.net) writes:
I am trying to copy a 20 GB database from server 1 to 2. I took a full
backup, copied to server2 and restored ( ready for use) using EM.

After the message restore completed, I see the database's status as
LOADING. We are on Sql Server 2000 (8.00.818) on Windows 2000 Adv
Servers.

I ran dbcc on the source DB and it returned no errors. I tried the
command 'restore db with recovery ' which didnot help. I get an message
saying that the data file is only partially restored.. It seems the last
resort is to change the status in system tables.


What does sp_helpdb say about the database?

If you run the RESTORE command from Query Analyzer, do you get any
interesting messages?

I think that what may have happened is that the database was restored
in such a way, that SQL Server now anticiapates one or more transaction
logs to be applied.

An example command to restore a database from QA:

RESTORE DATABASE db FROM DISK = 'C:\temp\whatev er.dmp' WITH
MOVE 'datafile' TO 'C:\datadir\db. mdf',
MOVE 'logilfe' TO 'D:\logdir\db.l df',
REPLACE, STATS
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
Hi Thanks for the reply. sp_helpdb returns 0 rows. The same sql restore
using QA returns this error

"Connection Read (WrapperRead()) . [SQLSTATE 01000] (Message 258)
General network error. Check your network documentation. [SQLSTATE
08S01] (Error 11) 10 percent restored. [SQLSTATE 01000] (Error 3211).
The step failed. "

But it restored fine once but when I tried again I got the above error.
The reasons could be 1. some timing limit ( Since I restore on the
server this should not be an issue.) 2. I see antivirus software trying
to scan the newly created data file.

Thanks for your time

Ragu

Jul 23 '05 #3
Ragu (ra*****@gmail. com) writes:
Hi Thanks for the reply. sp_helpdb returns 0 rows.
Then the database is not there.
The same sql restore using QA returns this error

"Connection Read (WrapperRead()) . [SQLSTATE 01000] (Message 258)
General network error. Check your network documentation. [SQLSTATE
08S01] (Error 11) 10 percent restored. [SQLSTATE 01000] (Error 3211).
The step failed. "
QA does not say "The step failed", does it? But, OK, I assume that it does
say "General network error" and all that jazz. That indicates that a tragic
accident occurred on the SQL Server side, and SQL Server had to terminate
the process. If you look in SQL Server's error log you are likely to find
a stack dump related to the accident. No, don't expect this stack dump
to make you that much wiser. But look there anyway.
But it restored fine once but when I tried again I got the above error.
The reasons could be 1. some timing limit ( Since I restore on the
server this should not be an issue.) 2. I see antivirus software trying
to scan the newly created data file.


I would certainly recommend preventing the antivirus stuff from sneaking
in.

What is funny is that you say that the backup loaded OK once. Had it never
loaded well I would suspect an accident in transport over the network. But,
hm, if I re-read your original message, not even then did the load complete
entirely successfully?

I would turn off the antivirus thing and try again. If it fails again,
I would try copying again. If I could find away to avoid the network,
I would try that. 20 GB is too much for a DVD, but a removable disk
that you connect over USB2 could be an alternative.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4
HI Erland :

1. I will try to turn off the virus and try again.
2. I ran the query in QA on the server console using VPN and Remote
Connection and one thing or other always gets disconnected. So I put
the restore sql in a job and executed the job instead. Hence the words
"step failed"
3. But the error is the same in QA also.
4. Sometimes even thoug we get the same error in QA, DB after 3 hrs and
several "Bypassing recovery for database 'DB' because it is marked IN
LOAD." and one " Recovery is checkpointing database " messages, the DB
gets restored fine. The only message in the error log is Database "DB'
restore completed.
5. And in other environements with fatser servers ,we donot get this
error at all with the same backup file.

Thanks

Ragu

Jul 23 '05 #5
Ragu (ra*****@gmail. com) writes:
1. I will try to turn off the virus and try again.
If you have a virus on the machine, you should definitely turn if off! :-)
2. I ran the query in QA on the server console using VPN and Remote
Connection and one thing or other always gets disconnected. So I put
the restore sql in a job and executed the job instead. Hence the words
"step failed"
I see.

If you run QA on the same machine as the server, and you get disconnected
that is likely to be a crash and not a network issue.
4. Sometimes even thoug we get the same error in QA, DB after 3 hrs and
several "Bypassing recovery for database 'DB' because it is marked IN
LOAD." and one " Recovery is checkpointing database " messages, the DB
gets restored fine. The only message in the error log is Database "DB'
restore completed.


Three hours to restore a 20 GB database? That appears to be a tad long
to me.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6
1. I meant virus scan not virus per se.
2. Normally it takes 15 -20 minues on production servers.
3. Reg getting disconnedt while on the server, there was no crash. I
think that the error message is misleading.
Thanks for your responses.

ragu

Jul 23 '05 #7
Another update. In the follwoing scenerio the restore was succesful.

1. created the db say DB1 on the server.

2, Using QA, run the sql

RESTORE DATABASE DB1 FROM DISK = 'c:\db_backup\D B.bak' with
move 'DB_Data' to 'c:\SQL_DATA\DB _RC4_Data.MDF',
move 'DB_Log' to 'c:\SQL_DATA\DB _RC4_Log.LDF',
REPLACE, STATS

3. After 15 min , got the error [Microsoft][ODBC SQL Server
Driver][Named Pipes]ConnectionRead (WrapperRead()) .
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
10 percent restored.
Connection Broken

4. The db looks fine. The tables,row count match. The db is healthy.

5. The error logs show s the same messages like starting up
datasbase,Bypas sing recovery for database Db1because it is marked IN
LOAD ( 2 times ), Recovery is checkpointing database ,Database
restored: over a period of 37 minutes. ( We got error in QA after 15
minutes )

Ragu
Though the QA gave the error, the db restored fine.

Jul 23 '05 #8
Ragu (ra*****@gmail. com) writes:
1. I meant virus scan not virus per se.


I understand that. But it looked funny! :-)

I will have to look into your other post tonight. It looks very strange
to me.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #9
Ragu (ra*****@gmail. com) writes:
3. After 15 min , got the error [Microsoft][ODBC SQL Server
Driver][Named Pipes]ConnectionRead (WrapperRead()) .
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
10 percent restored.
Connection Broken


One thing is funny here. Did you really run the BACKUP command on a QA
which is on the same box as SQL Server? In such case I would not
expect Named Pipes in the error message.

The conclusion I am prepared to make, is that the network is flaky,
but SQL Server does not notice that client is gone and jogs along
with the restore. The recovery messages you get are funny, though.

If you really run QA on the server box, open Client Network Utility,
and check that you have share memory enabled (this is a check box
in the lower left corner).


--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
1579
by: lreames | last post by:
I am running SQL 2000 SP2 on a Win 2000 box. I attempted to restore a dB a couple of times and the restore failed, but the dB appears in EM twice, once with Loading and the other with Read-Only. I stopped SQL and attempted to delete the LDF & MDF files, but there was a sharing violation. How can I detach these dBs from the SQL server?? Detach is not an option, because the 'OK' button is not clickable. HELP anyone.
1
5200
by: csomberg | last post by:
SQL 2000 SP3 My database is still showing Loading .... after doing a restore and the restore showing it to be successfull. I have restarted the service as well. Help !
2
2003
by: si.downes | last post by:
Using SQL Server 2000 SP3 I'm developing a data warehouse where data will be archived off to a filegroup, this filegroup backed up and the tables in this filegroup truncated to free up space on the server. So using Enterprise Manager I've copied data to the filegroup tables, backed up the file group, truncated the tables, but when I have restored the file group the database is stuck in a state of '(loading...)' and I can't get it to...
13
7306
by: Andy K | last post by:
Hi , I'm wondering what's going on with a restore I'm trying to do . My DB2 version is a 7.2 on a Linux RH ES 2.1 . I've copy a recent backup that was on NAS for a test on my machine . I then restore the backup without creating a database with the simple command line "db2 restore backup DBNAME".
2
1635
by: technocrat | last post by:
I am trying to load from a cursor which selects 124 million records...but while loading it completes the build phase and it stuck ...I think...at the delete phase(which in turn looks like doing the relational Integrity thing) according my DBA at around 4%...Is there a way to speed this up and try to get it done sooner...This has been running for almost 2 hrs now and seems no near to completion... Any suggestions are welcome...
2
5590
by: maha | last post by:
Dear DB2 experts! I'm stuck with the following issue: from a customer I got a DB2 backup file, created on an AIX-32 machine that I cannot restore on my windows platform. I admit that I posses only limited knowledge on the administration side of DB2, but I have done a lot of research, without solving the problem. What I tried: For a regular RESTORE I would need a linux Big Endian machine
1
2592
by: NewbieDB2DBA | last post by:
I'm running DB2 UDB v8.2 on Windows 2003 SP2. How do I get a tablespace out of Roll Forward in Progress state?! I've tried everything I can think of, but I'm extremely new to DB2 (more experience w/MS SQL 2000) and am finding that DB2 doesn't seem to be as user friendly as MS SQL (or is that just me?!). I figure this is progress considering the whole database was messed up earlier, and I was able to do a restore/rollforward/etc., and...
0
1078
by: Udi | last post by:
Hi All, WinForms app that uses DexExpress DockManager with ~15 panels. Lately I've added a new panel that contains a user control with bunch of panels and group boxes in it. I believe that starting from that point I'm encountering a new problem in my application which happens every once in a while: When my app is minimized or goes out of focus, sometimes I can't restore it. It stuck, the main window is not repainted, I can't click the...
0
2884
JamieHowarth0
by: JamieHowarth0 | last post by:
Hi guys, Up until 3 weeks ago I was running a Microsoft Windows Server 2003 R2 Enterprise Edition Beta from home on an ADSL connection with dynamic IP absolutely fine - until the beta decided to expire. I have since upgraded to a valid licensed copy but because the "upgrade" option wasn't given to me I had to do a clean install - so format and re-install my hard disk. Fortunately, I was clever and stored all my vitals - mailbox database,...
0
9800
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11178
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10777
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10438
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5817
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6014
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4642
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4243
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3251
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.