CODEWITHSUNDEEP
sql-server-2008ssmscollationdatabase-administration
Rockstart
Rockstart

Reputation: 2377

Change collation of database in MS SQL

How do I change the collation name of all databases in Database to SQL_Latin1_General_CP1_CI_AS in MS-SQL ?

Also I need the new databases to follow this collation name.

Upvotes: 3

Views: 536

Answers (1)

Abdur Rahim
Abdur Rahim

Reputation: 4021

Changing the default collation for an instance of SQL Server can be a complex operation and involves the following steps:

  1. Make sure you have all the information or scripts needed to re-create your user databases and all the objects in them.

  2. Export all your data using a tool such as the bcp Utility. For more information, see Importing and Exporting Bulk Data.

  3. Drop all the user databases.

  4. Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command. For example:

    Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ] /SQLCOLLATION=CollationName

For more information, see Rebuilding System Databases. Create all the databases and all the objects in them. Import all your data.

The original source is here

Upvotes: 3

Related Questions