Collation conflict isssue

Hi,

The tempdb db is having different collation than the application db.

I rebuilt the master db with the appropriate collation after backing up
master, model, msdb, appln databases.

On restore of the databases (master, model, msdb, appln) from the backup
restores the backed up database collation.

Is there any idea to restore the db with different collation than the
backed up collation.

Thanks,

*** Sent via Developersdex http://www.developersdex.com ***John Jayaseelan (john.jayaseelan@.caravan-club.co.uk) writes:
> The tempdb db is having different collation than the application db.
> I rebuilt the master db with the appropriate collation after backing up
> master, model, msdb, appln databases.
> On restore of the databases (master, model, msdb, appln) from the backup
> restores the backed up database collation.
> Is there any idea to restore the db with different collation than the
> backed up collation.

The only way to change the collation of a database, is to bulk out
all data, and rebuild the database from scripts. Well, you could
alter the collation of all character columns, but that would require
you to drop indexes and constraints, so that would probably be more work.

The same applies to the system databases. Rather than restoring backups
of these, you should have entered the information by other means.

Another option may be to install a second instance with a matching
collation.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Clustered indexes - unique and not unique

Hi friends,
In SQL Server 2000 if non-unique CI gets rebuilt then all NCI for a table
get rebuilt too along with it, and it's clear why.
In 2005, as far as I know, NCI don't get rebuilt along with CI whatsoever.
Why is that?
Thanks in advance.
Because the uniqueifier that is used in 2005 does not change when the
clustered index is rebuilt. So the key appended to the non-clustered index
will still point to the proper clustered index row after a rebuild.
Andrew J. Kelly SQL MVP
"Falconer" <me@.isp.net> wrote in message
news:C28zh.38401$Oa.29862@.edtnps82...
> Hi friends,
> In SQL Server 2000 if non-unique CI gets rebuilt then all NCI for a table
> get rebuilt too along with it, and it's clear why.
> In 2005, as far as I know, NCI don't get rebuilt along with CI whatsoever.
> Why is that?
> Thanks in advance.
>
|||Thanks Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJ5Ee9KTHHA.4252@.TK2MSFTNGP05.phx.gbl...
> Because the uniqueifier that is used in 2005 does not change when the
> clustered index is rebuilt. So the key appended to the non-clustered index
> will still point to the proper clustered index row after a rebuild.
> --
> Andrew J. Kelly SQL MVP
> "Falconer" <me@.isp.net> wrote in message
> news:C28zh.38401$Oa.29862@.edtnps82...
>

Clustered indexes - unique and not unique

Hi friends,
In SQL Server 2000 if non-unique CI gets rebuilt then all NCI for a table
get rebuilt too along with it, and it's clear why.
In 2005, as far as I know, NCI don't get rebuilt along with CI whatsoever.
Why is that?
Thanks in advance.Because the uniqueifier that is used in 2005 does not change when the
clustered index is rebuilt. So the key appended to the non-clustered index
will still point to the proper clustered index row after a rebuild.
Andrew J. Kelly SQL MVP
"Falconer" <me@.isp.net> wrote in message
news:C28zh.38401$Oa.29862@.edtnps82...
> Hi friends,
> In SQL Server 2000 if non-unique CI gets rebuilt then all NCI for a table
> get rebuilt too along with it, and it's clear why.
> In 2005, as far as I know, NCI don't get rebuilt along with CI whatsoever.
> Why is that?
> Thanks in advance.
>|||Thanks Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJ5Ee9KTHHA.4252@.TK2MSFTNGP05.phx.gbl...
> Because the uniqueifier that is used in 2005 does not change when the
> clustered index is rebuilt. So the key appended to the non-clustered index
> will still point to the proper clustered index row after a rebuild.
> --
> Andrew J. Kelly SQL MVP
> "Falconer" <me@.isp.net> wrote in message
> news:C28zh.38401$Oa.29862@.edtnps82...
>

Clustered indexes - unique and not unique

Hi friends,
In SQL Server 2000 if non-unique CI gets rebuilt then all NCI for a table
get rebuilt too along with it, and it's clear why.
In 2005, as far as I know, NCI don't get rebuilt along with CI whatsoever.
Why is that?
Thanks in advance.Because the uniqueifier that is used in 2005 does not change when the
clustered index is rebuilt. So the key appended to the non-clustered index
will still point to the proper clustered index row after a rebuild.
--
Andrew J. Kelly SQL MVP
"Falconer" <me@.isp.net> wrote in message
news:C28zh.38401$Oa.29862@.edtnps82...
> Hi friends,
> In SQL Server 2000 if non-unique CI gets rebuilt then all NCI for a table
> get rebuilt too along with it, and it's clear why.
> In 2005, as far as I know, NCI don't get rebuilt along with CI whatsoever.
> Why is that?
> Thanks in advance.
>|||Thanks Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJ5Ee9KTHHA.4252@.TK2MSFTNGP05.phx.gbl...
> Because the uniqueifier that is used in 2005 does not change when the
> clustered index is rebuilt. So the key appended to the non-clustered index
> will still point to the proper clustered index row after a rebuild.
> --
> Andrew J. Kelly SQL MVP
> "Falconer" <me@.isp.net> wrote in message
> news:C28zh.38401$Oa.29862@.edtnps82...
>> Hi friends,
>> In SQL Server 2000 if non-unique CI gets rebuilt then all NCI for a table
>> get rebuilt too along with it, and it's clear why.
>> In 2005, as far as I know, NCI don't get rebuilt along with CI
>> whatsoever. Why is that?
>> Thanks in advance.
>>
>