DB Stuck on (Loading) After Restore

We are using MS SQL Server 2000.
I am not a proficient SQL user.
I have created many databases based on a set of instructions from a former
consultant. This involves doing a restore from a SQL backup (to disk) of an
existing "shell" database and "restore as" a new database name. When
selecting options for the restore, I select "leave database operational."
Yesterday when I tried to do this, the progress bar went all the way across,
but then an error popped up at the end that said:
The log in this backup set begins at LSN 30200000 blah blah blah which is
too late to apply to the database. An earlier log backup that includes
30020202020 can be restored. RESTORE LOG is terminating abnormally.
In enterprise manager, my new database appears, but it is grayed out with
the word (loading) next to it.
I have googled and some suggestions say to do a RESTORE WITH RECOVERY, but I
have no idea where or how to do this, or even if this is the right thing to
do.
Would someone point me in the right direction?
Thank youmike wrote:
> We are using MS SQL Server 2000.
> I am not a proficient SQL user.
> I have googled and some suggestions say to do a RESTORE WITH RECOVERY, but
I
> have no idea where or how to do this, or even if this is the right thing t
o
> do.
> Would someone point me in the right direction?
> Thank you
>
That RESTORE command would be run in Query Analyzer. The biggest favor
that you can do yourself, if you're going to be working with SQL, is to
wean yourself from the point-and-click mentality and learn how to use
Query Analyzer to manipulate your databases. Everything you do in
Enterprise Manager is converted into T-SQL commands that are then sent
to the database. You can issue the same commands yourself via Query
Analyzer, and you'll have a firm understanding of what's going on "under
the hood".|||mike wrote:
> We are using MS SQL Server 2000.
> I am not a proficient SQL user.
> I have googled and some suggestions say to do a RESTORE WITH RECOVERY, but
I
> have no idea where or how to do this, or even if this is the right thing t
o
> do.
> Would someone point me in the right direction?
> Thank you
>
That RESTORE command would be run in Query Analyzer. The biggest favor
that you can do yourself, if you're going to be working with SQL, is to
wean yourself from the point-and-click mentality and learn how to use
Query Analyzer to manipulate your databases. Everything you do in
Enterprise Manager is converted into T-SQL commands that are then sent
to the database. You can issue the same commands yourself via Query
Analyzer, and you'll have a firm understanding of what's going on "under
the hood".|||mike
drop database test
go
create database test
GO
create table test..test(id int identity)
insert test..test default values
backup database test to disk = 'd:\db.bak' WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak'WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak' WITH NOINIT
GO
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, recovery
GO
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, norecovery
GO
"mike" <mike@.commmcasssttt.com> wrote in message
news:12a5vvi5kq0l1a6@.corp.supernews.com...
> We are using MS SQL Server 2000.
> I am not a proficient SQL user.
> I have created many databases based on a set of instructions from a former
> consultant. This involves doing a restore from a SQL backup (to disk) of
> an existing "shell" database and "restore as" a new database name. When
> selecting options for the restore, I select "leave database operational."
> Yesterday when I tried to do this, the progress bar went all the way
> across, but then an error popped up at the end that said:
> The log in this backup set begins at LSN 30200000 blah blah blah which is
> too late to apply to the database. An earlier log backup that includes
> 30020202020 can be restored. RESTORE LOG is terminating abnormally.
> In enterprise manager, my new database appears, but it is grayed out with
> the word (loading) next to it.
> I have googled and some suggestions say to do a RESTORE WITH RECOVERY, but
> I have no idea where or how to do this, or even if this is the right thing
> to do.
> Would someone point me in the right direction?
> Thank you
>|||mike
drop database test
go
create database test
GO
create table test..test(id int identity)
insert test..test default values
backup database test to disk = 'd:\db.bak' WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak'WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak' WITH NOINIT
GO
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, recovery
GO
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, norecovery
GO
"mike" <mike@.commmcasssttt.com> wrote in message
news:12a5vvi5kq0l1a6@.corp.supernews.com...
> We are using MS SQL Server 2000.
> I am not a proficient SQL user.
> I have created many databases based on a set of instructions from a former
> consultant. This involves doing a restore from a SQL backup (to disk) of
> an existing "shell" database and "restore as" a new database name. When
> selecting options for the restore, I select "leave database operational."
> Yesterday when I tried to do this, the progress bar went all the way
> across, but then an error popped up at the end that said:
> The log in this backup set begins at LSN 30200000 blah blah blah which is
> too late to apply to the database. An earlier log backup that includes
> 30020202020 can be restored. RESTORE LOG is terminating abnormally.
> In enterprise manager, my new database appears, but it is grayed out with
> the word (loading) next to it.
> I have googled and some suggestions say to do a RESTORE WITH RECOVERY, but
> I have no idea where or how to do this, or even if this is the right thing
> to do.
> Would someone point me in the right direction?
> Thank you
>|||Thank you both|||Thank you both

DB Stuck on (Loading) After Restore

We are using MS SQL Server 2000.
I am not a proficient SQL user.
I have created many databases based on a set of instructions from a former
consultant. This involves doing a restore from a SQL backup (to disk) of an
existing "shell" database and "restore as" a new database name. When
selecting options for the restore, I select "leave database operational."
Yesterday when I tried to do this, the progress bar went all the way across,
but then an error popped up at the end that said:
The log in this backup set begins at LSN 30200000 blah blah blah which is
too late to apply to the database. An earlier log backup that includes
30020202020 can be restored. RESTORE LOG is terminating abnormally.
In enterprise manager, my new database appears, but it is grayed out with
the word (loading) next to it.
I have googled and some suggestions say to do a RESTORE WITH RECOVERY, but I
have no idea where or how to do this, or even if this is the right thing to
do.
Would someone point me in the right direction?
Thank youmike wrote:
> We are using MS SQL Server 2000.
> I am not a proficient SQL user.
> I have googled and some suggestions say to do a RESTORE WITH RECOVERY, but I
> have no idea where or how to do this, or even if this is the right thing to
> do.
> Would someone point me in the right direction?
> Thank you
>
That RESTORE command would be run in Query Analyzer. The biggest favor
that you can do yourself, if you're going to be working with SQL, is to
wean yourself from the point-and-click mentality and learn how to use
Query Analyzer to manipulate your databases. Everything you do in
Enterprise Manager is converted into T-SQL commands that are then sent
to the database. You can issue the same commands yourself via Query
Analyzer, and you'll have a firm understanding of what's going on "under
the hood".|||mike
drop database test
go
create database test
GO
create table test..test(id int identity)
insert test..test default values
backup database test to disk = 'd:\db.bak' WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak'WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak' WITH NOINIT
GO
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, recovery
GO
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, norecovery
GO
"mike" <mike@.commmcasssttt.com> wrote in message
news:12a5vvi5kq0l1a6@.corp.supernews.com...
> We are using MS SQL Server 2000.
> I am not a proficient SQL user.
> I have created many databases based on a set of instructions from a former
> consultant. This involves doing a restore from a SQL backup (to disk) of
> an existing "shell" database and "restore as" a new database name. When
> selecting options for the restore, I select "leave database operational."
> Yesterday when I tried to do this, the progress bar went all the way
> across, but then an error popped up at the end that said:
> The log in this backup set begins at LSN 30200000 blah blah blah which is
> too late to apply to the database. An earlier log backup that includes
> 30020202020 can be restored. RESTORE LOG is terminating abnormally.
> In enterprise manager, my new database appears, but it is grayed out with
> the word (loading) next to it.
> I have googled and some suggestions say to do a RESTORE WITH RECOVERY, but
> I have no idea where or how to do this, or even if this is the right thing
> to do.
> Would someone point me in the right direction?
> Thank you
>|||Thank you both

dB is Loading

SQL & Win 2K
I have a dB that I restored and it got hung up at a phase
where it is '(Loading)'. I ran the script below, from a
post of Paul's from a while back, and an error message
was returned that 'Database 'xxx' cannot be opened. It is
in the middle of a restore.;' How can I get rid of this
dB?
alter database [xxx] set read_write with rollback
immediate
go
exec sp_removedbreplication [xxx]
go
use master
go
drop database [xxx]
go
TIA,
Larry...
Larry,
as far as I recall, my other solution was for a database stuck in RO mode.
For your case you could try
restore database xxx with recovery
Alternatively, you could restore the most recent backup with norecovery and
the logs with norecovery, apart from the last one which is with recovery.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I ran the command you suggested and QA returned...
File 'xxx' was only partially restored by a database or
file restore. The entire file must be successfully
restored before applying the log.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Any othere Ideas?
Larry..
|||Larry,
then it looks like your backup file wasn't fully
restored, so try restoring it again using with recovery.
Rgds,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||As always, thanks for your help.

DB IN LOADING STATE?

I had to recover from a point in time with this script...
RESTORE LOG IT_DATABASE FROM
DISK=N'\\sqlserver\E$\SQLSERVER_BACKUPS\IT_DATABAS E.BAK'
WITH RECOVERY,STOPAT='03/02/2005 13:35:00'
Now my DB is in a loading state and don't know how to get
it back operational...Any help is greatly appreciated.
Thanks,
Hi Bruce,
It looks like IT_DATABASE.BAK is not a transactional log backup file. You
need to use log backup file for point in time recovery.
Try issuing
RESTORE DATABASE YourDatabase
WITH RECOVERY
......to recover from the loading state. Let us know, if the problem
persists.
Thanks
Yogish

DB IN LOADING STATE?

I had to recover from a point in time with this script...
RESTORE LOG IT_DATABASE FROM
DISK=N'\\sqlserver\E$\SQLSERVER_BACKUPS\IT_DATABASE.BAK'
WITH RECOVERY,STOPAT='03/02/2005 13:35:00'
Now my DB is in a loading state and don't know how to get
it back operational...Any help is greatly appreciated.
Thanks,Hi Bruce,
It looks like IT_DATABASE.BAK is not a transactional log backup file. You
need to use log backup file for point in time recovery.
Try issuing
RESTORE DATABASE YourDatabase
WITH RECOVERY
.....to recover from the loading state. Let us know, if the problem
persists.
--
Thanks
Yogish|||Try closing down the process running it then try again.
If that doesn't work detach then attach the database it
worked for me anyway ;)
Peter
"It's true hard work never killed anybody, but I figure,
why take the chance?"
Ronald Reagan
>--Original Message--
>I had to recover from a point in time with this script...
>RESTORE LOG IT_DATABASE FROM
>DISK=N'\\sqlserver\E$\SQLSERVER_BACKUPS\IT_DATABASE.BAK'
>WITH RECOVERY,STOPAT='03/02/2005 13:35:00'
>
>Now my DB is in a loading state and don't know how to get
>it back operational...Any help is greatly appreciated.
>Thanks,
>
>.
>

DB IN LOADING STATE?

I had to recover from a point in time with this script...
RESTORE LOG IT_DATABASE FROM
DISK=N'\\sqlserver\E$\SQLSERVER_BACKUPS\
IT_DATABASE.BAK'
WITH RECOVERY,STOPAT='03/02/2005 13:35:00'
Now my DB is in a loading state and don't know how to get
it back operational...Any help is greatly appreciated.
Thanks,Hi Bruce,
It looks like IT_DATABASE.BAK is not a transactional log backup file. You
need to use log backup file for point in time recovery.
Try issuing
RESTORE DATABASE YourDatabase
WITH RECOVERY
.....to recover from the loading state. Let us know, if the problem
persists.
Thanks
Yogish

DB in Loading State

There was a restore script executed against a test
database that failed and left the database in loading
state. I fixed the restore script and executed it again.
I need confirmation if this is alright. I noticed the
MDF and LDF files date changed to the current date.
This looks ok as now restore works. Whenever the restore operation fails the
database is shown in loading state.
"J Lee" wrote:

> There was a restore script executed against a test
> database that failed and left the database in loading
> state. I fixed the restore script and executed it again.
> I need confirmation if this is alright. I noticed the
> MDF and LDF files date changed to the current date.
>

DB in Loading State

There was a restore script executed against a test
database that failed and left the database in loading
state. I fixed the restore script and executed it again.
I need confirmation if this is alright. I noticed the
MDF and LDF files date changed to the current date.This looks ok as now restore works. Whenever the restore operation fails the
database is shown in loading state.
"J Lee" wrote:

> There was a restore script executed against a test
> database that failed and left the database in loading
> state. I fixed the restore script and executed it again.
> I need confirmation if this is alright. I noticed the
> MDF and LDF files date changed to the current date.
>

DB in Loading State

There was a restore script executed against a test
database that failed and left the database in loading
state. I fixed the restore script and executed it again.
I need confirmation if this is alright. I noticed the
MDF and LDF files date changed to the current date.This looks ok as now restore works. Whenever the restore operation fails the
database is shown in loading state.
"J Lee" wrote:
> There was a restore script executed against a test
> database that failed and left the database in loading
> state. I fixed the restore script and executed it again.
> I need confirmation if this is alright. I noticed the
> MDF and LDF files date changed to the current date.
>

DB in "loading" state - error 22274

We had a situation where we had to restore one of our SQL
databases to the same server the original was running on.
We restored using Arcserve 2000 w/ SQL Agent and it
completed successfully to a different name & directory.
Name of DB was RES_LAW & we had the .mdf & .ldf files
named differently then the originals and in a different
folder.
Arcserve finished the restore successfully with no errors.
When you go into Enterprise Manager the database is there
but shows up GRAY and says "loading" underneath it. When
you double-click to expand properties, you get two errors:
1. error 22274 [SQL-DMO]This Database has been marked
inaccessible (then when you click OK, you get...)
2. Could not get property information for the
database "res_law"
Then it closes.
Does anyone know what is wrong or what we should do?
Computer Associates (maker of Arcserve, said it's not
their problem)
Any help is greatly appreciated.It may just need to be recovered. From Query Analyzer,
execute the following:
RESTORE DATABASE yourDB WITH RECOVERY
>Does anyone know what is wrong or what we should do?
>Computer Associates (maker of Arcserve, said it's not
>their problem)
Sounds familiar and you have my sympathy.
Linchi
>--Original Message--
>We had a situation where we had to restore one of our SQL
>databases to the same server the original was running
on.
>We restored using Arcserve 2000 w/ SQL Agent and it
>completed successfully to a different name & directory.
>Name of DB was RES_LAW & we had the .mdf & .ldf files
>named differently then the originals and in a different
>folder.
>Arcserve finished the restore successfully with no errors.
>When you go into Enterprise Manager the database is there
>but shows up GRAY and says "loading" underneath it. When
>you double-click to expand properties, you get two errors:
>1. error 22274 [SQL-DMO]This Database has been marked
>inaccessible (then when you click OK, you get...)
>2. Could not get property information for the
>database "res_law"
>Then it closes.
>Does anyone know what is wrong or what we should do?
>Computer Associates (maker of Arcserve, said it's not
>their problem)
>Any help is greatly appreciated.
>
>.
>

DB in "loading" state - error 22274

We had a situation where we had to restore one of our SQL
databases to the same server the original was running on.
We restored using Arcserve 2000 w/ SQL Agent and it
completed successfully to a different name & directory.
Name of DB was RES_LAW & we had the .mdf & .ldf files
named differently then the originals and in a different
folder.
Arcserve finished the restore successfully with no errors.
When you go into Enterprise Manager the database is there
but shows up GRAY and says "loading" underneath it. When
you double-click to expand properties, you get two errors:
1. error 22274 [SQL-DMO]This Database has been marked
inaccessible (then when you click OK, you get...)
2. Could not get property information for the
database "res_law"
Then it closes.
Does anyone know what is wrong or what we should do?
Computer Associates (maker of Arcserve, said it's not
their problem)
Any help is greatly appreciated.It may just need to be recovered. From Query Analyzer,
execute the following:
RESTORE DATABASE yourDB WITH RECOVERY

quote:

---

>Does anyone know what is wrong or what we should do?
>Computer Associates (maker of Arcserve, said it's not
>their problem)

---

Sounds familiar and you have my sympathy.
Linchi

quote:

---

>--Original Message--
>We had a situation where we had to restore one of our SQL
>databases to the same server the original was running

---

on.

quote:

---

>We restored using Arcserve 2000 w/ SQL Agent and it
>completed successfully to a different name & directory.
>Name of DB was RES_LAW & we had the .mdf & .ldf files
>named differently then the originals and in a different
>folder.
>Arcserve finished the restore successfully with no errors.
>When you go into Enterprise Manager the database is there
>but shows up GRAY and says "loading" underneath it. When
>you double-click to expand properties, you get two errors:
>1. error 22274 [SQL-DMO]This Database has been marked
>inaccessible (then when you click OK, you get...)
>2. Could not get property information for the
>database "res_law"
>Then it closes.
>Does anyone know what is wrong or what we should do?
>Computer Associates (maker of Arcserve, said it's not
>their problem)
>Any help is greatly appreciated.
>
>.
>

---