A database can be backed up from one computer running Microsoft® SQL Server™ to another only if both use the same sort orders, Unicode collation, and code pages (character set), set up during installation. If a different sort order, Unicode collation, Unicode locale, or character set is used, the restore operation detects this and does not attempt to restore the database. To restore the database, SQL Server must be reinstalled in the destination computer using the same sort order, Unicode collation, and character set as the computer that performed the database backup.
Note The Unicode collation defines the sorting behavior for the nchar and nvarchar data types.
If the databases use a binary sort order, it is not necessary for the character sets to match. If the character sets are the same, all characters are interpreted identically between the two computers. However, if the character sets are different, any extended characters are interpreted differently on the two computers. For example, the character “A” (ASCII value of 65) is interpreted the same for all character sets because it is not an extended character. However, if the character stored is “÷” (ASCII value of 246) on the first computer with a character set of code page 437 (U.S. English), the second computer with a character set of code page ISO 8859-1 (Latin 1 or ANSI), interprets the “÷” character as the “ö” character (still ASCII value 246).
Note If the computer where the database backup is to be restored does not have the same sort order, Unicode collation, and code page as the backup, is not using a binary sort order, or cannot be reinstalled, then it is not possible to restore the backup. Alternatively, data can be copied between the computers using the Data Transformation Services Import and Export wizards, the bcp utility, or a distributed query as part of an INSERT statement.
| Character Set | Data Transformation Services Import and Export Wizards |
| Sort Order | Importing and Exporting Data |