Drop Target Server When MSX Server Gone

Due to disk failure and lack of current backups ( I just started here) we
were forced to restore a copy of msdb from over a year ago.
(I had to put the database back together again like Humpty Dumpty)
My problem is that there are several jobs that were pushed from an MSX
server that no longer exists.
I can't delete them - it tells me to go to the MSX server.
Any suggestions?
HI
Defect target server from master.
Enlist again, and assign jobs to target.
Or
Delete jobssteps and jobs from msdb.dbo.sysjobsteps,
msdb.dbo.sysjobschedules and msdb.dbo.sysjobs.
Andras Jakus MCDBA
"Amanda" wrote:

> Due to disk failure and lack of current backups ( I just started here) we
> were forced to restore a copy of msdb from over a year ago.
> (I had to put the database back together again like Humpty Dumpty)
> My problem is that there are several jobs that were pushed from an MSX
> server that no longer exists.
> I can't delete them - it tells me to go to the MSX server.
> Any suggestions?
|||You should be able to use the information in step 3 at
http://www.karaszi.com/SQLServer/inf...rver_name.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Amanda" <Amanda@.discussions.microsoft.com> wrote in message
news:FF74683A-DAFB-4255-85A9-00DB3FA850D8@.microsoft.com...
> Due to disk failure and lack of current backups ( I just started here) we
> were forced to restore a copy of msdb from over a year ago.
> (I had to put the database back together again like Humpty Dumpty)
> My problem is that there are several jobs that were pushed from an MSX
> server that no longer exists.
> I can't delete them - it tells me to go to the MSX server.
> Any suggestions?
|||WAY COOL!
This worked. The other solution won't work because it won't let you change
anything on the target server - only on the master, which doesn't exist.
This worked wonderfully, though. Thanks.
"Tibor Karaszi" wrote:

> You should be able to use the information in step 3 at
> http://www.karaszi.com/SQLServer/inf...rver_name.asp.
>
sql

Drop Target Server When MSX Server Gone

Due to disk failure and lack of current backups ( I just started here) we
were forced to restore a copy of msdb from over a year ago.
(I had to put the database back together again like Humpty Dumpty)
My problem is that there are several jobs that were pushed from an MSX
server that no longer exists.
I can't delete them - it tells me to go to the MSX server.
Any suggestions?HI
Defect target server from master.
Enlist again, and assign jobs to target.
Or
Delete jobssteps and jobs from msdb.dbo.sysjobsteps,
msdb.dbo.sysjobschedules and msdb.dbo.sysjobs.
Andras Jakus MCDBA
"Amanda" wrote:

> Due to disk failure and lack of current backups ( I just started here) we
> were forced to restore a copy of msdb from over a year ago.
> (I had to put the database back together again like Humpty Dumpty)
> My problem is that there are several jobs that were pushed from an MSX
> server that no longer exists.
> I can't delete them - it tells me to go to the MSX server.
> Any suggestions?|||You should be able to use the information in step 3 at
http://www.karaszi.com/SQLServer/in...erver_name.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Amanda" <Amanda@.discussions.microsoft.com> wrote in message
news:FF74683A-DAFB-4255-85A9-00DB3FA850D8@.microsoft.com...
> Due to disk failure and lack of current backups ( I just started here) we
> were forced to restore a copy of msdb from over a year ago.
> (I had to put the database back together again like Humpty Dumpty)
> My problem is that there are several jobs that were pushed from an MSX
> server that no longer exists.
> I can't delete them - it tells me to go to the MSX server.
> Any suggestions?|||WAY COOL!
This worked. The other solution won't work because it won't let you change
anything on the target server - only on the master, which doesn't exist.
This worked wonderfully, though. Thanks.
"Tibor Karaszi" wrote:

> You should be able to use the information in step 3 at
> http://www.karaszi.com/SQLServer/in...erver_name.asp.
>

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 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

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 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