I am getting this error on replication and don't know why. Any ideas?
The process could not read file '\\NJRARSVR00E9\d$\sqldata\system\MSSQL$P001\ReplD ata\unc\NJRARSVR00E9$P001_PTR_PTR\20040707104224\s napshot.pre' due to OS error 5.Why is the extension "pre"? My understanding you're using an alternate location for distribution agent to read snapshot files from. If the file name is snapshot, I also assume that you chose "compression" for it, which means that the extension should be "cab"...What gives? And also post the entire error or errors.
Showing posts with label file. Show all posts
Showing posts with label file. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
Replication does not work after deleting the LDF file manually.
I have maually deleted the Database.LDF file by stopping the SQL Server. When i start the SQL Server, merge replication which was configured for the database does not work. How do i fix this problem.
Thanks in advance.
Regards,
Venu.Reinitialize the replication. It should pick up a new starting LSN. But why did you ever delete the LDF anyway?
Thanks in advance.
Regards,
Venu.Reinitialize the replication. It should pick up a new starting LSN. But why did you ever delete the LDF anyway?
Monday, March 12, 2012
Replication adn tempdb log file
I am relatively new to SQL Replication and am trying to configure replication
for some SQL databases for a disaster recovery project. I have several
databases ranging from 100MB to 5.5GB that I want to replicate to another
server offsite. I am starting with a small database. I go through the
Replication Wizard and it adds the articles. Then it sits on the create
publication screen forever and finally I get an error that says the tempdb
log file is full. I notice that the log file is over 10GB in size. Why is
that for a 100MB database? I have 17GB available on the server. How can I
create a publication for a database without filling up the tempdb log file?
If it uses over 10GB for a 100MB database what is going to happen when I
replicate a 5.5GB database?
Bill - such useage of tempdb is very odd - is it possible there is another
process occurring concurrently which fills the tempdb? Can you remove the
inactive entries to see if this helps:
backup log tempdb with no_log
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
for some SQL databases for a disaster recovery project. I have several
databases ranging from 100MB to 5.5GB that I want to replicate to another
server offsite. I am starting with a small database. I go through the
Replication Wizard and it adds the articles. Then it sits on the create
publication screen forever and finally I get an error that says the tempdb
log file is full. I notice that the log file is over 10GB in size. Why is
that for a 100MB database? I have 17GB available on the server. How can I
create a publication for a database without filling up the tempdb log file?
If it uses over 10GB for a 100MB database what is going to happen when I
replicate a 5.5GB database?
Bill - such useage of tempdb is very odd - is it possible there is another
process occurring concurrently which fills the tempdb? Can you remove the
inactive entries to see if this helps:
backup log tempdb with no_log
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Replication : The process coule not read file?
Hi.everyone.I use two computer in workgroup.I use A to be a publisher and distributor ,B to a Subscrible.When i create a snop in A computer and Create snopshot in "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\repldata\unc",when I use "view Synchronization Status" in B computer, the show "job already success start",but in A computer sqlserver error log show "Message
Replication-: agent LIUQINA\SERVER2005-TestTran-Tran-LIUQIANHOME\HOME-6 failed. The process could not read file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\ReplData\unc\LIUQINA$SERVER2005_TESTTRAN_TRAN\20070409150285\name_2.pre' due to OS error 3.". The publisher snapshot agent is servername\administrator.
I find a lot of message in internet ,but no one can help me .I try use same account and password in two computer.but no help.
Please tell me why and how to solve it ? Thanks.
Hello liuqian5036
You must use a UNC (Universal Naming Convention) for the path. A local path is not a valid path for the snapshot folder. For example, you should use \\<ServerName>\ReplData instead of "C:\....". You should share this folder on the network and give the snapshot account the necessary access rights to reach that folder. (read, write and modify is enough)
This address is specified when you configure the distributor. So you may go to the Replication folder in SQL Server instance you want to work on and then right click on the Replication folder and click Distributor Properties and click Publishers... item in the left panel. You will see Distribution Database column in the view and <Distributor Name>. Click the "..." just at the right side of it and change "Default Snapshot Folder" path as i told you above.
Ekrem ?nsoy
MCP, MCDST, MCDBA, MCAD.Net, MCSD.Net, MCSA, MCSE
|||Thank you very much,enaksanamun .but i have another question,When i run subscription ,I can see the replication is success,but In "View Synchronization Status" dialog it always run don't tell me it success.Why?|||You are welcome liuqian5036.
I need you to tell me the story more specific please. For example, what kind of replication do you use? (Snapshot, Transactional, Merge).
What do you see on the Synchronization Status window? OK icon or Error icon? And please check the history out. Write here what it says on the history.
Ekrem ?nsoy
MCP, MCDST, MCDBA, MCAD.Net, MCSD.Net, MCSA, MCSE
|||Hi.Ekrem.Thank you.I am sorry to say I mistake the conception of "Synchronization Status Window",It's ok,no problem.Thank you help.Replication / Restores and initialize schema question
I have a database with 3 added filegroups/files. The restore with move,
moves the logical filename to a specifiic physical file.
Once restored the db objects should be restored to their proper filegroups.
Once I run the snapshot, can all of the schema and data be pushed to the
PRIMARY/DEFAULT filegroup?
Because this is what appears to be happening, all objects are being pushed
into the PRIMARY/DEFAULT filegroup.
In published db, objects are in their correct filegroups, in the subscriber
db, they are all in the PRIMARY/DEFAULT.
Does this sound right... I posted a database restore question to the server
ng. I'm wondering if replication could cause this.
This doesn't make any sense to me...
no, you will have to then apply your schema through a pre-snapshot command,
and ensure you use the delete existing data in the snapshot tab of the table
article properties.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"frankm" <frankm@.nospam.postalias> wrote in message
news:OwVk18MKFHA.3500@.TK2MSFTNGP14.phx.gbl...
> I have a database with 3 added filegroups/files. The restore with move,
> moves the logical filename to a specifiic physical file.
> Once restored the db objects should be restored to their proper
filegroups.
> Once I run the snapshot, can all of the schema and data be pushed to the
> PRIMARY/DEFAULT filegroup?
> Because this is what appears to be happening, all objects are being pushed
> into the PRIMARY/DEFAULT filegroup.
> In published db, objects are in their correct filegroups, in the
subscriber
> db, they are all in the PRIMARY/DEFAULT.
> Does this sound right... I posted a database restore question to the
server
> ng. I'm wondering if replication could cause this.
> This doesn't make any sense to me...
>
|||Then it sounds like replication is actually pushing the schema and data to
the PRIMARY/DEFAULT filegroup.
I'm still trying to understand why it does that?
Looking at the "delete existing data" selection - this appears to be per
table.
Is there a way to set this globally? I'd hate to select that for 700 tables
individually.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uo8FoNNKFHA.2936@.TK2MSFTNGP15.phx.gbl...
> no, you will have to then apply your schema through a pre-snapshot
command,
> and ensure you use the delete existing data in the snapshot tab of the
table[vbcol=seagreen]
> article properties.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "frankm" <frankm@.nospam.postalias> wrote in message
> news:OwVk18MKFHA.3500@.TK2MSFTNGP14.phx.gbl...
> filegroups.
pushed
> subscriber
> server
>
|||oops - should have looked further down - looks like I can set it globally in
"Article Defaults"
"frankm" <frankm@.nospam.postalias> wrote in message
news:O9RjwUNKFHA.3500@.TK2MSFTNGP14.phx.gbl...
> Then it sounds like replication is actually pushing the schema and data to
> the PRIMARY/DEFAULT filegroup.
> I'm still trying to understand why it does that?
> Looking at the "delete existing data" selection - this appears to be per
> table.
> Is there a way to set this globally? I'd hate to select that for 700
tables[vbcol=seagreen]
> individually.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uo8FoNNKFHA.2936@.TK2MSFTNGP15.phx.gbl...
> command,
> table
move,[vbcol=seagreen]
the
> pushed
>
moves the logical filename to a specifiic physical file.
Once restored the db objects should be restored to their proper filegroups.
Once I run the snapshot, can all of the schema and data be pushed to the
PRIMARY/DEFAULT filegroup?
Because this is what appears to be happening, all objects are being pushed
into the PRIMARY/DEFAULT filegroup.
In published db, objects are in their correct filegroups, in the subscriber
db, they are all in the PRIMARY/DEFAULT.
Does this sound right... I posted a database restore question to the server
ng. I'm wondering if replication could cause this.
This doesn't make any sense to me...
no, you will have to then apply your schema through a pre-snapshot command,
and ensure you use the delete existing data in the snapshot tab of the table
article properties.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"frankm" <frankm@.nospam.postalias> wrote in message
news:OwVk18MKFHA.3500@.TK2MSFTNGP14.phx.gbl...
> I have a database with 3 added filegroups/files. The restore with move,
> moves the logical filename to a specifiic physical file.
> Once restored the db objects should be restored to their proper
filegroups.
> Once I run the snapshot, can all of the schema and data be pushed to the
> PRIMARY/DEFAULT filegroup?
> Because this is what appears to be happening, all objects are being pushed
> into the PRIMARY/DEFAULT filegroup.
> In published db, objects are in their correct filegroups, in the
subscriber
> db, they are all in the PRIMARY/DEFAULT.
> Does this sound right... I posted a database restore question to the
server
> ng. I'm wondering if replication could cause this.
> This doesn't make any sense to me...
>
|||Then it sounds like replication is actually pushing the schema and data to
the PRIMARY/DEFAULT filegroup.
I'm still trying to understand why it does that?
Looking at the "delete existing data" selection - this appears to be per
table.
Is there a way to set this globally? I'd hate to select that for 700 tables
individually.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uo8FoNNKFHA.2936@.TK2MSFTNGP15.phx.gbl...
> no, you will have to then apply your schema through a pre-snapshot
command,
> and ensure you use the delete existing data in the snapshot tab of the
table[vbcol=seagreen]
> article properties.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "frankm" <frankm@.nospam.postalias> wrote in message
> news:OwVk18MKFHA.3500@.TK2MSFTNGP14.phx.gbl...
> filegroups.
pushed
> subscriber
> server
>
|||oops - should have looked further down - looks like I can set it globally in
"Article Defaults"
"frankm" <frankm@.nospam.postalias> wrote in message
news:O9RjwUNKFHA.3500@.TK2MSFTNGP14.phx.gbl...
> Then it sounds like replication is actually pushing the schema and data to
> the PRIMARY/DEFAULT filegroup.
> I'm still trying to understand why it does that?
> Looking at the "delete existing data" selection - this appears to be per
> table.
> Is there a way to set this globally? I'd hate to select that for 700
tables[vbcol=seagreen]
> individually.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uo8FoNNKFHA.2936@.TK2MSFTNGP15.phx.gbl...
> command,
> table
move,[vbcol=seagreen]
the
> pushed
>
Friday, March 9, 2012
Replication & NDF
If my replicating db has an .mdf/.ndf/.ldf, with some tables being located on the Secondary Data File, should I change the schema of the replicated database to match the replicating db?
Does it matter? Would it be a 'best practice' to have both database schemas the same?
I didn't setup the .NDF, I basically inherited it from a third party software addition, who placed some of their tables on an .ndf.
I'm debating whether or not it matters in relation to replicating this database. What would you do?
Thanx for your opinion!
JLS,
I'd say 'it depends'
Are you getting significant performance benefits by using filegroups? Do you need those same benefits on the subscriber? Do you have the corresponding disk setup on the subscriber? I'm assuming each filegroup is on a different disk here, otherwise I see no reason to have this setup even on the publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||" I'm assuming each filegroup is on a different disk here, otherwise I see no reason to have this setup even on the publisher."
Perfect, and exactly what I thought!!!! I knew this guy didn't have a clue as to what he was doing, and you just confirmed this for me. I read the exact same thing, and also hedged due to the fact that if the NDF becomes corrupt, the entire db is marked Suspect.....
Thanx once again Paul!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:uwKaevZ2FHA.3964@.TK2MSFTNGP10.phx.gbl...
JLS,
I'd say 'it depends' Are you getting significant performance benefits by using filegroups? Do you need those same benefits on the subscriber? Do you have the corresponding disk setup on the subscriber? I'm assuming each filegroup is on a different disk here, otherwise I see no reason to have this setup even on the publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||JLS,
there might be possibly another reason he used this setup. Do you use filegroup backups? I don't use them, but if the database is too large for a conventional backup strategy, filegroups are sometimes used.
Cheers,
Paul Ibison
|||I would evaluate 1) whether the files are in their own file groups or a different file group
2) if so, are these files on the same logical/physical disk or are they on the same controller or are they all hanging off a san.
3) are the indexes or text or image data on these different files - there is a performance boost for doing this - and you can backup the image and text columns separate from the other file backups.
If they are on different files or different file groups is this to allow you to do file/filegroup level backups and do you need this?
If they are on different file groups and they are hanging off the same drive (logical or physical) or off the same controller or san there is no performance benefit to have them on different files, filegroups.
On SQL 7 there was a performance boost if you had multiple files in your filegroup. In SQL 2000 on win2k there could be in some situations - but this has gone away in win2003.
In short, I think the answer is no.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JLS" <jlshoop@.hotmail.com> wrote in message news:et4WmgZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
If my replicating db has an .mdf/.ndf/.ldf, with some tables being located on the Secondary Data File, should I change the schema of the replicated database to match the replicating db?
Does it matter? Would it be a 'best practice' to have both database schemas the same?
I didn't setup the .NDF, I basically inherited it from a third party software addition, who placed some of their tables on an .ndf.
I'm debating whether or not it matters in relation to replicating this database. What would you do?
Thanx for your opinion!
|||No, we do database backups using 3rd party tool, LiteSpeed.
So, I see no advantage to the .ndf & am planning to replicate without considering the .ndf
Thanx for your opinions & advice, I really appreciate it!!!!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:OVvZ7ta2FHA.3156@.TK2MSFTNGP10.phx.gbl...
JLS,
there might be possibly another reason he used this setup. Do you use filegroup backups? I don't use them, but if the database is too large for a conventional backup strategy, filegroups are sometimes used.
Cheers,
Paul Ibison
|||Short answer is what I thought you would say, Thanx!
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:OXFxh1c2FHA.3912@.TK2MSFTNGP15.phx.gbl...
I would evaluate 1) whether the files are in their own file groups or a different file group
2) if so, are these files on the same logical/physical disk or are they on the same controller or are they all hanging off a san.
3) are the indexes or text or image data on these different files - there is a performance boost for doing this - and you can backup the image and text columns separate from the other file backups.
If they are on different files or different file groups is this to allow you to do file/filegroup level backups and do you need this?
If they are on different file groups and they are hanging off the same drive (logical or physical) or off the same controller or san there is no performance benefit to have them on different files, filegroups.
On SQL 7 there was a performance boost if you had multiple files in your filegroup. In SQL 2000 on win2k there could be in some situations - but this has gone away in win2003.
In short, I think the answer is no.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JLS" <jlshoop@.hotmail.com> wrote in message news:et4WmgZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
If my replicating db has an .mdf/.ndf/.ldf, with some tables being located on the Secondary Data File, should I change the schema of the replicated database to match the replicating db?
Does it matter? Would it be a 'best practice' to have both database schemas the same?
I didn't setup the .NDF, I basically inherited it from a third party software addition, who placed some of their tables on an .ndf.
I'm debating whether or not it matters in relation to replicating this database. What would you do?
Thanx for your opinion!
Does it matter? Would it be a 'best practice' to have both database schemas the same?
I didn't setup the .NDF, I basically inherited it from a third party software addition, who placed some of their tables on an .ndf.
I'm debating whether or not it matters in relation to replicating this database. What would you do?
Thanx for your opinion!
JLS,
I'd say 'it depends'
Are you getting significant performance benefits by using filegroups? Do you need those same benefits on the subscriber? Do you have the corresponding disk setup on the subscriber? I'm assuming each filegroup is on a different disk here, otherwise I see no reason to have this setup even on the publisher.Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||" I'm assuming each filegroup is on a different disk here, otherwise I see no reason to have this setup even on the publisher."
Perfect, and exactly what I thought!!!! I knew this guy didn't have a clue as to what he was doing, and you just confirmed this for me. I read the exact same thing, and also hedged due to the fact that if the NDF becomes corrupt, the entire db is marked Suspect.....
Thanx once again Paul!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:uwKaevZ2FHA.3964@.TK2MSFTNGP10.phx.gbl...
JLS,
I'd say 'it depends'
Are you getting significant performance benefits by using filegroups? Do you need those same benefits on the subscriber? Do you have the corresponding disk setup on the subscriber? I'm assuming each filegroup is on a different disk here, otherwise I see no reason to have this setup even on the publisher.Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||JLS,
there might be possibly another reason he used this setup. Do you use filegroup backups? I don't use them, but if the database is too large for a conventional backup strategy, filegroups are sometimes used.
Cheers,
Paul Ibison
|||I would evaluate 1) whether the files are in their own file groups or a different file group
2) if so, are these files on the same logical/physical disk or are they on the same controller or are they all hanging off a san.
3) are the indexes or text or image data on these different files - there is a performance boost for doing this - and you can backup the image and text columns separate from the other file backups.
If they are on different files or different file groups is this to allow you to do file/filegroup level backups and do you need this?
If they are on different file groups and they are hanging off the same drive (logical or physical) or off the same controller or san there is no performance benefit to have them on different files, filegroups.
On SQL 7 there was a performance boost if you had multiple files in your filegroup. In SQL 2000 on win2k there could be in some situations - but this has gone away in win2003.
In short, I think the answer is no.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JLS" <jlshoop@.hotmail.com> wrote in message news:et4WmgZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
If my replicating db has an .mdf/.ndf/.ldf, with some tables being located on the Secondary Data File, should I change the schema of the replicated database to match the replicating db?
Does it matter? Would it be a 'best practice' to have both database schemas the same?
I didn't setup the .NDF, I basically inherited it from a third party software addition, who placed some of their tables on an .ndf.
I'm debating whether or not it matters in relation to replicating this database. What would you do?
Thanx for your opinion!
|||No, we do database backups using 3rd party tool, LiteSpeed.
So, I see no advantage to the .ndf & am planning to replicate without considering the .ndf
Thanx for your opinions & advice, I really appreciate it!!!!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:OVvZ7ta2FHA.3156@.TK2MSFTNGP10.phx.gbl...
JLS,
there might be possibly another reason he used this setup. Do you use filegroup backups? I don't use them, but if the database is too large for a conventional backup strategy, filegroups are sometimes used.
Cheers,
Paul Ibison
|||Short answer is what I thought you would say, Thanx!
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:OXFxh1c2FHA.3912@.TK2MSFTNGP15.phx.gbl...
I would evaluate 1) whether the files are in their own file groups or a different file group
2) if so, are these files on the same logical/physical disk or are they on the same controller or are they all hanging off a san.
3) are the indexes or text or image data on these different files - there is a performance boost for doing this - and you can backup the image and text columns separate from the other file backups.
If they are on different files or different file groups is this to allow you to do file/filegroup level backups and do you need this?
If they are on different file groups and they are hanging off the same drive (logical or physical) or off the same controller or san there is no performance benefit to have them on different files, filegroups.
On SQL 7 there was a performance boost if you had multiple files in your filegroup. In SQL 2000 on win2k there could be in some situations - but this has gone away in win2003.
In short, I think the answer is no.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JLS" <jlshoop@.hotmail.com> wrote in message news:et4WmgZ2FHA.4076@.TK2MSFTNGP09.phx.gbl...
If my replicating db has an .mdf/.ndf/.ldf, with some tables being located on the Secondary Data File, should I change the schema of the replicated database to match the replicating db?
Does it matter? Would it be a 'best practice' to have both database schemas the same?
I didn't setup the .NDF, I basically inherited it from a third party software addition, who placed some of their tables on an .ndf.
I'm debating whether or not it matters in relation to replicating this database. What would you do?
Thanx for your opinion!
Replication - SQL Server 2000 - Service Pack 4
Hi,
I am considering upgrading to SQL Server service pack 4 and would just like
to clarify.
In the readme file for SP 4 if find the following:
“Note If an instance of SQL Server is not part of a replication topology,
you can back up a user database and restore it on any other release of SQL
Server 2000.”
Does this mean that I will not be able to restore a user database from a
instance running SP3 to a instance running sp 4 if it is part of a
replication topology? And if this is the case is there a workaround? Like a
script that upgrades the user databases to sp 4?
Scenario: Two environments: testing and production. I upgrade test to SP4
and then restore user databases from production (sp3). Replication is used
both in test and production, so the restored user databases that are involved
in replication will not longer work, because they are from an instance that
is sp3? Right?
Many Thanks,
Nina
You cannot restore a backup of a replicated database from a different version
of SQL Server 2000. This is because replication updates user databases
(rather than just system databases) when the service pack is applied, and a
restored SP3 database may have replication metadata that is incompatible with
SP4 replication. User databases participating in a replication topology must
be online and accessible during upgrade to SP4 so that updates can be made.
I'm not sure if there is a separate script just to update user databases to
SP4, it may be part of the overall replication upgrade scripts. At any rate,
the recommended process is to let SP4 Setup perform the upgrade.
Glenn Gailey
SQL Server User Education
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nina" wrote:
> Hi,
> I am considering upgrading to SQL Server service pack 4 and would just like
> to clarify.
> In the readme file for SP 4 if find the following:
> “Note If an instance of SQL Server is not part of a replication topology,
> you can back up a user database and restore it on any other release of SQL
> Server 2000.”
> Does this mean that I will not be able to restore a user database from a
> instance running SP3 to a instance running sp 4 if it is part of a
> replication topology? And if this is the case is there a workaround? Like a
> script that upgrades the user databases to sp 4?
> Scenario: Two environments: testing and production. I upgrade test to SP4
> and then restore user databases from production (sp3). Replication is used
> both in test and production, so the restored user databases that are involved
> in replication will not longer work, because they are from an instance that
> is sp3? Right?
> Many Thanks,
> Nina
>
I am considering upgrading to SQL Server service pack 4 and would just like
to clarify.
In the readme file for SP 4 if find the following:
“Note If an instance of SQL Server is not part of a replication topology,
you can back up a user database and restore it on any other release of SQL
Server 2000.”
Does this mean that I will not be able to restore a user database from a
instance running SP3 to a instance running sp 4 if it is part of a
replication topology? And if this is the case is there a workaround? Like a
script that upgrades the user databases to sp 4?
Scenario: Two environments: testing and production. I upgrade test to SP4
and then restore user databases from production (sp3). Replication is used
both in test and production, so the restored user databases that are involved
in replication will not longer work, because they are from an instance that
is sp3? Right?
Many Thanks,
Nina
You cannot restore a backup of a replicated database from a different version
of SQL Server 2000. This is because replication updates user databases
(rather than just system databases) when the service pack is applied, and a
restored SP3 database may have replication metadata that is incompatible with
SP4 replication. User databases participating in a replication topology must
be online and accessible during upgrade to SP4 so that updates can be made.
I'm not sure if there is a separate script just to update user databases to
SP4, it may be part of the overall replication upgrade scripts. At any rate,
the recommended process is to let SP4 Setup perform the upgrade.
Glenn Gailey
SQL Server User Education
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nina" wrote:
> Hi,
> I am considering upgrading to SQL Server service pack 4 and would just like
> to clarify.
> In the readme file for SP 4 if find the following:
> “Note If an instance of SQL Server is not part of a replication topology,
> you can back up a user database and restore it on any other release of SQL
> Server 2000.”
> Does this mean that I will not be able to restore a user database from a
> instance running SP3 to a instance running sp 4 if it is part of a
> replication topology? And if this is the case is there a workaround? Like a
> script that upgrades the user databases to sp 4?
> Scenario: Two environments: testing and production. I upgrade test to SP4
> and then restore user databases from production (sp3). Replication is used
> both in test and production, so the restored user databases that are involved
> in replication will not longer work, because they are from an instance that
> is sp3? Right?
> Many Thanks,
> Nina
>
Subscribe to:
Posts (Atom)