SQL Server Performance Forum – Threads Archive

DB restore error – loading for too long

I’m trying to restore a database from a BAK file (2GB) – there is enough space on the drive but after successfully restoring, the database is grey with a staus of (Loading)! Does anyone know how to prevent this or rectify it?? Many Thanks.[?]

---

Post the code used to restore the database and if you use WITH NORECOVERY clause at the end it will be in LOADING state only. You should use WITH RECOVERY if you do not have any transaction logs to restore, refer to books online about RESTORE topic for more information. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Hi I’m using the Enterprise Manager. Where do I find the code?

---

Can I remove the Loading status easily?

---

Code I’m using now is:
RESTORE DATABASE sundb
FROM DISK = ‘p:MDFilesSQL80DatasundbFUL.Bak’
WITH RECOVERY But I’m now getting a ‘db in use’ message – and I’ve tried the normal things and it isn’t used by anyone.

---

THis is what I use.<br /><br />Step 1.<br />rp_kill_db_processes ‘x’ <br /><br /><br />Step 2.<br /><br />RESTORE DATABASE x<br /> FROM disk=’G:x.bak'<br /> WITH RECOVERY,<br /> MOVE ‘x_data’ TO ‘G:dbx_data.mdf’, <br /> MOVE ‘x_log’ TO ‘G:dbx_log.ldf'<br /><br /><br />___________________________________________________<br /><br />Here is procedure for killing processes:<br />_)__________________________________________________<br /><br /><br />CREATE proc rp_kill_db_processes<br />(@dbname varchar(20))<br />as<br /><br />Declare @dbid int,<br /> @spid int,<br /> @str nvarchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />select @dbid = dbid from master..sysdatabases<br /> where name = @dbname<br />declare spidcurs cursor for<br /> select spid from master..sysprocesses where dbid = @dbid<br />open spidcurs<br />fetch next from spidcurs into @spid<br />While @@fetch_status = 0<br /> Begin<br /> Select @str = ‘Kill ‘+convert(nvarchar(30),@spid)<br /> exec(@str)<br /> –print @str<br /> fetch next from spidcurs into @spid<br />End<br />Deallocate spidcurs<br /><br /><br />GO<br />

---

Thanks – [:0]all sorted

---

You shoud have used
USE MASTER
RESTORE DATABASE sundb
FROM DISK = ‘p:MDFilesSQL80DatasundbFUL.Bak’
WITH RECOVERY Madhivanan Failing to plan is Planning to fail

---

By all means keep in touch with Books Online that is installed alongwith SQL server on your machine for such statements and information. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Getting the exact same issue ! For 2 years, my SQL Server 2000 was working fine. Suddenly, past 2 months, databases (no pattern, just happens randomly) after a Forced restore hang up in "Loading" state. We do get the success message "Restore Complete". BUT, the windows application log does not log events for which the "Loading" issue occurs. We have tried SQL jobs with a kill connection step, dropped the whole database and restore fresh. Most of the times,we do this manually, instead of SQL Job. Sometimes, after dropping the whole database and restroring afresh the restore works fine. Sometimes, bouncing the SQL Server helps. But NO permanent solution so far. Can this be an issue with master db ? May be after restore the master db is not able to recognize the same. Any suggestions guys?
Thanks for your time.
Thanks,
Anand.K
[email protected] "What would you attempt to do if you knew that you could never fail"
-Robert Schuller

---

Anand
Please do not duplicate the question, refer tohttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=12122 post for your resolution. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

]]>