DSO in SQL2005: restored

Showing posts with label restored. Show all posts

Thursday, March 29, 2012

Dropping Loading/Suspect database

Hi:
I restored a database from the backup. It is showing Loading/Suspect in
enterprise manager. How do I drop this database and try again. What could
have caused this? Please let me know.
ThanksSohail,
Have you tried the DROP DATABASE statement from Query Analyzer? Is this a
test box, if so and the former doesn't work, you might try KILLing the
process performing the RESTORE (if available).
HTH
Jerry
"Sohail" <Sohail@.discussions.microsoft.com> wrote in message
news:97BDB53E-F7DF-434E-B66F-E9C28B4BC024@.microsoft.com...
> Hi:
> I restored a database from the backup. It is showing Loading/Suspect in
> enterprise manager. How do I drop this database and try again. What
> could
> have caused this? Please let me know.
> Thanks|||Actually I do not have access to the clients machine. Will the mode field in
the following query tell me the spid.
SELECT mode, status, status2
FROM master..sysdatabases
WHERE name = 'XYZ'
Thanks
"Jerry Spivey" wrote:
> Sohail,
> Have you tried the DROP DATABASE statement from Query Analyzer? Is this a
> test box, if so and the former doesn't work, you might try KILLing the
> process performing the RESTORE (if available).
> HTH
> Jerry
> "Sohail" <Sohail@.discussions.microsoft.com> wrote in message
> news:97BDB53E-F7DF-434E-B66F-E9C28B4BC024@.microsoft.com...
> > Hi:
> >
> > I restored a database from the backup. It is showing Loading/Suspect in
> > enterprise manager. How do I drop this database and try again. What
> > could
> > have caused this? Please let me know.
> >
> > Thanks
>
>|||Hi,
SPID column in sysprocess gives the processID. use the below query to get
the peocess id.
SELECT SPID,cmd FROM master..sysdatabases WHERE name = 'XYZ'
After getting the SPID use the KILL statement to kill the old restore
process
KILL SPID
Thanks
Hari
SQL Server MVP
"Sohail" <Sohail@.discussions.microsoft.com> wrote in message
news:7DA75FB6-9B36-48CB-8744-64DD60BFD7D9@.microsoft.com...
> Actually I do not have access to the clients machine. Will the mode field
> in
> the following query tell me the spid.
> SELECT mode, status, status2
> FROM master..sysdatabases
> WHERE name = 'XYZ'
> Thanks
> "Jerry Spivey" wrote:
>> Sohail,
>> Have you tried the DROP DATABASE statement from Query Analyzer? Is this
>> a
>> test box, if so and the former doesn't work, you might try KILLing the
>> process performing the RESTORE (if available).
>> HTH
>> Jerry
>> "Sohail" <Sohail@.discussions.microsoft.com> wrote in message
>> news:97BDB53E-F7DF-434E-B66F-E9C28B4BC024@.microsoft.com...
>> > Hi:
>> >
>> > I restored a database from the backup. It is showing Loading/Suspect
>> > in
>> > enterprise manager. How do I drop this database and try again. What
>> > could
>> > have caused this? Please let me know.
>> >
>> > Thanks
>>|||Hi Hari:
SPID and CMD fields do not exist in master..sysdatabases table. Is this the
right table?
"Hari Prasad" wrote:
> Hi,
> SPID column in sysprocess gives the processID. use the below query to get
> the peocess id.
> SELECT SPID,cmd FROM master..sysdatabases WHERE name = 'XYZ'
> After getting the SPID use the KILL statement to kill the old restore
> process
> KILL SPID
> Thanks
> Hari
> SQL Server MVP
>
> "Sohail" <Sohail@.discussions.microsoft.com> wrote in message
> news:7DA75FB6-9B36-48CB-8744-64DD60BFD7D9@.microsoft.com...
> > Actually I do not have access to the clients machine. Will the mode field
> > in
> > the following query tell me the spid.
> >
> > SELECT mode, status, status2
> > FROM master..sysdatabases
> > WHERE name = 'XYZ'
> >
> > Thanks
> >
> > "Jerry Spivey" wrote:
> >
> >> Sohail,
> >>
> >> Have you tried the DROP DATABASE statement from Query Analyzer? Is this
> >> a
> >> test box, if so and the former doesn't work, you might try KILLing the
> >> process performing the RESTORE (if available).
> >>
> >> HTH
> >>
> >> Jerry
> >> "Sohail" <Sohail@.discussions.microsoft.com> wrote in message
> >> news:97BDB53E-F7DF-434E-B66F-E9C28B4BC024@.microsoft.com...
> >> > Hi:
> >> >
> >> > I restored a database from the backup. It is showing Loading/Suspect
> >> > in
> >> > enterprise manager. How do I drop this database and try again. What
> >> > could
> >> > have caused this? Please let me know.
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>

Dropping Loading/Suspect database

Friday, March 9, 2012

Drop Database

Hi all,

I try to restore a DB on MS SQL 2000 but something was wrog because restored DB compare on tree view of SQL Enterprise Manager commented with "(Loading/Suspect)".

I can't access to DB and I cant delete it. I obtain this : "Error 3724: Cannot drop the database 'XXXX' bacause it is being used for replication.

How can I delete or solve this problem?

Thank's a lot

bye

HID

Hi hidme...

To get the database out of the loading state, try the following statement:

restore database <dbname> with recovery

Assuming that brings your database up into an accessible state, then try dropping any publications/subscriptions/etc. that are included on the database, or try running the following as a last-ditch resort (only if you plan on destroying the database anyhow hopefully):

exec sp_removedbreplication @.dbname = '<dbname>'

Then you should be able to drop the database, repost if you continue to have problems,

HTH,

|||

Well,

thank's for your rapid suggest and sorry for may long break.

So the problem persist because I can't access to DB for restore..., what do you think if I try to shutdown DBMRS and I delete file_data.mdf ? Do you think a link on tree of SQL Manager go away?

Thanks

hid

|||

Hi hid...when you say you can't access the DB for restore, are you saying that you are trying to switch to that database to perform the restore? If so, you should be able to run the restore statement above from the master database against the database you are having trouble with.

If that's not what you are saying, could you provide a little clarification for me?

Also, don't delete the mdf/ldf files, the database in the tree will not disappear, it will simply show up as corrupt...

|||

I think he is trying to restore using the GUI by right clicking on the database.

hidme, don't use the GUI interface. Write the code into a query window and execute it against the master database.

Drop Database

Hi all,

I try to restore a DB on MS SQL 2000 but something was wrog because restored DB compare on tree view of SQL Enterprise Manager commented with "(Loading/Suspect)".

I can't access to DB and I cant delete it. I obtain this : "Error 3724: Cannot drop the database 'XXXX' bacause it is being used for replication.

How can I delete or solve this problem?

Thank's a lot

bye

HID

Hi hidme...

To get the database out of the loading state, try the following statement:

restore database <dbname> with recovery

exec sp_removedbreplication @.dbname = '<dbname>'

Then you should be able to drop the database, repost if you continue to have problems,

HTH,

|||

Well,

thank's for your rapid suggest and sorry for may long break.

So the problem persist because I can't access to DB for restore..., what do you think if I try to shutdown DBMRS and I delete file_data.mdf ? Do you think a link on tree of SQL Manager go away?

Thanks

hid

|||

If that's not what you are saying, could you provide a little clarification for me?

Also, don't delete the mdf/ldf files, the database in the tree will not disappear, it will simply show up as corrupt...

|||

I think he is trying to restore using the GUI by right clicking on the database.

hidme, don't use the GUI interface. Write the code into a query window and execute it against the master database.

Thursday, March 29, 2012

Dropping Loading/Suspect database

Dropping Loading/Suspect database

Dropping Loading/Suspect database

Friday, March 9, 2012

Drop Database

Drop Database

DSO in SQL2005

Blog Archive

About Me