Changed (or inserted) data not transferring

We've had replication running for a while. We had some hardware
failures last week and had to restore an old database from before the
failure. Now we have some tables that are replicating correctly but
others aren't (using merge replication). We've made changes to the
data, seen the data get added or updated in msmerge_contents but when
the merge agent runs it shows no data needed to be merged.
For one table I tried sp_addtabletocontents also just to see and it
still had the same issue. We do have some row filtering on some tables
but on others we don't.
I can't say 100% yet, but for the most part it seems to be that data
from the remote server wont' replicate down to us but in some cases it
will replicate our changes up to them.
Any ideas or tips on what else to look at would be greatly appreciated.
Thanks.
Update the problem tables setting a columns' value to itself. See if this
fixes the problem.
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
"Don" <google@.thecoles.org> wrote in message
news:1124223959.170691.79230@.o13g2000cwo.googlegro ups.com...
> We've had replication running for a while. We had some hardware
> failures last week and had to restore an old database from before the
> failure. Now we have some tables that are replicating correctly but
> others aren't (using merge replication). We've made changes to the
> data, seen the data get added or updated in msmerge_contents but when
> the merge agent runs it shows no data needed to be merged.
> For one table I tried sp_addtabletocontents also just to see and it
> still had the same issue. We do have some row filtering on some tables
> but on others we don't.
> I can't say 100% yet, but for the most part it seems to be that data
> from the remote server wont' replicate down to us but in some cases it
> will replicate our changes up to them.
> Any ideas or tips on what else to look at would be greatly appreciated.
> Thanks.
>
|||Actually we tried that earlier to no avail (that's how we did get a
couple of the tables to update but the others still didn't). We're
trying out one program so we can find the data to manually transfer and
will probably end up removing replication and starting over again. But
we're still game to any ideas for now or in the future.
Thanks.
Hilary Cotter wrote:
> Update the problem tables setting a columns' value to itself. See if this
> fixes the problem.
> --

Change the location of the tempdb and the model databases

Is there a way to change the location of the tempdb and the model database files. I tried to do a restore with move with no luck and I cannot seem to get the syntax for an Alter database. Any suggestions?Look up Atricle Q224071 in Microsoft's knowledge base, it addresses your questions.

Change sort order for SQL 7 SP4

Is their a way to change the sort order. I have a backup
i need to restore, but it says the sort order is not
correct.
Thanks
RickRick,
With SQL 7, I think you'd have to re-install the server & specify the
appropriate sort-order during installation. I assume that you're restoring
to a different server than that from which the backup was taken, hence the
error message.
If you need to transfer a database to a server that you can't re-install,
with different language, sortorder, case sensitivity etc, I think you may
need to do the transfer using DTS or similar.
HTH
--
--
Kind Regards,
Robert A. Ellis, MCSD
Software Developer
"Rick" <rhollis@.fmtsd.com> wrote in message
news:18c501c35dd9$adfb9ba0$7d02280a@.phx.gbl...
> Is their a way to change the sort order. I have a backup
> i need to restore, but it says the sort order is not
> correct.
> Thanks
> Rick|||Hi Rick and Robert,
Actually you need to rebuild the master database (using rebuildm utility)
to change sort order in SQL Server 7.0. Doing this in effect will have a
newly built master database, so all your references to user databases,
logins etc will be lost.
Sincerely,
Yih-Yoon Lee [Microsoft]
Microsoft SQL Server Support
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Change Ownership

How do I change ownership of a database table to another user?
I did a restore of a database using our database software. It retained the
user's ownership of a table, but did not restore the users, so basically i
have a user that does not exist to this server owning a table.. confusing!!
I would like to give ownership of this table to a new user I create for this
database. SQL server will not let me create a user with the same name b/c it
says the user exist...it does not. I'm very confused and need help thanks. I
hope this helps.. We are running SQL 2000 SP3a on Windows 2000 Server SP4.Look up sp_changedbowner in BOL (Books OnLine).
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Adrian Moseley" <amoseley@.personix.fiserv.com> wrote in message
news:%23RxUpJmHEHA.3832@.TK2MSFTNGP10.phx.gbl...
> How do I change ownership of a database table to another user?
> I did a restore of a database using our database software. It retained the
> user's ownership of a table, but did not restore the users, so basically i
> have a user that does not exist to this server owning a table..
confusing!!
> I would like to give ownership of this table to a new user I create for
this
> database. SQL server will not let me create a user with the same name b/c
it
> says the user exist...it does not. I'm very confused and need help thanks.
I
> hope this helps.. We are running SQL 2000 SP3a on Windows 2000 Server SP4.
>|||Hi,
To Add on,
Since you did a restore of database actually your database users also got
restored in the new database, but the lost the SID chain with Master
database syslogins tables. Due to this you will not be able to see the users
either thru enterprise manager or sp_helpuser procedure. But if you query
the "sysusers" table in corresponding database you will be able to see them.
The brink back the chanin you have to execute the procedure
Use <dbname>
go
sp_change_users_login 'Report'
This will Lists the users, and their corresponding security identifiers
(SID), that are in the current database, not linked to any login.
Based on the output you can use 'Update_one' option to rectify the isue.
Refer books online for more information.
Thanks
Hari
MCDBA
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:O5zu#rmHEHA.2924@.TK2MSFTNGP09.phx.gbl...
> Look up sp_changedbowner in BOL (Books OnLine).
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Adrian Moseley" <amoseley@.personix.fiserv.com> wrote in message
> news:%23RxUpJmHEHA.3832@.TK2MSFTNGP10.phx.gbl...
the
i
> confusing!!
> this
b/c
> it
thanks.
> I
SP4.
>

Change Logical Name in Script

I'm trying to restore about 70 databases onto a new SQL server and
wanted to script the creation and restore. I've done the creation with
no problems, but on the restore, the logical names (of the original
data) are all over the place and were historically wrong.

So, when I use the script below... I've had to work out the Logical
name for the data and the log file and alter the script accordingly.

Creation
----

Create Database MyDatabase ON (NAME = MyDatabase_data, FileName =
'D:\Database\MSSQL\Data\MyDatabase.mdf') LOG ON (NAME = MyDatabase_log,
FileName = 'D:\Database\MSSQL\Data\MyDatabase.ldf') COLLATE
SQL_Latin1_General_CP1_CI_AS

Restore
----

RESTORE FILELISTONLY
from disk =
'D:\Database\MSSQL\BACKUP\2006-08-07\MyDatabase_db_200608072100.BAK'

restore database MyDatabase
from disk =
'D:\Database\MSSQL\BACKUP\2006-08-07\MyDatabase_db_200608072100.BAK'
with REPLACE,
MOVE 'SomeOtherRubbish_Data' TO
'D:\Database\MSSQL\Data\MyDatabase.mdf',
MOVE 'SomeOtherRubbish_Log' TO
'D:\Database\MSSQL\Data\MyDatabase.ldf'
go

When I then look at the properties of the database, it shows the old
convention which I don't want.

So, even though I've been neat creating the database, it gets
overwritten with the odl rubbish name. How can I change the logical
name so that I can have a nice and neat naming convention ?

Oh, Yes I know I added the collation when creating the database, but
that's another thing that we need to address at some point.

Thanks in advanceRyan (ryanofford@.hotmail.com) writes:

Quote:

Originally Posted by

I'm trying to restore about 70 databases onto a new SQL server and
wanted to script the creation and restore. I've done the creation with
no problems, but on the restore, the logical names (of the original
data) are all over the place and were historically wrong.

If I understand this correctly, you first run CREATE DATABASE for a
database, and then RESTORE for the same database for you. I've bad news
for you: the script for CREATE DATABASE was in vein. RESTORE will create
the database if it does not exist. And if it exists, it will throw the
old one away.

Quote:

Originally Posted by

So, even though I've been neat creating the database, it gets
overwritten with the odl rubbish name. How can I change the logical
name so that I can have a nice and neat naming convention ?

ALTER DATBASE db MODIFY FILE oldname NAME = oldname, NEWNAME = newname

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland. I'll give it a try. No major issue on not using the
create script, but thanks for the advice on that.

Ryan

Erland Sommarskog wrote:

Quote:

Originally Posted by

Ryan (ryanofford@.hotmail.com) writes:

Quote:

Originally Posted by

I'm trying to restore about 70 databases onto a new SQL server and
wanted to script the creation and restore. I've done the creation with
no problems, but on the restore, the logical names (of the original
data) are all over the place and were historically wrong.

>
If I understand this correctly, you first run CREATE DATABASE for a
database, and then RESTORE for the same database for you. I've bad news
for you: the script for CREATE DATABASE was in vein. RESTORE will create
the database if it does not exist. And if it exists, it will throw the
old one away.
>

Quote:

Originally Posted by

So, even though I've been neat creating the database, it gets
overwritten with the odl rubbish name. How can I change the logical
name so that I can have a nice and neat naming convention ?

>
ALTER DATBASE db MODIFY FILE oldname NAME = oldname, NEWNAME = newname
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Change Logical File Name during Restore Database

When I attempt to restore a SQL Server 2000 database
SAPProd backup to SAPConv, it works fine.
However, if I change the "Logical File Name" from
SAPProd_Data to SAPConv_Data & SAPProd_Log to SAPConv_Log,
I get an error message saying
"Logical file SAPConv_Data is not part of database
SAPConv ... ".
I just wonder is it possible for me to change the logical
file name during restore database. Besides, I would like
to know when will we use the Logical File Name.
ThanksYou can't change the logical file name when you do the restore, though you
can change the physical file name. That said, once you have restored the
database, you can use ALTER DATABASE to change the logical file name. You
use the logical file name for various ALTER DATABASE commands as well as
DBCC SHRINKFILE.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:20aee01c45981$75d62c00$a501280a@.phx.gbl...
When I attempt to restore a SQL Server 2000 database
SAPProd backup to SAPConv, it works fine.
However, if I change the "Logical File Name" from
SAPProd_Data to SAPConv_Data & SAPProd_Log to SAPConv_Log,
I get an error message saying
"Logical file SAPConv_Data is not part of database
SAPConv ... ".
I just wonder is it possible for me to change the logical
file name during restore database. Besides, I would like
to know when will we use the Logical File Name.
Thanks|||Thank you for your reply.
I would like to know is there any problem if 2 databases
carry the same "Logical File Name" ?
Thanks
>--Original Message--
>You can't change the logical file name when you do the
restore, though you
>can change the physical file name. That said, once you
have restored the
>database, you can use ALTER DATABASE to change the
logical file name. You
>use the logical file name for various ALTER DATABASE
commands as well as
>DBCC SHRINKFILE.
>--
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>..
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:20aee01c45981$75d62c00$a501280a@.phx.gbl...
>When I attempt to restore a SQL Server 2000 database
>SAPProd backup to SAPConv, it works fine.
>However, if I change the "Logical File Name" from
>SAPProd_Data to SAPConv_Data & SAPProd_Log to SAPConv_Log,
>I get an error message saying
>"Logical file SAPConv_Data is not part of database
>SAPConv ... ".
>I just wonder is it possible for me to change the logical
>file name during restore database. Besides, I would like
>to know when will we use the Logical File Name.
>Thanks
>.
>|||Hi,
No issues at all. You can have same logical name for 2 or more databases.
You can even have the same physical names for 2 database sprovided the
physical files are kept in different
folders
--
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:20bed01c45987$d431b160$a401280a@.phx.gbl...
> Thank you for your reply.
> I would like to know is there any problem if 2 databases
> carry the same "Logical File Name" ?
> Thanks
> >--Original Message--
> >You can't change the logical file name when you do the
> restore, though you
> >can change the physical file name. That said, once you
> have restored the
> >database, you can use ALTER DATABASE to change the
> logical file name. You
> >use the logical file name for various ALTER DATABASE
> commands as well as
> >DBCC SHRINKFILE.
> >
> >--
> > Tom
> >
> >----
> >Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >SQL Server MVP
> >Columnist, SQL Server Professional
> >Toronto, ON Canada
> >www.pinnaclepublishing.com/sql
> >..
> >"Peter" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:20aee01c45981$75d62c00$a501280a@.phx.gbl...
> >When I attempt to restore a SQL Server 2000 database
> >SAPProd backup to SAPConv, it works fine.
> >
> >However, if I change the "Logical File Name" from
> >SAPProd_Data to SAPConv_Data & SAPProd_Log to SAPConv_Log,
> >I get an error message saying
> >"Logical file SAPConv_Data is not part of database
> >SAPConv ... ".
> >
> >I just wonder is it possible for me to change the logical
> >file name during restore database. Besides, I would like
> >to know when will we use the Logical File Name.
> >
> >Thanks
> >
> >.
> >

Change Logical File Name during Restore Database

When I attempt to restore a SQL Server 2000 database
SAPProd backup to SAPConv, it works fine.
However, if I change the "Logical File Name" from
SAPProd_Data to SAPConv_Data & SAPProd_Log to SAPConv_Log,
I get an error message saying
"Logical file SAPConv_Data is not part of database
SAPConv ... ".
I just wonder is it possible for me to change the logical
file name during restore database. Besides, I would like
to know when will we use the Logical File Name.
Thanks
You can't change the logical file name when you do the restore, though you
can change the physical file name. That said, once you have restored the
database, you can use ALTER DATABASE to change the logical file name. You
use the logical file name for various ALTER DATABASE commands as well as
DBCC SHRINKFILE.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
..
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:20aee01c45981$75d62c00$a501280a@.phx.gbl...
When I attempt to restore a SQL Server 2000 database
SAPProd backup to SAPConv, it works fine.
However, if I change the "Logical File Name" from
SAPProd_Data to SAPConv_Data & SAPProd_Log to SAPConv_Log,
I get an error message saying
"Logical file SAPConv_Data is not part of database
SAPConv ... ".
I just wonder is it possible for me to change the logical
file name during restore database. Besides, I would like
to know when will we use the Logical File Name.
Thanks
|||Thank you for your reply.
I would like to know is there any problem if 2 databases
carry the same "Logical File Name" ?
Thanks

>--Original Message--
>You can't change the logical file name when you do the
restore, though you
>can change the physical file name. That said, once you
have restored the
>database, you can use ALTER DATABASE to change the
logical file name. You
>use the logical file name for various ALTER DATABASE
commands as well as
>DBCC SHRINKFILE.
>--
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>..
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:20aee01c45981$75d62c00$a501280a@.phx.gbl...
>When I attempt to restore a SQL Server 2000 database
>SAPProd backup to SAPConv, it works fine.
>However, if I change the "Logical File Name" from
>SAPProd_Data to SAPConv_Data & SAPProd_Log to SAPConv_Log,
>I get an error message saying
>"Logical file SAPConv_Data is not part of database
>SAPConv ... ".
>I just wonder is it possible for me to change the logical
>file name during restore database. Besides, I would like
>to know when will we use the Logical File Name.
>Thanks
>.
>
|||Hi,
No issues at all. You can have same logical name for 2 or more databases.
You can even have the same physical names for 2 database sprovided the
physical files are kept in different
folders
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:20bed01c45987$d431b160$a401280a@.phx.gbl...[vbcol=seagreen]
> Thank you for your reply.
> I would like to know is there any problem if 2 databases
> carry the same "Logical File Name" ?
> Thanks
> restore, though you
> have restored the
> logical file name. You
> commands as well as
> message

Change Logical File Name during Restore Database

When I attempt to restore a SQL Server 2000 database
SAPProd backup to SAPConv, it works fine.
However, if I change the "Logical File Name" from
SAPProd_Data to SAPConv_Data & SAPProd_Log to SAPConv_Log,
I get an error message saying
"Logical file SAPConv_Data is not part of database
SAPConv ... ".
I just wonder is it possible for me to change the logical
file name during restore database. Besides, I would like
to know when will we use the Logical File Name.
ThanksYou can't change the logical file name when you do the restore, though you
can change the physical file name. That said, once you have restored the
database, you can use ALTER DATABASE to change the logical file name. You
use the logical file name for various ALTER DATABASE commands as well as
DBCC SHRINKFILE.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:20aee01c45981$75d62c00$a501280a@.phx
.gbl...
When I attempt to restore a SQL Server 2000 database
SAPProd backup to SAPConv, it works fine.
However, if I change the "Logical File Name" from
SAPProd_Data to SAPConv_Data & SAPProd_Log to SAPConv_Log,
I get an error message saying
"Logical file SAPConv_Data is not part of database
SAPConv ... ".
I just wonder is it possible for me to change the logical
file name during restore database. Besides, I would like
to know when will we use the Logical File Name.
Thanks|||Thank you for your reply.
I would like to know is there any problem if 2 databases
carry the same "Logical File Name" ?
Thanks

>--Original Message--
>You can't change the logical file name when you do the
restore, though you
>can change the physical file name. That said, once you
have restored the
>database, you can use ALTER DATABASE to change the
logical file name. You
>use the logical file name for various ALTER DATABASE
commands as well as
>DBCC SHRINKFILE.
>--
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>..
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
> news:20aee01c45981$75d62c00$a501280a@.phx
.gbl...
>When I attempt to restore a SQL Server 2000 database
>SAPProd backup to SAPConv, it works fine.
>However, if I change the "Logical File Name" from
>SAPProd_Data to SAPConv_Data & SAPProd_Log to SAPConv_Log,
>I get an error message saying
>"Logical file SAPConv_Data is not part of database
>SAPConv ... ".
>I just wonder is it possible for me to change the logical
>file name during restore database. Besides, I would like
>to know when will we use the Logical File Name.
>Thanks
>.
>|||Hi,
No issues at all. You can have same logical name for 2 or more databases.
You can even have the same physical names for 2 database sprovided the
physical files are kept in different
folders
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:20bed01c45987$d431b160$a401280a@.phx
.gbl...[vbcol=seagreen]
> Thank you for your reply.
> I would like to know is there any problem if 2 databases
> carry the same "Logical File Name" ?
> Thanks
>
> restore, though you
> have restored the
> logical file name. You
> commands as well as
> message