Hi,
we use ftp to transfer database from one server to another
(detach, ftp copy, attach). Database is pretty large
(~300GB), and when we reattach database files to
destination server, database comes up with errors
(detected by dbcc checkalloc, there is nothing in error
log). We're unable to repair the database, and even if we
use dts to recreate corrupted tables, other tables come up
corrupted...
We use ftp because of the speed... Does anybody know why
is this happening? Any other idea for data transfer?
Thanks,
OJ
How often are you doing this? Have you considered doing a backup at one
site, restoring at the other, and then using log shipping/replication to
keep them in sync perpetually?
http://www.aspfaq.com/
(Reverse address to reply.)
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:018301c495d2$e21fc460$a401280a@.phx.gbl...
> Hi,
> we use ftp to transfer database from one server to another
> (detach, ftp copy, attach). Database is pretty large
> (~300GB), and when we reattach database files to
> destination server, database comes up with errors
> (detected by dbcc checkalloc, there is nothing in error
> log). We're unable to repair the database, and even if we
> use dts to recreate corrupted tables, other tables come up
> corrupted...
> We use ftp because of the speed... Does anybody know why
> is this happening? Any other idea for data transfer?
> Thanks,
> OJ
|||Have you run CHECKDB on the original db to make sure it isn't corrupted to
begin with?
Andrew J. Kelly SQL MVP
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:018301c495d2$e21fc460$a401280a@.phx.gbl...
> Hi,
> we use ftp to transfer database from one server to another
> (detach, ftp copy, attach). Database is pretty large
> (~300GB), and when we reattach database files to
> destination server, database comes up with errors
> (detected by dbcc checkalloc, there is nothing in error
> log). We're unable to repair the database, and even if we
> use dts to recreate corrupted tables, other tables come up
> corrupted...
> We use ftp because of the speed... Does anybody know why
> is this happening? Any other idea for data transfer?
> Thanks,
> OJ
|||No, because we build this database on the staging server
monthly and after transfer to destination server set it to
read only mode...
Thanks
>--Original Message--
>How often are you doing this? Have you considered doing
a backup at one
>site, restoring at the other, and then using log
shipping/replication to
>keep them in sync perpetually?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"OJ" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:018301c495d2$e21fc460$a401280a@.phx.gbl...
another[vbcol=seagreen]
we[vbcol=seagreen]
up
>
>.
>
|||Yes we have.We have every day daily maintenance job which
checks database integrity...
>--Original Message--
>Have you run CHECKDB on the original db to make sure it
isn't corrupted to
>begin with?
>--
>Andrew J. Kelly SQL MVP
>
>"OJ" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:018301c495d2$e21fc460$a401280a@.phx.gbl...
another[vbcol=seagreen]
we[vbcol=seagreen]
up
>
>.
>
|||Is there any chance you are transferring the files using ASCII instead of
binary?
"OJ" wrote:
> Hi,
> we use ftp to transfer database from one server to another
> (detach, ftp copy, attach). Database is pretty large
> (~300GB), and when we reattach database files to
> destination server, database comes up with errors
> (detected by dbcc checkalloc, there is nothing in error
> log). We're unable to repair the database, and even if we
> use dts to recreate corrupted tables, other tables come up
> corrupted...
> We use ftp because of the speed... Does anybody know why
> is this happening? Any other idea for data transfer?
> Thanks,
> OJ
>
|||I would suspect the FTP process it self then. I have had issues in the past
with large FTP files getting corrupted especially if it is not sent in
binary format. You might want to look at using a product such as SQL
LiteSpeed to provide you with a compressed backup that you can then FTP.
Your likely to get a file that is up to 70 or 80% smaller than the detached
one you are using now.
Andrew J. Kelly SQL MVP
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:030201c495e6$517ab730$a401280a@.phx.gbl...[vbcol=seagreen]
> Yes we have.We have every day daily maintenance job which
> checks database integrity...
> isn't corrupted to
> message
> another
> we
> up
Showing posts with label corrupted. Show all posts
Showing posts with label corrupted. Show all posts
Sunday, February 12, 2012
After ftp transfer, database allways corrupted
Hi,
we use ftp to transfer database from one server to another
(detach, ftp copy, attach). Database is pretty large
(~300GB), and when we reattach database files to
destination server, database comes up with errors
(detected by dbcc checkalloc, there is nothing in error
log). We're unable to repair the database, and even if we
use dts to recreate corrupted tables, other tables come up
corrupted...
We use ftp because of the speed... Does anybody know why
is this happening? Any other idea for data transfer?
Thanks,
OJHow often are you doing this? Have you considered doing a backup at one
site, restoring at the other, and then using log shipping/replication to
keep them in sync perpetually?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:018301c495d2$e21fc460$a401280a@.phx.gbl...
> Hi,
> we use ftp to transfer database from one server to another
> (detach, ftp copy, attach). Database is pretty large
> (~300GB), and when we reattach database files to
> destination server, database comes up with errors
> (detected by dbcc checkalloc, there is nothing in error
> log). We're unable to repair the database, and even if we
> use dts to recreate corrupted tables, other tables come up
> corrupted...
> We use ftp because of the speed... Does anybody know why
> is this happening? Any other idea for data transfer?
> Thanks,
> OJ|||Have you run CHECKDB on the original db to make sure it isn't corrupted to
begin with?
--
Andrew J. Kelly SQL MVP
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:018301c495d2$e21fc460$a401280a@.phx.gbl...
> Hi,
> we use ftp to transfer database from one server to another
> (detach, ftp copy, attach). Database is pretty large
> (~300GB), and when we reattach database files to
> destination server, database comes up with errors
> (detected by dbcc checkalloc, there is nothing in error
> log). We're unable to repair the database, and even if we
> use dts to recreate corrupted tables, other tables come up
> corrupted...
> We use ftp because of the speed... Does anybody know why
> is this happening? Any other idea for data transfer?
> Thanks,
> OJ|||No, because we build this database on the staging server
monthly and after transfer to destination server set it to
read only mode...
Thanks
>--Original Message--
>How often are you doing this? Have you considered doing
a backup at one
>site, restoring at the other, and then using log
shipping/replication to
>keep them in sync perpetually?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"OJ" <anonymous@.discussions.microsoft.com> wrote in
message
>news:018301c495d2$e21fc460$a401280a@.phx.gbl...
>> Hi,
>> we use ftp to transfer database from one server to
another
>> (detach, ftp copy, attach). Database is pretty large
>> (~300GB), and when we reattach database files to
>> destination server, database comes up with errors
>> (detected by dbcc checkalloc, there is nothing in error
>> log). We're unable to repair the database, and even if
we
>> use dts to recreate corrupted tables, other tables come
up
>> corrupted...
>> We use ftp because of the speed... Does anybody know why
>> is this happening? Any other idea for data transfer?
>> Thanks,
>> OJ
>
>.
>|||Yes we have.We have every day daily maintenance job which
checks database integrity...
>--Original Message--
>Have you run CHECKDB on the original db to make sure it
isn't corrupted to
>begin with?
>--
>Andrew J. Kelly SQL MVP
>
>"OJ" <anonymous@.discussions.microsoft.com> wrote in
message
>news:018301c495d2$e21fc460$a401280a@.phx.gbl...
>> Hi,
>> we use ftp to transfer database from one server to
another
>> (detach, ftp copy, attach). Database is pretty large
>> (~300GB), and when we reattach database files to
>> destination server, database comes up with errors
>> (detected by dbcc checkalloc, there is nothing in error
>> log). We're unable to repair the database, and even if
we
>> use dts to recreate corrupted tables, other tables come
up
>> corrupted...
>> We use ftp because of the speed... Does anybody know why
>> is this happening? Any other idea for data transfer?
>> Thanks,
>> OJ
>
>.
>|||I would suspect the FTP process it self then. I have had issues in the past
with large FTP files getting corrupted especially if it is not sent in
binary format. You might want to look at using a product such as SQL
LiteSpeed to provide you with a compressed backup that you can then FTP.
Your likely to get a file that is up to 70 or 80% smaller than the detached
one you are using now.
--
Andrew J. Kelly SQL MVP
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:030201c495e6$517ab730$a401280a@.phx.gbl...
> Yes we have.We have every day daily maintenance job which
> checks database integrity...
> >--Original Message--
> >Have you run CHECKDB on the original db to make sure it
> isn't corrupted to
> >begin with?
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"OJ" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:018301c495d2$e21fc460$a401280a@.phx.gbl...
> >> Hi,
> >> we use ftp to transfer database from one server to
> another
> >> (detach, ftp copy, attach). Database is pretty large
> >> (~300GB), and when we reattach database files to
> >> destination server, database comes up with errors
> >> (detected by dbcc checkalloc, there is nothing in error
> >> log). We're unable to repair the database, and even if
> we
> >> use dts to recreate corrupted tables, other tables come
> up
> >> corrupted...
> >> We use ftp because of the speed... Does anybody know why
> >> is this happening? Any other idea for data transfer?
> >> Thanks,
> >> OJ
> >
> >
> >.
> >
we use ftp to transfer database from one server to another
(detach, ftp copy, attach). Database is pretty large
(~300GB), and when we reattach database files to
destination server, database comes up with errors
(detected by dbcc checkalloc, there is nothing in error
log). We're unable to repair the database, and even if we
use dts to recreate corrupted tables, other tables come up
corrupted...
We use ftp because of the speed... Does anybody know why
is this happening? Any other idea for data transfer?
Thanks,
OJHow often are you doing this? Have you considered doing a backup at one
site, restoring at the other, and then using log shipping/replication to
keep them in sync perpetually?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:018301c495d2$e21fc460$a401280a@.phx.gbl...
> Hi,
> we use ftp to transfer database from one server to another
> (detach, ftp copy, attach). Database is pretty large
> (~300GB), and when we reattach database files to
> destination server, database comes up with errors
> (detected by dbcc checkalloc, there is nothing in error
> log). We're unable to repair the database, and even if we
> use dts to recreate corrupted tables, other tables come up
> corrupted...
> We use ftp because of the speed... Does anybody know why
> is this happening? Any other idea for data transfer?
> Thanks,
> OJ|||Have you run CHECKDB on the original db to make sure it isn't corrupted to
begin with?
--
Andrew J. Kelly SQL MVP
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:018301c495d2$e21fc460$a401280a@.phx.gbl...
> Hi,
> we use ftp to transfer database from one server to another
> (detach, ftp copy, attach). Database is pretty large
> (~300GB), and when we reattach database files to
> destination server, database comes up with errors
> (detected by dbcc checkalloc, there is nothing in error
> log). We're unable to repair the database, and even if we
> use dts to recreate corrupted tables, other tables come up
> corrupted...
> We use ftp because of the speed... Does anybody know why
> is this happening? Any other idea for data transfer?
> Thanks,
> OJ|||No, because we build this database on the staging server
monthly and after transfer to destination server set it to
read only mode...
Thanks
>--Original Message--
>How often are you doing this? Have you considered doing
a backup at one
>site, restoring at the other, and then using log
shipping/replication to
>keep them in sync perpetually?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"OJ" <anonymous@.discussions.microsoft.com> wrote in
message
>news:018301c495d2$e21fc460$a401280a@.phx.gbl...
>> Hi,
>> we use ftp to transfer database from one server to
another
>> (detach, ftp copy, attach). Database is pretty large
>> (~300GB), and when we reattach database files to
>> destination server, database comes up with errors
>> (detected by dbcc checkalloc, there is nothing in error
>> log). We're unable to repair the database, and even if
we
>> use dts to recreate corrupted tables, other tables come
up
>> corrupted...
>> We use ftp because of the speed... Does anybody know why
>> is this happening? Any other idea for data transfer?
>> Thanks,
>> OJ
>
>.
>|||Yes we have.We have every day daily maintenance job which
checks database integrity...
>--Original Message--
>Have you run CHECKDB on the original db to make sure it
isn't corrupted to
>begin with?
>--
>Andrew J. Kelly SQL MVP
>
>"OJ" <anonymous@.discussions.microsoft.com> wrote in
message
>news:018301c495d2$e21fc460$a401280a@.phx.gbl...
>> Hi,
>> we use ftp to transfer database from one server to
another
>> (detach, ftp copy, attach). Database is pretty large
>> (~300GB), and when we reattach database files to
>> destination server, database comes up with errors
>> (detected by dbcc checkalloc, there is nothing in error
>> log). We're unable to repair the database, and even if
we
>> use dts to recreate corrupted tables, other tables come
up
>> corrupted...
>> We use ftp because of the speed... Does anybody know why
>> is this happening? Any other idea for data transfer?
>> Thanks,
>> OJ
>
>.
>|||I would suspect the FTP process it self then. I have had issues in the past
with large FTP files getting corrupted especially if it is not sent in
binary format. You might want to look at using a product such as SQL
LiteSpeed to provide you with a compressed backup that you can then FTP.
Your likely to get a file that is up to 70 or 80% smaller than the detached
one you are using now.
--
Andrew J. Kelly SQL MVP
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:030201c495e6$517ab730$a401280a@.phx.gbl...
> Yes we have.We have every day daily maintenance job which
> checks database integrity...
> >--Original Message--
> >Have you run CHECKDB on the original db to make sure it
> isn't corrupted to
> >begin with?
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"OJ" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:018301c495d2$e21fc460$a401280a@.phx.gbl...
> >> Hi,
> >> we use ftp to transfer database from one server to
> another
> >> (detach, ftp copy, attach). Database is pretty large
> >> (~300GB), and when we reattach database files to
> >> destination server, database comes up with errors
> >> (detected by dbcc checkalloc, there is nothing in error
> >> log). We're unable to repair the database, and even if
> we
> >> use dts to recreate corrupted tables, other tables come
> up
> >> corrupted...
> >> We use ftp because of the speed... Does anybody know why
> >> is this happening? Any other idea for data transfer?
> >> Thanks,
> >> OJ
> >
> >
> >.
> >
After corrupted publisher
Hi All,
I am fairly new to MS SQL Replication. I have two servers one is setup
as the publisher and the other is setup as both the subscriber and the
distributor.
Due to server reboots, the publicher DB became corrupted therefore we
pointed all activity to the subscriber DB. Now we would like to bring
back the publisher online, but we do not have a backup.
My Question is, can we restore the publisher DB from the backup of the
subscriber and reinstate replication? if yes could someone please
explain the process as I am new to SQL and replication.
Question two, someone metioned while re-establishing replication I can
regenerate the DB by doing a snapshot. How can I do that on the
publisher.
If you have any other method that will help bring back the publisher
and copy the updated data from the suscriber to the publisher and
re-instate replication that would be great.
PS: the reason why we know the publisher is corrupted is due to a DBCC
CHECKDB(XXX) which gave us a lot of errors.
Thank you much,
Ron
For Question 1: prevent all access to the subscriber database and take a
backup of the publisher and subscriber's database.
Script out the replication setup off the publisher if it is accessible.
Restore on the publisher.
Run sp_removedbreplication on each database.
Obtain your replication script to recreate the publication and subscription
(do you have one?) and edit it to ensure the publication is set up as a
nosync one (for SQL 2000:
http://www.replicationanswers.com/No...lizations.asp, for SQL 2005:
http://www.replicationanswers.com/NoSyncOn2005.asp).
Initialize as per normal.
This means that any tables you had in the publisher's database and not in
the subscriber database will be replaced - is this OK? If not, restore the
publisher database as another name and transfer over the tables/objwects
selectively using DTS.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Ron - answers inline:
> 1: Can I set the subscriber DB to read only?
(1) yes, but when you restore it on the publisher you'll need to set to be
RW to add the publication
> 2: Why do I need a backup of the publisher as it is corrupted.
(2) In case there are tables which aren't in the publication as you'll need
to copy them over into the restored subscriber database later on, assuming
it is addressable in some form or other. Or more simply, just rename it
after scripting out the replication setup.
> 3: By Scripting out the replication off the publisher do you mean right
> click on the on the replication tree in the server where the publisher
> is located and click on Generate SQL Script than click on preview and
> copy the script?
(3) Yes.
> 4: By restore on the publisher do you mean I can restore the backup of
> the subscriber to the publisher?
(4) Yes. When restoring it, you'll give it the same name as the published
database you had previously (the corrupted one). After that, you'll need to
assign permissions to it and then copy in any extra tables.
> 5: You said to run sp_removedbreplication on each database, does that
> mean both publisher and subscriber? are there any other option I
> should use with the sp_removedbreplication?
Yes. No - it's pretty straightforward and just takes one parameter - the
database name.
> 6: Using the replication script how do I recreate the publication and
> subscription? How do I set it up as a nosync on SQL 2000 I did not
> really understand the article.
The replication script should be able to be used as it is with one slight
change: set the @.sync_type = none parameter value of sp_addsubscription ie
change 'Automatic' to 'None'. After that, run
sp_scriptpublicationcustomprocs 'publicationname' at the publisher and
execute the results on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
I am fairly new to MS SQL Replication. I have two servers one is setup
as the publisher and the other is setup as both the subscriber and the
distributor.
Due to server reboots, the publicher DB became corrupted therefore we
pointed all activity to the subscriber DB. Now we would like to bring
back the publisher online, but we do not have a backup.
My Question is, can we restore the publisher DB from the backup of the
subscriber and reinstate replication? if yes could someone please
explain the process as I am new to SQL and replication.
Question two, someone metioned while re-establishing replication I can
regenerate the DB by doing a snapshot. How can I do that on the
publisher.
If you have any other method that will help bring back the publisher
and copy the updated data from the suscriber to the publisher and
re-instate replication that would be great.
PS: the reason why we know the publisher is corrupted is due to a DBCC
CHECKDB(XXX) which gave us a lot of errors.
Thank you much,
Ron
For Question 1: prevent all access to the subscriber database and take a
backup of the publisher and subscriber's database.
Script out the replication setup off the publisher if it is accessible.
Restore on the publisher.
Run sp_removedbreplication on each database.
Obtain your replication script to recreate the publication and subscription
(do you have one?) and edit it to ensure the publication is set up as a
nosync one (for SQL 2000:
http://www.replicationanswers.com/No...lizations.asp, for SQL 2005:
http://www.replicationanswers.com/NoSyncOn2005.asp).
Initialize as per normal.
This means that any tables you had in the publisher's database and not in
the subscriber database will be replaced - is this OK? If not, restore the
publisher database as another name and transfer over the tables/objwects
selectively using DTS.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Ron - answers inline:
> 1: Can I set the subscriber DB to read only?
(1) yes, but when you restore it on the publisher you'll need to set to be
RW to add the publication
> 2: Why do I need a backup of the publisher as it is corrupted.
(2) In case there are tables which aren't in the publication as you'll need
to copy them over into the restored subscriber database later on, assuming
it is addressable in some form or other. Or more simply, just rename it
after scripting out the replication setup.
> 3: By Scripting out the replication off the publisher do you mean right
> click on the on the replication tree in the server where the publisher
> is located and click on Generate SQL Script than click on preview and
> copy the script?
(3) Yes.
> 4: By restore on the publisher do you mean I can restore the backup of
> the subscriber to the publisher?
(4) Yes. When restoring it, you'll give it the same name as the published
database you had previously (the corrupted one). After that, you'll need to
assign permissions to it and then copy in any extra tables.
> 5: You said to run sp_removedbreplication on each database, does that
> mean both publisher and subscriber? are there any other option I
> should use with the sp_removedbreplication?
Yes. No - it's pretty straightforward and just takes one parameter - the
database name.
> 6: Using the replication script how do I recreate the publication and
> subscription? How do I set it up as a nosync on SQL 2000 I did not
> really understand the article.
The replication script should be able to be used as it is with one slight
change: set the @.sync_type = none parameter value of sp_addsubscription ie
change 'Automatic' to 'None'. After that, run
sp_scriptpublicationcustomprocs 'publicationname' at the publisher and
execute the results on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
After corrupted publisher
Hi All,
I am fairly new to MS SQL Replication. I have two servers one is setup
as the publisher and the other is setup as both the subscriber and the
distributor.
Due to server reboots, the publicher DB became corrupted therefore we
pointed all activity to the subscriber DB. Now we would like to bring
back the publisher online, but we do not have a backup.
My Question is, can we restore the publisher DB from the backup of the
subscriber and reinstate replication? if yes could someone please
explain the process as I am new to SQL and replication.
Question two, someone metioned while re-establishing replication I can
regenerate the DB by doing a snapshot. How can I do that on the
publisher.
If you have any other method that will help bring back the publisher
and copy the updated data from the suscriber to the publisher and
re-instate replication that would be great.
PS: the reason why we know the publisher is corrupted is due to a DBCC
CHECKDB(XXX) which gave us a lot of errors.
Thank you much,
RonFor Question 1: prevent all access to the subscriber database and take a
backup of the publisher and subscriber's database.
Script out the replication setup off the publisher if it is accessible.
Restore on the publisher.
Run sp_removedbreplication on each database.
Obtain your replication script to recreate the publication and subscription
(do you have one?) and edit it to ensure the publication is set up as a
nosync one (for SQL 2000:
http://www.replicationanswers.com/N...alizations.asp, for SQL 2005:
http://www.replicationanswers.com/NoSyncOn2005.asp).
Initialize as per normal.
This means that any tables you had in the publisher's database and not in
the subscriber database will be replaced - is this OK? If not, restore the
publisher database as another name and transfer over the tables/objwects
selectively using DTS.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Hi Ron - answers inline:
> 1: Can I set the subscriber DB to read only?
(1) yes, but when you restore it on the publisher you'll need to set to be
RW to add the publication
> 2: Why do I need a backup of the publisher as it is corrupted.
(2) In case there are tables which aren't in the publication as you'll need
to copy them over into the restored subscriber database later on, assuming
it is addressable in some form or other. Or more simply, just rename it
after scripting out the replication setup.
> 3: By Scripting out the replication off the publisher do you mean right
> click on the on the replication tree in the server where the publisher
> is located and click on Generate SQL Script than click on preview and
> copy the script?
(3) Yes.
> 4: By restore on the publisher do you mean I can restore the backup of
> the subscriber to the publisher?
(4) Yes. When restoring it, you'll give it the same name as the published
database you had previously (the corrupted one). After that, you'll need to
assign permissions to it and then copy in any extra tables.
> 5: You said to run sp_removedbreplication on each database, does that
> mean both publisher and subscriber? are there any other option I
> should use with the sp_removedbreplication?
Yes. No - it's pretty straightforward and just takes one parameter - the
database name.
> 6: Using the replication script how do I recreate the publication and
> subscription? How do I set it up as a nosync on SQL 2000 I did not
> really understand the article.
The replication script should be able to be used as it is with one slight
change: set the @.sync_type = none parameter value of sp_addsubscription ie
change 'Automatic' to 'None'. After that, run
sp_scriptpublicationcustomprocs 'publicationname' at the publisher and
execute the results on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
I am fairly new to MS SQL Replication. I have two servers one is setup
as the publisher and the other is setup as both the subscriber and the
distributor.
Due to server reboots, the publicher DB became corrupted therefore we
pointed all activity to the subscriber DB. Now we would like to bring
back the publisher online, but we do not have a backup.
My Question is, can we restore the publisher DB from the backup of the
subscriber and reinstate replication? if yes could someone please
explain the process as I am new to SQL and replication.
Question two, someone metioned while re-establishing replication I can
regenerate the DB by doing a snapshot. How can I do that on the
publisher.
If you have any other method that will help bring back the publisher
and copy the updated data from the suscriber to the publisher and
re-instate replication that would be great.
PS: the reason why we know the publisher is corrupted is due to a DBCC
CHECKDB(XXX) which gave us a lot of errors.
Thank you much,
RonFor Question 1: prevent all access to the subscriber database and take a
backup of the publisher and subscriber's database.
Script out the replication setup off the publisher if it is accessible.
Restore on the publisher.
Run sp_removedbreplication on each database.
Obtain your replication script to recreate the publication and subscription
(do you have one?) and edit it to ensure the publication is set up as a
nosync one (for SQL 2000:
http://www.replicationanswers.com/N...alizations.asp, for SQL 2005:
http://www.replicationanswers.com/NoSyncOn2005.asp).
Initialize as per normal.
This means that any tables you had in the publisher's database and not in
the subscriber database will be replaced - is this OK? If not, restore the
publisher database as another name and transfer over the tables/objwects
selectively using DTS.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Hi Ron - answers inline:
> 1: Can I set the subscriber DB to read only?
(1) yes, but when you restore it on the publisher you'll need to set to be
RW to add the publication
> 2: Why do I need a backup of the publisher as it is corrupted.
(2) In case there are tables which aren't in the publication as you'll need
to copy them over into the restored subscriber database later on, assuming
it is addressable in some form or other. Or more simply, just rename it
after scripting out the replication setup.
> 3: By Scripting out the replication off the publisher do you mean right
> click on the on the replication tree in the server where the publisher
> is located and click on Generate SQL Script than click on preview and
> copy the script?
(3) Yes.
> 4: By restore on the publisher do you mean I can restore the backup of
> the subscriber to the publisher?
(4) Yes. When restoring it, you'll give it the same name as the published
database you had previously (the corrupted one). After that, you'll need to
assign permissions to it and then copy in any extra tables.
> 5: You said to run sp_removedbreplication on each database, does that
> mean both publisher and subscriber? are there any other option I
> should use with the sp_removedbreplication?
Yes. No - it's pretty straightforward and just takes one parameter - the
database name.
> 6: Using the replication script how do I recreate the publication and
> subscription? How do I set it up as a nosync on SQL 2000 I did not
> really understand the article.
The replication script should be able to be used as it is with one slight
change: set the @.sync_type = none parameter value of sp_addsubscription ie
change 'Automatic' to 'None'. After that, run
sp_scriptpublicationcustomprocs 'publicationname' at the publisher and
execute the results on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
After corrupted publisher
Hi All,
I am fairly new to MS SQL Replication. I have two servers one is setup
as the publisher and the other is setup as both the subscriber and the
distributor.
Due to server reboots, the publicher DB became corrupted therefore we
pointed all activity to the subscriber DB. Now we would like to bring
back the publisher online, but we do not have a backup.
My Question is, can we restore the publisher DB from the backup of the
subscriber and reinstate replication? if yes could someone please
explain the process as I am new to SQL and replication.
Question two, someone metioned while re-establishing replication I can
regenerate the DB by doing a snapshot. How can I do that on the
publisher.
If you have any other method that will help bring back the publisher
and copy the updated data from the suscriber to the publisher and
re-instate replication that would be great.
PS: the reason why we know the publisher is corrupted is due to a DBCC
CHECKDB(XXX) which gave us a lot of errors.
Thank you much,
RonFor Question 1: prevent all access to the subscriber database and take a
backup of the publisher and subscriber's database.
Script out the replication setup off the publisher if it is accessible.
Restore on the publisher.
Run sp_removedbreplication on each database.
Obtain your replication script to recreate the publication and subscription
(do you have one?) and edit it to ensure the publication is set up as a
nosync one (for SQL 2000:
http://www.replicationanswers.com/NoSyncInitializations.asp, for SQL 2005:
http://www.replicationanswers.com/NoSyncOn2005.asp).
Initialize as per normal.
This means that any tables you had in the publisher's database and not in
the subscriber database will be replaced - is this OK? If not, restore the
publisher database as another name and transfer over the tables/objwects
selectively using DTS.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Hi Paul,
Thank you for your response.
I have a few question and pardon my lack of understanding as I am new
to SQL and replication.
1: Can I set the subscriber DB to read only?
2: Why do I need a backup of the publisher as it is corrupted.
3: By Scripting out the replication off the publisher do you mean right
click on the on the replication tree in the server where the publisher
is located and click on Generate SQL Script than click on preview and
copy the script?
4: By restore on the publisher do you mean I can restore the backup of
the subscriber to the publisher?
5: You said to run sp_removedbreplication on each database, does that
mean both publisher and subscriber? are there any other option I
should use with the sp_removedbreplication?
6: Using the replication script how do I recreate the publication and
subscription? How do I set it up as a nosync on SQL 2000 I did not
really understand the article.
Thank you very much for you help.
Ron.
------
Paul Ibison wrote:
> For Question 1: prevent all access to the subscriber database and take a
> backup of the publisher and subscriber's database.
> Script out the replication setup off the publisher if it is accessible.
> Restore on the publisher.
> Run sp_removedbreplication on each database.
> Obtain your replication script to recreate the publication and subscription
> (do you have one?) and edit it to ensure the publication is set up as a
> nosync one (for SQL 2000:
> http://www.replicationanswers.com/NoSyncInitializations.asp, for SQL 2005:
> http://www.replicationanswers.com/NoSyncOn2005.asp).
> Initialize as per normal.
> This means that any tables you had in the publisher's database and not in
> the subscriber database will be replaced - is this OK? If not, restore the
> publisher database as another name and transfer over the tables/objwects
> selectively using DTS.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Hi Ron - answers inline:
> 1: Can I set the subscriber DB to read only?
(1) yes, but when you restore it on the publisher you'll need to set to be
RW to add the publication
> 2: Why do I need a backup of the publisher as it is corrupted.
(2) In case there are tables which aren't in the publication as you'll need
to copy them over into the restored subscriber database later on, assuming
it is addressable in some form or other. Or more simply, just rename it
after scripting out the replication setup.
> 3: By Scripting out the replication off the publisher do you mean right
> click on the on the replication tree in the server where the publisher
> is located and click on Generate SQL Script than click on preview and
> copy the script?
(3) Yes.
> 4: By restore on the publisher do you mean I can restore the backup of
> the subscriber to the publisher?
(4) Yes. When restoring it, you'll give it the same name as the published
database you had previously (the corrupted one). After that, you'll need to
assign permissions to it and then copy in any extra tables.
> 5: You said to run sp_removedbreplication on each database, does that
> mean both publisher and subscriber? are there any other option I
> should use with the sp_removedbreplication?
Yes. No - it's pretty straightforward and just takes one parameter - the
database name.
> 6: Using the replication script how do I recreate the publication and
> subscription? How do I set it up as a nosync on SQL 2000 I did not
> really understand the article.
The replication script should be able to be used as it is with one slight
change: set the @.sync_type = none parameter value of sp_addsubscription ie
change 'Automatic' to 'None'. After that, run
sp_scriptpublicationcustomprocs 'publicationname' at the publisher and
execute the results on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
I am fairly new to MS SQL Replication. I have two servers one is setup
as the publisher and the other is setup as both the subscriber and the
distributor.
Due to server reboots, the publicher DB became corrupted therefore we
pointed all activity to the subscriber DB. Now we would like to bring
back the publisher online, but we do not have a backup.
My Question is, can we restore the publisher DB from the backup of the
subscriber and reinstate replication? if yes could someone please
explain the process as I am new to SQL and replication.
Question two, someone metioned while re-establishing replication I can
regenerate the DB by doing a snapshot. How can I do that on the
publisher.
If you have any other method that will help bring back the publisher
and copy the updated data from the suscriber to the publisher and
re-instate replication that would be great.
PS: the reason why we know the publisher is corrupted is due to a DBCC
CHECKDB(XXX) which gave us a lot of errors.
Thank you much,
RonFor Question 1: prevent all access to the subscriber database and take a
backup of the publisher and subscriber's database.
Script out the replication setup off the publisher if it is accessible.
Restore on the publisher.
Run sp_removedbreplication on each database.
Obtain your replication script to recreate the publication and subscription
(do you have one?) and edit it to ensure the publication is set up as a
nosync one (for SQL 2000:
http://www.replicationanswers.com/NoSyncInitializations.asp, for SQL 2005:
http://www.replicationanswers.com/NoSyncOn2005.asp).
Initialize as per normal.
This means that any tables you had in the publisher's database and not in
the subscriber database will be replaced - is this OK? If not, restore the
publisher database as another name and transfer over the tables/objwects
selectively using DTS.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Hi Paul,
Thank you for your response.
I have a few question and pardon my lack of understanding as I am new
to SQL and replication.
1: Can I set the subscriber DB to read only?
2: Why do I need a backup of the publisher as it is corrupted.
3: By Scripting out the replication off the publisher do you mean right
click on the on the replication tree in the server where the publisher
is located and click on Generate SQL Script than click on preview and
copy the script?
4: By restore on the publisher do you mean I can restore the backup of
the subscriber to the publisher?
5: You said to run sp_removedbreplication on each database, does that
mean both publisher and subscriber? are there any other option I
should use with the sp_removedbreplication?
6: Using the replication script how do I recreate the publication and
subscription? How do I set it up as a nosync on SQL 2000 I did not
really understand the article.
Thank you very much for you help.
Ron.
------
Paul Ibison wrote:
> For Question 1: prevent all access to the subscriber database and take a
> backup of the publisher and subscriber's database.
> Script out the replication setup off the publisher if it is accessible.
> Restore on the publisher.
> Run sp_removedbreplication on each database.
> Obtain your replication script to recreate the publication and subscription
> (do you have one?) and edit it to ensure the publication is set up as a
> nosync one (for SQL 2000:
> http://www.replicationanswers.com/NoSyncInitializations.asp, for SQL 2005:
> http://www.replicationanswers.com/NoSyncOn2005.asp).
> Initialize as per normal.
> This means that any tables you had in the publisher's database and not in
> the subscriber database will be replaced - is this OK? If not, restore the
> publisher database as another name and transfer over the tables/objwects
> selectively using DTS.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Hi Ron - answers inline:
> 1: Can I set the subscriber DB to read only?
(1) yes, but when you restore it on the publisher you'll need to set to be
RW to add the publication
> 2: Why do I need a backup of the publisher as it is corrupted.
(2) In case there are tables which aren't in the publication as you'll need
to copy them over into the restored subscriber database later on, assuming
it is addressable in some form or other. Or more simply, just rename it
after scripting out the replication setup.
> 3: By Scripting out the replication off the publisher do you mean right
> click on the on the replication tree in the server where the publisher
> is located and click on Generate SQL Script than click on preview and
> copy the script?
(3) Yes.
> 4: By restore on the publisher do you mean I can restore the backup of
> the subscriber to the publisher?
(4) Yes. When restoring it, you'll give it the same name as the published
database you had previously (the corrupted one). After that, you'll need to
assign permissions to it and then copy in any extra tables.
> 5: You said to run sp_removedbreplication on each database, does that
> mean both publisher and subscriber? are there any other option I
> should use with the sp_removedbreplication?
Yes. No - it's pretty straightforward and just takes one parameter - the
database name.
> 6: Using the replication script how do I recreate the publication and
> subscription? How do I set it up as a nosync on SQL 2000 I did not
> really understand the article.
The replication script should be able to be used as it is with one slight
change: set the @.sync_type = none parameter value of sp_addsubscription ie
change 'Automatic' to 'None'. After that, run
sp_scriptpublicationcustomprocs 'publicationname' at the publisher and
execute the results on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
Subscribe to:
Posts (Atom)