MS SQL databse is not transferred while migration from Plesk 8.X with external MS SQL server.

Created:

2016-11-16 12:47:31 UTC

Modified:

2017-04-24 12:11:38 UTC

0

Was this article helpful?


Have more questions?

Submit a request

MS SQL databse is not transferred while migration from Plesk 8.X with external MS SQL server.

Applicable to:

  • Plesk 9.x and below for Windows

Symptoms

After migration from Plesk 8.x with external MS SQL server, one or more MS SQL databases are not transferred to destination server. Empty databases are created on the destination server instead.The dump file of these databases are not included to C:\\TEMP\\<SESSION_ID>\\NativeDump\\psaDump.zip on the destination server.

Cause

The following warning in Plesk backup log file " %plesk_dir%\\PrivateTemp\\PSABackupLogs\\DATA.NN\\cmd.log " explains the situation:

Start: Back up MS-SQL database DATABASE_NAME
Warning: psabackup.InformationalException: Unable to create dump file for MS-SQL database DATABASE_NAME on domain DOMAIN_NAME ---> psabackup.InformationalException: Unable to dump MS SQL database. [Microsoft][ODBC SQL Server Driver][SQL Server]The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes. Database backup will be not included to Plesk backup.

Resolution

Connect to the source remote MS SQL server using Management studio, WebAdmin or osql tool and run the following queries for each skipped database " database_name ":

ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 80

Then migrate the data again.

If you need to change compatibility level for all databases on MS SQL server, you can use the following MS SQL query:

EXEC
sp_MSforeachdb '
use [?]
if (select count(*)
from sys.databases
where compatibility_level > 80 and
name=''?'' and database_id>4 and
is_read_only = 0
)=1
begin
alter database [?] set compatibility_level = 80
PRINT ''compatibility changed to 80 for database = '' + DB_NAME()
end'
Have more questions? Submit a request
Please sign in to leave a comment.