SQL 2k restore - db stuck on "loading"

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)

SQL 2k restore - db stuck on "loading"

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2003-09-08 : 16:10:09

I was sent a zipped up web app to install, which included a sql backup file for the back end.

After a bit of research on the web, I found these commands, and stuck in the proper arguments.

RESTORE DATABASE foo FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\ foo' WITH FILE = 1, MOVE ' foo_dat' TO

'c:\foo.mdf', MOVE ' foo_log' TO 'c:\foo_log.ldf', NORECOVERY

Which resulted in:

Processed 688 pages for database 'foo', file 'foo_dat' on file 1.

Processed 1 pages for database ' foo', file ' foo_log' on file 1.

RESTORE DATABASE successfully processed 689 pages in 0.587 seconds (9.603 MB/sec).

However, all I get in em under “databases” is “foo (Loading)”. I’ve waited several minutes and restarted the sql service as well.

Any ideas where I went wrong?

restore verifyonly from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL\foo' results in:

The backup set is valid.

Thanks for any help,

Matthew

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-08 : 16:55:19

Wht are you using NORECOVERY?

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-08 : 16:59:18

And since it's only 9 mb it should be fast anyway...is there a reason you want to not have any transactions roll back?

btw, because of NORECOVERY, it will be left in that state...

take NORECOVERY off your statement..unless you need it, then leave it

You'll probably then need to do an alter database

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

chedderslam
Posting Yak Master

223 Posts

Posted - 2003-09-08 : 17:02:19

Well, I'm just a web guy, and this is the first time that I have done a sql restore.

I searcherd sqlteam.com for the errror I was getting and tried to follow the instructions on this page:

http://dbforums.com/arch/69/2002/8/476716

So take off the no recovery? I'll give it a try; thanks to you both.

chedderslam
Posting Yak Master

223 Posts

Posted - 2003-09-08 : 17:04:47

OK, worked a treat. Super thanks!

Why was the no recovery causing the problem?

Also, what "alter statement" were you referring to, X002548?

Thanks again!

Matthew

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-08 : 17:28:05

NORECOVERY mode means that you want to apply additional transaction logs. RECOVERY mode is the default option, so you don't have to specify it. In your situation, you did not have additional transaction logs to apply, so you want RECOVERY mode.

Not sure what ALTER statement Brett was referring to either, but you might need to run sp_change_users_login after the RESTORE if the user accounts are orphaned (if any accounts exist in the database).

Tara

chedderslam
Posting Yak Master

223 Posts

Posted - 2003-09-08 : 17:41:44

Thanks, Tara.

Yes there are three accounts in the db. Also, I am unable to log into the db with an asp connect. I have even tried using the sa account to no avail. Any help?

I'm a bit out of my element as I usually do coldfusion; asp always seems to mess me up a bit.

MM_VPOMS_STRING = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=foo;Data Source=(local);pwd=xxxxxxxx"

Set dbcon = Server.CreateObject("ADODB.Connection")

dbcon.open MM_VPOMS_STRING

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-08 : 17:44:04

Well, do not ever use the sa account in an application. When SQL Server was installed, was mixed-authentication selected? If not, then no SQL accounts will get you in. You can only use Windows authentication in this mode. To check which option was done, you'll need to use an account that has local administrator rights on the SQL Server. With that account go to the server properties in Enterprise Manager, then to Security tab. Which authentication mode is selected?

Tara

chedderslam
Posting Yak Master

223 Posts

Posted - 2003-09-08 : 17:48:38

Tara,

Yes, I am familiar with mixed mode, and that is what the server is set up for.

I understand that you shouldn't use sa for a web app. This is just my local box, and after failing with several different accounts, I figured I would try the sa. I am a bit weak with accounts and their set up.

Thanks to everyone for the quick replies and extreme patience.

Cheers,

Matthew

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-09 : 12:13:11

Glad you got it to work...

Tara...if you use NORECOVERY and have nothing left to apply, can't you use ALTER to get the database out of loading status?

don't know...(never had to)

As far as logins....I would suggest you make your development match as closely to prod as you can...develop code using the id that will be used in prod..it'll save you a lot of headaches...

MOO

Also use Roles to assign permissions...not individual ids...and since it's a web app are you going to be connecting with 1 id and then have application level security?

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-09 : 12:19:44

I do not believe that you can use ALTER to get the database out of NORECOVERY mode, you have to specify RECOVERY mode in the RESTORE command. Since Matthew just did a RESTORE DATABASE command, then he needs to rerun it with RECOVERY option specified.

If you have used NORECOVERY with RESTORE DATABASE and then RESTORE LOG, you can re-run the RESTORE LOG command but specify RECOVERY option:

RESTORE LOG DBName
FROM DISK = 'F:\MSSQL\Backup\DBName_TRN1.TRN'
WITH NORECOVERY

RESTORE LOG DBName
FROM DISK = 'F:\MSSQL\Backup\DBName_TRN1.TRN'
WITH RECOVERY

Tara

chedderslam
Posting Yak Master

223 Posts

Posted - 2003-09-09 : 12:35:04

Thanks, guys, lots of good info. Very much appreciate the learning experience.

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-09 : 14:15:39

quote:
Originally posted by chedderslam

Thanks, guys, lots of good info. Very much appreciate the learning experience.

Nice doing business with you...btw...great login name...

any meaning behind it? Born in Wisconsin?

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

chedderslam
Posting Yak Master

223 Posts

Posted - 2003-09-09 : 14:22:56

Haha, no meaning behind it. Just a play on the Denny's breakfasts, I guess(which i rarely if ever eat). More just a silly play on words, and I don't usually find it taken when registering for a site.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources