deleting SQL Srv 2K5 publication with no Agents

I have the case where a SQL Srv 2K5 publication has no agents - agents were accidentally erased during backup/restore. I was abel to erase subscription.

The error is "The specified @.job_name <jab name> does not exist. (Microsft SQL Server, Error: 14262)

Is there any way to remove publication?

Help will be greatly appreciated!

Vladimir

Do you have trouble in dropping subscription/publication? Error looks like to happen when you drop a remote pull subscription.

Anyway, you may call SP directly to drop the subscription, then publication. Please refer to BOL.

Similar call will be like:

use [PubDB]
exec sp_dropmergesubscription

exec sp_mergesubscription_cleanup

exec sp_dropmergepublication

Hope it will be helpful.

Thanks

Yunjing

|||

Hello,

I am dropping xactional publication, not merge subscription. Normally, it is not a problem, but in this case all three agent jobs are gone.

When I try to delete it, I am getting message about absent snapshot agent, Error 20678 "|Could not find the regular snapshot job for the specified publication <publication name>. Supply either @.job_id or @.job name to idnetify the job."

But the jobs like I said is gone.

I have tried sp_droppublication. Got error: "Message 14013, This database is not enabled for publication", which does not make sense since publication is there already.

Thanks for trying.

Vladimir

|||

Hello, Vladimir,

I think meta data got messed up somehow. Could you check whether publications are still there?

please call the below two sql to see if you can get any thing.

On publisher server, select * from [PublicationDB]..syspublications

On distributor server, select * from [distributionDB]..MSpublications

Thanks

Yunjing

|||

Hi Yunjing,

The syspublications does not have any rows.

MSpublications does not list this "orphaned" publication, but it lists all other "healthy" publication I created.

Yesterday I backed up and restored the publisher database. There is the switch during restore 'keep_publication' which gave me hope that

if I will not set it during restore that will break link b/w db and publication. But after restore error still percists.

R-clicking on publication allows to create publication drop script. I ran it, and against every article in publication the message was "the publication does not exist'. This is while I see that publication in GUI.

Also, r-clicking, I tried to start Snapshot agent to create snapshot - the absent snapshot may be the root of the problem.

I guess it was erase together with erasure of snapshot agent.

It failed at the end with error "Cannot insert NULL into column "publisher_database_id", table "distribution.dbo.MSrepl_transactions".

Thanks Yunjing for keeping up.

Vladimir

sql

Deleted entire DB by accident!

Gentlemen, Can anyone save my life at the moment!
I need to restore a DB that I accidentally erased using
the Delete command in the Enterprise admin. I was on the
wrong server and the DB names are the same.
(No Backups were on this box since it's a dev box.)
Can anyone tell me a way to get my DB back?
Thanks,
BradTake your last production backup and restore it to your dev server.
Or were you fibbing and delete a production database you had no backups for?
"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in message
news:276e601c38f65$cc22d6d0$a601280a@.phx.gbl...
> Gentlemen, Can anyone save my life at the moment!
> I need to restore a DB that I accidentally erased using
> the Delete command in the Enterprise admin. I was on the
> wrong server and the DB names are the same.
> (No Backups were on this box since it's a dev box.)
> Can anyone tell me a way to get my DB back?
> Thanks,
> Brad|||The concept of Dev in our dept is that it it's Dev before
it goes to production. So, it's never actually seen
production. There is no actual restore .BAK file and
the .MDF file is missing.
Any ideas?
>--Original Message--
>Take your last production backup and restore it to your
dev server.
>Or were you fibbing and delete a production database you
had no backups for?
>"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in
message
>news:276e601c38f65$cc22d6d0$a601280a@.phx.gbl...
>> Gentlemen, Can anyone save my life at the moment!
>> I need to restore a DB that I accidentally erased using
>> the Delete command in the Enterprise admin. I was on
the
>> wrong server and the DB names are the same.
>> (No Backups were on this box since it's a dev box.)
>> Can anyone tell me a way to get my DB back?
>> Thanks,
>> Brad
>
>.
>|||Turn off the sql server and start searching for a file restore utility (I
don't have one or know of one in particular) that will work on your OS. If
the filespace is overwritten you will lose your data.
Just in case, though, did you select to delete the filegroups? If not they
will still be there as .ndf, .mdf, .ldf files.
"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in message
news:2774501c38f69$d9323470$a601280a@.phx.gbl...
> The concept of Dev in our dept is that it it's Dev before
> it goes to production. So, it's never actually seen
> production. There is no actual restore .BAK file and
> the .MDF file is missing.
> Any ideas?
>
> >--Original Message--
> >Take your last production backup and restore it to your
> dev server.
> >
> >Or were you fibbing and delete a production database you
> had no backups for?
> >
> >"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in
> message
> >news:276e601c38f65$cc22d6d0$a601280a@.phx.gbl...
> >> Gentlemen, Can anyone save my life at the moment!
> >> I need to restore a DB that I accidentally erased using
> >> the Delete command in the Enterprise admin. I was on
> the
> >> wrong server and the DB names are the same.
> >> (No Backups were on this box since it's a dev box.)
> >> Can anyone tell me a way to get my DB back?
> >>
> >> Thanks,
> >>
> >> Brad
> >
> >
> >.
> >|||Thanks for the help, unfortunately I've been trying to
recover the MDF files with recovery utilities but so far
I've had no luck. Is it even possible to track down an
MDF file once it's been deleted with Enterprise Manager?
>--Original Message--
>Turn off the sql server and start searching for a file
restore utility (I
>don't have one or know of one in particular) that will
work on your OS. If
>the filespace is overwritten you will lose your data.
>Just in case, though, did you select to delete the
filegroups? If not they
>will still be there as .ndf, .mdf, .ldf files.
>
>
>
>"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in
message
>news:2774501c38f69$d9323470$a601280a@.phx.gbl...
>> The concept of Dev in our dept is that it it's Dev
before
>> it goes to production. So, it's never actually seen
>> production. There is no actual restore .BAK file and
>> the .MDF file is missing.
>> Any ideas?
>>
>> >--Original Message--
>> >Take your last production backup and restore it to your
>> dev server.
>> >
>> >Or were you fibbing and delete a production database
you
>> had no backups for?
>> >
>> >"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in
>> message
>> >news:276e601c38f65$cc22d6d0$a601280a@.phx.gbl...
>> >> Gentlemen, Can anyone save my life at the moment!
>> >> I need to restore a DB that I accidentally erased
using
>> >> the Delete command in the Enterprise admin. I was on
>> the
>> >> wrong server and the DB names are the same.
>> >> (No Backups were on this box since it's a dev box.)
>> >> Can anyone tell me a way to get my DB back?
>> >>
>> >> Thanks,
>> >>
>> >> Brad
>> >
>> >
>> >.
>> >
>
>.
>|||Deleting with EM means that EM executes the DROP DATABASE command. For the DROP DATABASE command,
SQL Server will delete the file physically. Working on the OS level is your last resort. And, if
keeping the data from the dev environment is crucial, make sure you have backup for the dev
environment next time. :-)
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in message
news:036001c38f72$ef2e2b90$a401280a@.phx.gbl...
> Thanks for the help, unfortunately I've been trying to
> recover the MDF files with recovery utilities but so far
> I've had no luck. Is it even possible to track down an
> MDF file once it's been deleted with Enterprise Manager?
> >--Original Message--
> >Turn off the sql server and start searching for a file
> restore utility (I
> >don't have one or know of one in particular) that will
> work on your OS. If
> >the filespace is overwritten you will lose your data.
> >
> >Just in case, though, did you select to delete the
> filegroups? If not they
> >will still be there as .ndf, .mdf, .ldf files.
> >
> >
> >
> >
> >
> >
> >"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in
> message
> >news:2774501c38f69$d9323470$a601280a@.phx.gbl...
> >> The concept of Dev in our dept is that it it's Dev
> before
> >> it goes to production. So, it's never actually seen
> >> production. There is no actual restore .BAK file and
> >> the .MDF file is missing.
> >>
> >> Any ideas?
> >>
> >>
> >> >--Original Message--
> >> >Take your last production backup and restore it to your
> >> dev server.
> >> >
> >> >Or were you fibbing and delete a production database
> you
> >> had no backups for?
> >> >
> >> >"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in
> >> message
> >> >news:276e601c38f65$cc22d6d0$a601280a@.phx.gbl...
> >> >> Gentlemen, Can anyone save my life at the moment!
> >> >> I need to restore a DB that I accidentally erased
> using
> >> >> the Delete command in the Enterprise admin. I was on
> >> the
> >> >> wrong server and the DB names are the same.
> >> >> (No Backups were on this box since it's a dev box.)
> >> >> Can anyone tell me a way to get my DB back?
> >> >>
> >> >> Thanks,
> >> >>
> >> >> Brad
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >