Database in LOAD after restore

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Database in LOAD after restore

Author

Topic

PeterRay
Starting Member

3 Posts

Posted - 2004-07-20 : 17:25:43

I have a problem restoring a database in my server:
i made a mistake restoring a backup (of a database "A") over a database (i call it database "B"), so during restore process i stopped procedure with "cancel" button.
But with this action the database "B" seems ruined.. No problem, i deleted it because i have a backup.
Restore of "B" backup completed successfully but, after completion of restore the database appears marked "in load".
I beginned to think about the causes of this, so i made some tests:
-Restore of other backups is done without problems in same server;
-Restore of "B" backup is done correctly in other servers;
-Restore of "B" backup with same name or another db name (or with a different physical name fails (the db is always in load state));

I saw this forum (there was a similar article at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=37088) and i tried to run this sp to clean msdb tables:

sp_delete_backuphistory [ @oldest_date = ] 'oldest_date'

But no way.. the situation is not solved.
Tables seems all cleaned in MSDB but error occurs..
I tried also to see Microsoft KB but also there i have no solution.
Infact they suggest to restore the db and then to solve the situation to run this query:

RESTORE DATABASE DatabaseName WITH RECOVERY

But i think that my problem is another (also because in another server the backup is restored without any problem): probably some system tables (in msdb or in master) remained dirty and i don't know exactly how to solve this situation.
If anyone can help me, i would be grateful.
Thank you in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-20 : 17:31:35

Try this:

RESTORE DATABASE DBName
FROM DISK = 'E:\MSSQL\BACKUP\DBName.BAK'
WITH REPLACE, RECOVERY

Tara

PeterRay
Starting Member

3 Posts

Posted - 2004-07-21 : 15:52:00

Thanks a lot Tara.
I successfully restored the database with this query, but the problem is not definitively solved, infact when i try to restore the same backup (with a different database name) from Enterprise Manager i still have the problem.
And unfortunately there's another problem: scheduled backups of all databases are not done.
All suggestion are wellcome...
Peter.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-21 : 16:32:11

From Enterprise it fails, but not from Query Analyzer? If so, then you are not selecting the correct options in Enterprise Manager.

Scheduled backups of all databases are not done...Can you give more information on this? Are you using maintenance plans? What happens when you schedule a BACKUP command? Does it work? Have a look at my database routines that are a great alternative to the maintenance plan wizards. See the comments as well.

http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Tara

PeterRay
Starting Member

3 Posts

Posted - 2004-07-25 : 18:19:03

I made a lot of tests but finally i found the origin of my problem, but not yet the cause:
i disabled "Named Pipes" protocol (it was selected as primary protocol in SQL Server Client network utility), now there's only TCP/IP and all works (backup from Q.A., Enterprise Manager and scheduled backups in manteinance plans).
But i don't really know what's happening with Named Pipes, it always worked with it...
With Named Pipes as primary protocol, running a backup command from Query Analizer i have this error:

Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionRead (WrapperRead()).
Server: message 11, level 16, state 1, row 0
General network error. Check your network documentation.
But the backup seems done (follows a message about pages of db elaborated).

During these test days i found a case similar as mine at:

http://www.google.it/search?q=cache:wJQGmlsZNuYJ:dbforums.com/arch/70/2003/11/960479+Microsoft%5D%5BODBC+SQL+Server+Driver%5D%5BShared+Memory%5DConnectionRead+(WrapperRead()).+Backup&hl=it&start=3

As you see in the final part of the page probably the problem is caused by some register keys (but which?) revised with some unexpect reasons (events or application), but is only my idea.

Bye
Peter.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources