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.
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.
Connect to the source remote MS SQL server using Management studio, WebAdmin or osql tool and run the following queries for each skipped database "
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'