Recovery from a Restore without replication info

Can anyone point me to a set of documented steps on how to clean up the
following mess?
Three days ago a developer (not me) deployed some changes to a production
database by restoring from a backup created on another server. The problem
is the production database was set for replication. Now there is an
inconsistency between the replication metadata stored in Master and
Distribution vs. the production database. The type of replication is
snapshot and the servers are all version 2005 SP1.
I've recovered from this exact same situation in SQL 2000 but never in 2005.
Unfortunately I didn't bother to document the steps. I dimly recall however
a lot of false starts and direct editing of the contents of the Master
database. I'd really love to avoid experimentation this time around.
Hi, Kevin,
To let me better understand your issue, coulder you please let me know the
follwing questions:
1. Who is the publisher?
2. Who is the distributor?
3. Who is the subscriber?
4. What is the type of your replication?
5. Could you please describe me more detailed of the inconsistency that you
mentioned?
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||As it is snapshot replication, what I'd do is to remove the publication, run
sp_removedbreplication on the relevant databases (assuming no other
publications there) and then readd the publication. If you have been backing
up your replication scripts (http://www.replicationanswers.com/Script7.asp)
this is pretty straightforward to do.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Thanks for the suggestion. That's what I am going to try to do this morning.
I spent all part of yesterday setting up a test environment that reproduces
the problem. I know that on 2000 the system stored procs that delete
replication data from the Master and Distribution databases fail if the
publishing database does not contain replication metadata. My hope is that
the procs have been improved in 2005 and will no longer fail.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e5JCCbGbHHA.4176@.TK2MSFTNGP02.phx.gbl...
> As it is snapshot replication, what I'd do is to remove the publication,
> run sp_removedbreplication on the relevant databases (assuming no other
> publications there) and then readd the publication. If you have been
> backing up your replication scripts
> (http://www.replicationanswers.com/Script7.asp) this is pretty
> straightforward to do.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
|||Hi, Kevin,
I am sorry that I missed reading that your replication type was snapshot.
Did you mean that your publication database was polluted?
In this case, you can just restore your publication database from the
latest dabase backup file since it is a snapshot replication; however there
may be data loss between the backup time to now.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Recovering SQL 2000 Database without the Log File not in clean disconnected state

We just had a hard drive crash here. We were able to copy off the .mdf file from SQL server 2000 from the drive, but not the .ldf (which was only 720k anyhow).

When I try to attach the mdf to an SQL Server 2000 or 2005 server I get an error saying that the log file doesn't exist.

Does anyone know a way to attach this not-cleanly-closed database file and recover the data? (apparently the person decided that they need-not backup their database :<)

Thanks for any help you can provide!

You may get lucky and be able to attach to a SQL 2005 instance and use the ATTACH_REBUILD_LOG clause...try something like this:

create database <x> on <filespec> for attach_rebuild_log

|||

Thanks. I tried that, but it said the same thing, and the documentation says that you have to have a cleanly shut down database to be able to use that command.

Thanks anyways! Any other ideas?

|||I got the same problem and the sames answers from 2000 and 2005. Any idea will help me too.|||This has to go back to your backups. Do you have full/differential/log backups? What's your disk architecture?|||I posted too fast...didn't get to read that you do not have backups...I have another question...did the entire server go down or only the disk?|||The whole drive failed. I was able to freeze it and get the mdf, but the log file was corrupt.

Hence i have an mdf and no log file that will work, and the idiot that owned the machine didn't do a backup.

Recovering SQL 2000 Database without the Log File not in clean disconnected state

We just had a hard drive crash here. We were able to copy off the .mdf file from SQL server 2000 from the drive, but not the .ldf (which was only 720k anyhow).

When I try to attach the mdf to an SQL Server 2000 or 2005 server I get an error saying that the log file doesn't exist.

Does anyone know a way to attach this not-cleanly-closed database file and recover the data? (apparently the person decided that they need-not backup their database :<)

Thanks for any help you can provide!

You may get lucky and be able to attach to a SQL 2005 instance and use the ATTACH_REBUILD_LOG clause...try something like this:

create database <x> on <filespec> for attach_rebuild_log

|||

Thanks. I tried that, but it said the same thing, and the documentation says that you have to have a cleanly shut down database to be able to use that command.

Thanks anyways! Any other ideas?

|||I got the same problem and the sames answers from 2000 and 2005. Any idea will help me too.|||This has to go back to your backups. Do you have full/differential/log backups? What's your disk architecture?|||I posted too fast...didn't get to read that you do not have backups...I have another question...did the entire server go down or only the disk?|||The whole drive failed. I was able to freeze it and get the mdf, but the log file was corrupt.

Hence i have an mdf and no log file that will work, and the idiot that owned the machine didn't do a backup.

Recovering SQL 2000 Database without the Log File not in clean disconnected state

We just had a hard drive crash here. We were able to copy off the .mdf file from SQL server 2000 from the drive, but not the .ldf (which was only 720k anyhow).

When I try to attach the mdf to an SQL Server 2000 or 2005 server I get an error saying that the log file doesn't exist.

Does anyone know a way to attach this not-cleanly-closed database file and recover the data? (apparently the person decided that they need-not backup their database :<)

Thanks for any help you can provide!

You may get lucky and be able to attach to a SQL 2005 instance and use the ATTACH_REBUILD_LOG clause...try something like this:

create database <x> on <filespec> for attach_rebuild_log

|||

Thanks. I tried that, but it said the same thing, and the documentation says that you have to have a cleanly shut down database to be able to use that command.

Thanks anyways! Any other ideas?

|||I got the same problem and the sames answers from 2000 and 2005. Any idea will help me too.|||This has to go back to your backups. Do you have full/differential/log backups? What's your disk architecture?|||I posted too fast...didn't get to read that you do not have backups...I have another question...did the entire server go down or only the disk?|||The whole drive failed. I was able to freeze it and get the mdf, but the log file was corrupt.

Hence i have an mdf and no log file that will work, and the idiot that owned the machine didn't do a backup.