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.
Thanks
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
|||Actually I do not have access to the clients machine. Will the mode field in
the following query tell me the spid.
SELECTmode, 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,
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...[vbcol=seagreen]
> 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:
|||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...
>
>
sql

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

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 i
n
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,
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...[vbcol=seagreen]
> 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:
>|||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...
>
>

Dropped Table

Help!! I dropped two tables, what a stupid!!.
However, I have a Complete Backup from my
database "mydata" created at june, 24th 18:21 and after
dropped those tables I create a new complete backup that
was yesterday june 25th 18:55
I restored my backup of 24th on another Sql_server
(hopefully I was installing a new server to change this
one I am using now) but I can't restore the transaction
log of the 25th backup because It says something like, I
need another transaction log, but I don't have it!!.
So I have my data until 24th 18:21 but I need to recover
all the data!!
Please HELP ME!!!!
You should have made a transaction log backup on the 25:th instead of a database backup.
In SQL Server, you can restore a database backup and then a number of transaction log backups. You
cannot skip over a log backup, as all log records need to be applied one after the other. This seems
to be your situation, that you are missing a log backup. You can look in the backup history tables
in msdb to see when the log backups were performed and to where. These tables has a rather complex
structure, though. Also, there are some operations that just cut the log without doing a backup.
This will also infer with a log backup restore sequence. I suggest you open a case with MS PSS so
they can help you through this situation as this is very difficult to do over a newsgroup...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Duval" <rduval@.discussions.microsoft.com> wrote in message
news:21abc01c45b2f$da9b2e10$a101280a@.phx.gbl...
> Help!! I dropped two tables, what a stupid!!.
> However, I have a Complete Backup from my
> database "mydata" created at june, 24th 18:21 and after
> dropped those tables I create a new complete backup that
> was yesterday june 25th 18:55
> I restored my backup of 24th on another Sql_server
> (hopefully I was installing a new server to change this
> one I am using now) but I can't restore the transaction
> log of the 25th backup because It says something like, I
> need another transaction log, but I don't have it!!.
> So I have my data until 24th 18:21 but I need to recover
> all the data!!
> Please HELP ME!!!!
sql

Dropped Table

Help!! I dropped two tables, what a stupid!!.
However, I have a Complete Backup from my
database "mydata" created at june, 24th 18:21 and after
dropped those tables I create a new complete backup that
was yesterday june 25th 18:55
I restored my backup of 24th on another Sql_server
(hopefully I was installing a new server to change this
one I am using now) but I can't restore the transaction
log of the 25th backup because It says something like, I
need another transaction log, but I don't have it!!.
So I have my data until 24th 18:21 but I need to recover
all the data!!
Please HELP ME!!!!You should have made a transaction log backup on the 25:th instead of a database backup.
In SQL Server, you can restore a database backup and then a number of transaction log backups. You
cannot skip over a log backup, as all log records need to be applied one after the other. This seems
to be your situation, that you are missing a log backup. You can look in the backup history tables
in msdb to see when the log backups were performed and to where. These tables has a rather complex
structure, though. Also, there are some operations that just cut the log without doing a backup.
This will also infer with a log backup restore sequence. I suggest you open a case with MS PSS so
they can help you through this situation as this is very difficult to do over a newsgroup...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Duval" <rduval@.discussions.microsoft.com> wrote in message
news:21abc01c45b2f$da9b2e10$a101280a@.phx.gbl...
> Help!! I dropped two tables, what a stupid!!.
> However, I have a Complete Backup from my
> database "mydata" created at june, 24th 18:21 and after
> dropped those tables I create a new complete backup that
> was yesterday june 25th 18:55
> I restored my backup of 24th on another Sql_server
> (hopefully I was installing a new server to change this
> one I am using now) but I can't restore the transaction
> log of the 25th backup because It says something like, I
> need another transaction log, but I don't have it!!.
> So I have my data until 24th 18:21 but I need to recover
> all the data!!
> Please HELP ME!!!!

Dropped Table

Help!! I dropped two tables, what a stupid!!.
However, I have a Complete Backup from my
database "mydata" created at june, 24th 18:21 and after
dropped those tables I create a new complete backup that
was yesterday june 25th 18:55
I restored my backup of 24th on another Sql_server
(hopefully I was installing a new server to change this
one I am using now) but I can't restore the transaction
log of the 25th backup because It says something like, I
need another transaction log, but I don't have it!!.
So I have my data until 24th 18:21 but I need to recover
all the data!!
Please HELP ME!!!!You should have made a transaction log backup on the 25:th instead of a data
base backup.
In SQL Server, you can restore a database backup and then a number of transa
ction log backups. You
cannot skip over a log backup, as all log records need to be applied one aft
er the other. This seems
to be your situation, that you are missing a log backup. You can look in the
backup history tables
in msdb to see when the log backups were performed and to where. These table
s has a rather complex
structure, though. Also, there are some operations that just cut the log wit
hout doing a backup.
This will also infer with a log backup restore sequence. I suggest you open
a case with MS PSS so
they can help you through this situation as this is very difficult to do ove
r a newsgroup...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Duval" <rduval@.discussions.microsoft.com> wrote in message
news:21abc01c45b2f$da9b2e10$a101280a@.phx
.gbl...
> Help!! I dropped two tables, what a stupid!!.
> However, I have a Complete Backup from my
> database "mydata" created at june, 24th 18:21 and after
> dropped those tables I create a new complete backup that
> was yesterday june 25th 18:55
> I restored my backup of 24th on another Sql_server
> (hopefully I was installing a new server to change this
> one I am using now) but I can't restore the transaction
> log of the 25th backup because It says something like, I
> need another transaction log, but I don't have it!!.
> So I have my data until 24th 18:21 but I need to recover
> all the data!!
> Please HELP ME!!!!

Droping suspect publication database

Hi.
I have a publication databse that become suspect. It is a test database, and
I don't have backup.
I'm trying drop this, but I can't, SQL Server return a error message: Cannot
drop the database 'teste_replica' because it is being used for replication.
I tried reset suspect status with sp_resetstatus but it doesn't worked.
Any suggestion?
Tanks.
sio Nunes
If you have tried sp_resetstatus, I would just like to know whether you
STOPPED and RE-STARTED services of SQL thereafter?
I hope this helps !
Reetesh B. Chhatpar
ShawMan Software Enterprises
www.shawmansoftware.com
Diamond is just another piece of coal that did well under pressure.
"Esio Nunes" <esio_nunes@.hotmail.com> wrote in message
news:Or%23B$gV0EHA.3120@.TK2MSFTNGP12.phx.gbl...
> Hi.
> I have a publication databse that become suspect. It is a test database,
and
> I don't have backup.
> I'm trying drop this, but I can't, SQL Server return a error message:
Cannot
> drop the database 'teste_replica' because it is being used for
replication.
> I tried reset suspect status with sp_resetstatus but it doesn't worked.
> Any suggestion?
> Tanks.
> sio Nunes
>
|||what happens when you issue a
sp_replicationdboption 'PublicationDatabase','publish', 'false'
or
sp_replicationdboption 'PublicationDatabase','merge publish', 'false'
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Reetesh B. Chhatpar" <reetesh@.widesystems.com> wrote in message
news:exURfqV0EHA.3596@.TK2MSFTNGP12.phx.gbl...
> If you have tried sp_resetstatus, I would just like to know whether you
> STOPPED and RE-STARTED services of SQL thereafter?
> I hope this helps !
> Reetesh B. Chhatpar
> ShawMan Software Enterprises
> www.shawmansoftware.com
> Diamond is just another piece of coal that did well under pressure.
>
> "Esio Nunes" <esio_nunes@.hotmail.com> wrote in message
> news:Or%23B$gV0EHA.3120@.TK2MSFTNGP12.phx.gbl...
> and
> Cannot
> replication.
>
|||I can't acess this database, since it is suspect.
Error messeage with sp_replicationdboption:
Server: Msg 945, Level 14, State 2, Line 183
Database 'TESTE_REPLICA' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for details.
sio
"Hilary Cotter" <hilary.cotter@.gmail.com> escreveu na mensagem
news:uHh30zV0EHA.416@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> what happens when you issue a
> sp_replicationdboption 'PublicationDatabase','publish', 'false'
> or
> sp_replicationdboption 'PublicationDatabase','merge publish', 'false'
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "Reetesh B. Chhatpar" <reetesh@.widesystems.com> wrote in message
> news:exURfqV0EHA.3596@.TK2MSFTNGP12.phx.gbl...
database,
>
|||I restarted the server, but it doesn't work.
sio
"Reetesh B. Chhatpar" <reetesh@.widesystems.com> escreveu na mensagem
news:exURfqV0EHA.3596@.TK2MSFTNGP12.phx.gbl...
> If you have tried sp_resetstatus, I would just like to know whether you
> STOPPED and RE-STARTED services of SQL thereafter?
> I hope this helps !
> Reetesh B. Chhatpar
> ShawMan Software Enterprises
> www.shawmansoftware.com
> Diamond is just another piece of coal that did well under pressure.
>
> "Esio Nunes" <esio_nunes@.hotmail.com> wrote in message
> news:Or%23B$gV0EHA.3120@.TK2MSFTNGP12.phx.gbl...
> and
> Cannot
> replication.
>
|||I've run into this problem.
I think the best way to get around it is to stop sql server and then
manually move the related database files to another location.
Then restart SQL Server and delete the rows from the sysdatabases. Then
recreate the database, and restore from backup.
I would advise you to open a support incident with Microsoft PSS on this one
for getting a supported way to fix this problem.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Esio Nunes" <esio_nunes@.hotmail.com> wrote in message
news:OuZP66V0EHA.1860@.TK2MSFTNGP15.phx.gbl...
>I can't acess this database, since it is suspect.
> Error messeage with sp_replicationdboption:
> Server: Msg 945, Level 14, State 2, Line 183
> Database 'TESTE_REPLICA' cannot be opened due to inaccessible files or
> insufficient memory or disk space. See the SQL Server errorlog for
> details.
>
> sio
> "Hilary Cotter" <hilary.cotter@.gmail.com> escreveu na mensagem
> news:uHh30zV0EHA.416@.TK2MSFTNGP10.phx.gbl...
> database,
>
|||1. Put the db into EMERGENCY BYPASS STATUS (32768)
-- so you can rebuild the log
sp_configure 'allow', 1
go
reconfigure with override
update sysdatabases
set status = -32768
where name='yourDBname'
2. Stop sql server - rename log file (You will delete
it later)
3. Start sql server. Rebuild the log.
dbcc rebuild_log
('yourDBname', 'TheDriveForLogFiles:\PathToLogFiles\logfilename.l df')
3. Put db back from EMERGENCY BYPASS STATUS (32768) to
status 16
update sysdatabases
set status = 16 -- or 24
where 'yourDBname'
4. Clean up
sp_configure 'allow', 0
go
reconfigure with override
Now you have solved the most probable cause of your 'Suspect’ status: a
corrupted log file. After this you have a clean log file and SQL will be able
to start the DB not heaving to roll back or forward corrupted transactions.
Then you can run DBCC CheckDB to see if the data file is OK or you can drop
the database as you wish.
|||I tryied this but the problem is with the data file, not log file.
sio
"Adrian Mos" <AdrianMos@.discussions.microsoft.com> escreveu na mensagem
news:3EF556B1-B7A2-4CC8-B461-7093574C68F3@.microsoft.com...
> 1. Put the db into EMERGENCY BYPASS STATUS (32768)
> -- so you can rebuild the log
> sp_configure 'allow', 1
> go
> reconfigure with override
> update sysdatabases
> set status = -32768
> where name='yourDBname'
> 2. Stop sql server - rename log file (You will delete
> it later)
> 3. Start sql server. Rebuild the log.
> dbcc rebuild_log
> ('yourDBname', 'TheDriveForLogFiles:\PathToLogFiles\logfilename.l df')
> 3. Put db back from EMERGENCY BYPASS STATUS (32768) to
> status 16
> update sysdatabases
> set status = 16 -- or 24
> where 'yourDBname'
> 4. Clean up
> sp_configure 'allow', 0
> go
> reconfigure with override
>
> Now you have solved the most probable cause of your 'Suspect' status: a
> corrupted log file. After this you have a clean log file and SQL will be
able
> to start the DB not heaving to roll back or forward corrupted
transactions.
> Then you can run DBCC CheckDB to see if the data file is OK or you can
drop
> the database as you wish.

DROP TABLE BY MISTAKE

I have just dropped a table in Sql Server By mistake
Is there a way to tr retieve this without pulling up an old backup?
TIANone that I can think of.
Your backup is probably the best bet.
"Grant Merwitz" <grant@.magicalia.com> wrote in message
news:O7EE91v1EHA.3408@.tk2msftngp13.phx.gbl...
> I have just dropped a table in Sql Server By mistake
> Is there a way to tr retieve this without pulling up an old backup?
> TIA
>|||http://www.aspfaq.com/2449
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Grant Merwitz" <grant@.magicalia.com> wrote in message
news:O7EE91v1EHA.3408@.tk2msftngp13.phx.gbl...
> I have just dropped a table in Sql Server By mistake
> Is there a way to tr retieve this without pulling up an old backup?
> TIA
>

drop replication

Here was my problem.
I have a database with replication. All I need to do was restore the
database from a backup (totally different data from another server).
The restore process was messed up because I was not aware there is
replication in place. So the database status is always loading. I have
to use sp_removedb and then restore the db.
But the publication is still there (with a push subscription). Can not
do anything with Enterprise manager either. So I did a
sp_dropReplication but I later noticed that I have to do
sp_dropsubscription first.
Now I am stuck:
I can not drop the subscription (complaining error 20026 can not find
publication)
and I can not create publication either (compaling there is already a
subscriptin)
Anyone just tell how to get rid of this and I can start over.
Thanks
John
Hey
If u hav droped the publication already dont wory forget it
and continue.
The other way is in the publication DB u hav some tables
which keep tracks of the subcription info just clear that
rows also. or just drop the subscription DB also
And other thing what u hav done is u hav droped the DB
If the DB is droped then automatically publication is deleted
and related information tables are also deleted which holds
the subscription information
I really dont know what is stoping u to continue to restore DB
from the backup and dont get panic and start the
restore process
-Cheers - Prax
Q. John Chen wrote:
> Here was my problem.
> I have a database with replication. All I need to do was restore the
> database from a backup (totally different data from another server).
> The restore process was messed up because I was not aware there is
> replication in place. So the database status is always loading. I have
> to use sp_removedb and then restore the db.
> But the publication is still there (with a push subscription). Can not
> do anything with Enterprise manager either. So I did a
> sp_dropReplication but I later noticed that I have to do
> sp_dropsubscription first.
> Now I am stuck:
> I can not drop the subscription (complaining error 20026 can not find
> publication)
> and I can not create publication either (compaling there is already a
> subscriptin)
> Anyone just tell how to get rid of this and I can start over.
> Thanks
> John
|||Hi,
The MS document says to do sp_dropReplication AFTER
sp_dropSubscription.
I dropped publication first so the subscriptions became orphant.
I fixed it by manaully deleting entry in distribution..MSsubscriptions
table. After that I was able to re-create publication and then the
subscription.
Thanks
John
prakashcn@.hotmail.com wrote:[vbcol=seagreen]
> Hey
> If u hav droped the publication already dont wory forget it
> and continue.
> The other way is in the publication DB u hav some tables
> which keep tracks of the subcription info just clear that
> rows also. or just drop the subscription DB also
> And other thing what u hav done is u hav droped the DB
> If the DB is droped then automatically publication is deleted
> and related information tables are also deleted which holds
> the subscription information
> I really dont know what is stoping u to continue to restore DB
> from the backup and dont get panic and start the
> restore process
> -Cheers - Prax
>
> Q. John Chen wrote:

Drop a suspect database

a sql 7.0 database goes suspect because the HD fails. Now i cant restore the database from the backup neither delete the database. How can i do it
In the fail i lose the mdf archiveRestart the sqlserver in mimum configuration mode.
Then delete the entry in the master.dbo.sysdatabases.
Restart the sqlserver.
"Rafael Araujo" <raraujoh@.hotmail.com> wrote in message
news:B1E10C04-6C75-4B0B-9958-2D38B709CF7C@.microsoft.com...
> a sql 7.0 database goes suspect because the HD fails. Now i cant restore
the database from the backup neither delete the database. How can i do it?
> In the fail i lose the mdf archive|||So you want to drop the database in order to be able to restore it from a
backup?
If so, first try DROP DATABASE from query analyzer. If that doesn't work,
try sp_dbremove.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rafael Araujo" <raraujoh@.hotmail.com> wrote in message
news:B1E10C04-6C75-4B0B-9958-2D38B709CF7C@.microsoft.com...
> a sql 7.0 database goes suspect because the HD fails. Now i cant restore
the database from the backup neither delete the database. How can i do it?
> In the fail i lose the mdf archive