Help! Databases are in "Loading" state after Restore from tape!

I run MS SQL Server 2000 (8.00.194) on a Windows 2000 Server.

After having problems with extremely large logfiles, I was told to stop the SQL Service, and delete the logs, and when I restarted the SQL Service the Server would recreate the log files. That did not happend, and the databases had "Suspect" state. After I've tried a few things found on this forum among others, I ended up restoring the db's from my last tape backup.

This was successfully (with Veritas Backup Exec), but now the databases have "Loading" state. I've searched the forums, but haven't found a solution, please help!

Thank you in advance!In Query Analyzer, issue the following command:

restore database yourdatabase with recovery

Substitute your database's name with "yourdatabase".

That should clear up the loading state. I trust you have resolved to never again take advice from the guy that told you to delete the log files?|||Originally posted by MCrowley
In Query Analyzer, issue the following command:

restore database yourdatabase with recovery

Substitute your database's name with "yourdatabase".

That should clear up the loading state. I trust you have resolved to never again take advice from the guy that told you to delete the log files?

Thanks for your answer. I tried it, and got this msg back:

Server: Msg 4331, Level 16, State 1, Line 1
The database cannot be recovered because the files have been restored to inconsistent points in time.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

:(

Yes, I will not delete them again, but I seriously need to get these databases up and running.|||As you can see, I have full backup of the databases on tape.

So If anyone could give me info on how to do a restore correctly, that will also solve my problem.

Thanks, again.|||here is a link to an article at Veritas that may help you out:

http://seer.support.veritas.com/docs/258895.htm

do you only have the backups done through the Veritas agent? or do you also have files from a SQL Server initiated backup (like from a maintenance plan)? If you have backup files you can restore those to disk and then restore through SQL Server.

It might not help now, but you might want to consider scheduling a backup to disk (either standalone or through a maintenance plan) in addition to your agent backups. I personally don't trust backups that go straight to tape, they're good to have as additional insurance, but I wouldn't rely on them alone.

If you are running 8.00.194 (RTM) you might also want to look into getting that server up to SP3a or at least to a point where you're covered against the various vulnerabilities like slammer, etc.. that is of course if your application will run correctly|||Originally posted by drew
here is a link to an article at Veritas that may help you out:

http://seer.support.veritas.com/docs/258895.htm

do you only have the backups done through the Veritas agent? or do you also have files from a SQL Server initiated backup (like from a maintenance plan)? If you have backup files you can restore those to disk and then restore through SQL Server.

It might not help now, but you might want to consider scheduling a backup to disk (either standalone or through a maintenance plan) in addition to your agent backups. I personally don't trust backups that go straight to tape, they're good to have as additional insurance, but I wouldn't rely on them alone.

If you are running 8.00.194 (RTM) you might also want to look into getting that server up to SP3a or at least to a point where you're covered against the various vulnerabilities like slammer, etc.. that is of course if your application will run correctly

Thanks for your answer, I'll look into what the article describes. I've understand that this backup isn't good enought, sp it will be changed as soon as I have my data up and running again. About the upgrade, it has not been done due to compatibility problems. Hopefully it will soon be patched with latest sp.|||You can try this code to force your database out of the loading state, but because it modifies system tables use it at your own risk:

sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
go
update master..sysdatabases
set status = status - 32
where name = 'YourDatabaseName' and status & 32 > 0
go
sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
go

If this does not work, try dropping and recreating your databases prior to running MCrowley's RESTORE DATABASE suggestion.

blindman|||Seems like the veritas article was what I needed to at least get the first backup database back :D

Thanks a lot everyone...one db to go, hopefully that's goes okay as well.

This forum is awesome. Helping a n00b who started with sql a few hours ago, thanks again!

- Everything is back and up and running from the backup.
Good night ppl!